MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports - Page 7
November 17, 2004
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.
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.
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.
The Matrix Properties dialog - General tab appears, as depicted in Illustration 24.
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.