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.
Illustration 31: DataSet
Properties Dialog - ProductData DataSet
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.
|
Expression
|
Operator
|
Value
|
|
=Fields!Store_Sales.Value
|
>
|
=0
|
Table 2: Input for the
Respective Columns of the Filter Definition
The DataSet
Properties dialog, Filters tab, appears as shown in Illustration
32.
Illustration 32: DataSet
Properties Dialog - Filters Tab
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.
Illustration 33: Processing
Errors Dialog - Comparison Failure
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).
|
Expression
|
Operator
|
Value
|
|
=Cstr(Fields!Store_Sales.Value)
|
>
|
="0"
|
Table 3: Input for the
Respective Columns of the Filter Definition
The DataSet
Properties dialog, Filters tab, appears as shown in Illustration
34.
Illustration 34: DataSet
Properties Dialog - Filters Tab: New Input
» 
Comment and Contribute
