This
represents an opportunity to highlight another item: the fact that we now have
an incidence of nulls appearing in the DataSet. The nulls did not exist
before, because the DataSet did not extend to the lowest level. Relative to
the example I highlight above (CDR Grape Jelly), nulls did not appear
because the measures were rolled up to the next level of aggregation, and the
nulls in one product were "buried" in the aggregate of the members of
the Product Brand. As we have focused on a lower level, the empty cells
(meaning no activity for the time period we are examining, naturally enough)
show themselves. As I mentioned earlier, this is far more common in reality,
where sparsity is typical and pervasive.
Nulls
present challenges when we work with filters in our DataSets. We will
encounter these in setting up our filters to meet the business requirements
specified by the information consumers. It is for this reason that I am
focusing upon an incidence of a null, so that we can achieve the tandem
objective of exposing basic filter use, together with the management of nulls
within our use of filters. We will return to this in short order.
For now, let's finish
modifications to our report to enable the drill down that the consumers have
requested - to the Product Name level. We know that the Product Name
data now exists in the DataSet; next, we will make changes in the report layout
to exchange the Product Name data for the Brand Name data that
currently holds the lowest level in the physical drilldown.
TIP:
When designing a report in Reporting Services, or any other
enterprise reporting application, always focus on the complete DataSet first.
Making sure you have all the data that the report will require - before
beginning the physical construction of the report, and especially before
investing time in formatting and the like - can save the hours of rework that
is due upon those operating under the "I'll come back to that later"
mentality.
12.
Click the Layout
tab to switch to the Layout view.
13.
Widen the
fourth column (both from left and right, and thus the middle column of the
report) enough to expose the full expression in the textbox, =Fields!Brand_Name.Value,
as shown in Illustration 22.
Illustration 22: Expanded
Column in Layout Tab (Partial View)
We must
make modifications in a couple of places to exchange the expression we need to
replace the Brand Name level with the Product Name level, to
which the information consumers wish to be able to drill.
14.
Click anywhere
within the Matrix data region to activate the gray column and row
headers.
15.
Right-click
the upper right corner of the Matrix data region.
16.
Select Properties
from the bottom of the context menu that appears, as shown in Illustration
23.
Illustration 23: Select
Properties from the Bottom of the Context Menu ...
The Matrix
Properties dialog - General tab appears, as depicted in Illustration 24.
Illustration 24: Matrix
Properties Dialog - General Tab
17.
Click the Groups
tab, to access the first area we need to modify, the group that currently
points to Brand Name.
18.
Click the BrandSales_Brand_Name
item that appears at the bottom of the Rows group list, as shown in Illustration
25.
Illustration 25: Select
BrandSales_Brand_Name Row Group ...
» 