MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports - Page 9
November 17, 2004
Filter the FoodMart Sales Report DataSet to Meet Business Requirements
When we filter a DataSet, or other components within Reporting Services, we find that the presence of nulls can complicate the already unusual workings of filters. Null filters present no real obstacles when we are attempting to restrict nulls for fields with a string data type, but when we find ourselves in a position of needing to filter data whose data type is numeric, integer and other non-string values, we have to take extra steps to create effective filters. The issue seems to partially lie within a conceptual conflict between zero and "empty:" when we implant logic that is making a comparison between a given non-string value and "empty," versus zero.
We might handle the problem, of course, by managing the nulls at the cube level - and I often do so for various reasons. However, we will assume, for purposes of this lesson, that we have a need to manage nulls within Reporting Services. Moreover, that need, in the present case, is driven by a larger need to filter by a certain value for the reporting purposes of the intended audience.
We will set up a filter that might represent the "intuitive" attempt to filter the nulls under consideration in our hypothetical business scenario in this section. We will see that the approach does not work, and we will examine a couple of approaches to accomplish our objectives with the filters we need. The second alternative is the best solution, as it provides an effective filter and manages nulls effectively at the same time. Covering both avenues will perhaps make an impression upon us, so that, should we come across the same scenario within our own business environments, we will recall the dilemma and the solution that we activated within our practice exercise.
First, let's set about the initial approach to the construction of the filter.
1. Click the Data tab to get to the DataSet we altered earlier.
2. Ensuring that the ProductData DataSet appears in the DataSet selector, click the ellipses ("...") to its right.
The DataSet Properties dialog opens, defaulting to the Query tab, as shown in Illustration 31.
3. Click the Filters tab on the DataSet Properties dialog.
4. Select (using the selector button to its right) or type into the respective column of the first line of the Filters section (leave the fourth column of the Filters definition area at default) the details depicted in Table 2.
The DataSet Properties dialog, Filters tab, appears as shown in Illustration 32.
5. Click OK to save the Filter setting and close the DataSet Properties dialog.
6. Click Preview to begin report execution.
The report begins to process, but is interrupted when the Processing Errors dialog, depicted in Illustration 33, appears.
The Processing Errors dialog indicates that a "comparison failure" has occurred, and that the filter expression is the culprit. The same failure occurs if we attempt the filter with quotes (of either variety) surrounding the 0, with removal of the "=" sign, the substitution of the ">" sign for the "=" sign in the value cell, or if we attempt to substitute <Blank>, which can be selected for the Value setting on the row. The reason for the failure is that null is not the same as zero, and requires special syntax in a Reporting Services filter when it is a consideration. Couple this with the fact that Reporting Services handles string and non-string comparisons differently, and the confusion only increases.
There are several ways to manage non-string comparisons of the sort we have encountered, and we will look at a couple of approaches to the management of filtering nulls for a non-string value. Let's return to the DataSet filter and get a feel for these solutions.
7. Click OK to close the Processing Errors dialog.
8. Click the Data tab.
9. Ensuring that the ProductData DataSet appears in the DataSet selector, click the ellipses ("...") to its right.
The DataSet Properties dialog opens once again, defaulting to the Query tab, as before.
10. Click the Filters tab on the DataSet Properties dialog.
11. Replace the contents of each of the respective columns of the first line of the Filters section with the details depicted in Table 3 (again, nothing need be done with the fourth column).
The DataSet Properties dialog, Filters tab, appears as shown in Illustration 34.