CompPhase IS NOT NULL
However, you can't dynamically modify the filter. To achieve this dynamic filter effect, you must use a reporting tool to create your own drillthrough report and create an action that passes your current position in the cube to Reporting Services so that your query can retrieve the correct data.
Creating the Report
Reporting Services lets you easily create reports and pass parameters into those reports. Before you can create a custom drillthrough report, you need to know the values of the dimensions coming from the cube. Then you'll be able to apply the correct filter to weed out NULL values from the results.
The first step in creating a custom drillthrough is to use Visual Studio .NET to create a new Reporting Services report and decide which fields you want in the report. The second step is to create the parameters that you'll pass to the report. In this example, you need to create parameters for the project and the date. For the Date hierarchy, you have to create a value for each level—Year, Quarter, Month, and Day—because a drillthrough could occur at any of these levels. Likewise, if you had a Project hierarchy, you'd need to create a parameter for each level of that hierarchy.
NULL values are important because if you're at the top level of a hierarchy, you need to pass a NULL value to the report so that you get all the records. For example, if you're on the Date dimension's All level, you want to see data for all years, so you must pass a NULL instead of a date value. As you'll see when I show how to create the action, the action must determine what level in the hierarchy the user has drilled to. If the user is at the Month level in the Date dimension, the action will pass the month value into a Month parameter, so the Day parameter will be NULL. Fortunately, the action passes these values to the parameters in Reporting Services; the user need not do anything special to pass the parameters.
If you were creating a parameter for only the year, the initial query might look something like Listing 1. Notice the excessive checking for NULL. When you create the report parameters, it's essential to select the "Allow null value" and "Allow blank value" checkboxes, as Figure 7 shows. In this example, the Year and Proj parameters should have both checkboxes selected. Don't worry about the Meas parameter; you'll learn more about that in a moment.
Notice that the last line in the query is a hardcoded value, CompPhase IS NOT NULL. You want to use this value only when the user has drilled down on CompCount. So you need to determine the measure on which the user drilled down and adjust this final condition accordingly. At first glance, it might seem that a simple CASE statement in the WHERE clause would work, but I couldn't get this to work in Reporting Services without moving the query into a stored procedure. A simple approach is to pass in a parameter and use an IF statement to choose the correct SELECT statement. To make the switch, you have to pass the current measure from the action into a parameter in Reporting Services. Therefore, you'll create a parameter called Meas and in this case, don't let it accept NULL or blank values. The code in Listing 2 handles a drillthrough on either CompCount or BidCount and includes the correct WHERE clause.
Note that in this code, the Meas parameter determines which SELECT statement is chosen. If you had more than two possible drillthrough fields, you'd just add multiple IF statements to check for all of them. In addition, don't set the Meas parameter to "Allow null value" or "Allow blank value." Make Meas a required parameter that must be passed from Analysis Services with each call to the report. The action you create next will pass the parameters from Analysis Services to Reporting Services.
Running this report as it now stands gives you the results that Figure 8 shows. You can fill in a Meas value of either "comp" or "bid" and get the correct results. You're now ready to create in the Analysis Services cube an action that passes the appropriate parameters to the report.
Creating the Action
Analysis Services lets you add to your cube actions that you can tie to various cube elements: a dimension level, a dimension member, an individual cell, and so on. For this example, you need a cell-level action, the most granular level of action possible. You need to be able to pass the current value for the date, project, and measure the user is interested in, so you need the intersection of all the dimensions, which is an individual cell.
When creating a new action, you have options for where in the cube to place the action. The New Action Wizard will ask for the "target" of the action, which lets Analysis Services know from where in the cube the action will be callable. Set the target to "Cells in this cube." Analysis Services includes various types of actions; for this example, use a URL action.
You now need to create the action syntax. To work with the report you just created, your action will look like the code in Listing 3. Note that the first line simply points to the report, which is named Drillthrough in a project with the same name. Then, an IIF function determines which measure the user wants to drill on (if you had more measures, you'd just nest IIF statements) and sets the Meas parameter to the correct value. Another IIF statement then determines where the user is in the Project dimension hierarchy. If the user isn't at the top (All) level, the statement passes the actual project value. A similar IIF statement determines where the user is in the Date dimension hierarchy and sets the date parameter appropriately. The last phrase of the action turns off the report's parameters bar so that the report runs with the values your action has passed to it instead. Figure 9 shows the report after you've used your newly created action to run it, and the menu command in ProClarity that you'd use to run the action.
Let me add a couple of cautions. First, the Analysis Services Manager cube browser doesn't pass parameters correctly. If you change a field in a drop-down box at the top (e.g., changing the date from All to 2004), the browser doesn't detect this change, and the action behaves as if the Date dimension is still at the All level. So choose another tool to test your action.
Second, in your action, you might want to explicitly pass a NULL value rather than ignoring the parameter and passing nothing. The syntax for this is a little strange. For example, to force the Proj parameter to NULL on the URL line of Listing 3, the syntax looks like "&Proj:isnull=true".
Your Better Drillthrough
Drillthrough is a useful tool for letting analysts see the underlying relational data when they're working with a cube. When the default behavior of Analysis Services' drillthrough isn't sufficient, you have few options for modifying it. Replacing the inherent drillthrough with a custom solution that uses Reporting Services is one option for overcoming drillthrough's limitations. Creating the report is fairly simple, although it's complicated a bit by the convoluted query syntax to check for and handle NULL values.
When first presented with this problem, Microsoft told me that you shouldn't have NULL values in the fact table. But in some cases, it makes sense to have NULL values. (And you can't always control what your clients do when they build the warehouse.) In this case, no other value would have work; a 0 in a field meant that the project had gone through that phase.
The creation of the cell-level action is less straightforward, often consisting of many nested IIF statements because you need one nesting for each dimension level. Ultimately, however, you can build a URL that passes the appropriate values to the report and launches the report for the user.