MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports - Page 2

Managing Nulls in Reporting Services

Objective and Business Scenario

As we have noted, it is often not possible to filter nulls from the OLAP data we are bringing into our report. While the very few samples with which we are provided in an installation of MSAS are remarkably free of sparsity, this certainly does not reflect reality in general. Sparsity, as we have intimated, is a fact of life, but not necessarily something we want to reflect in our reports. We therefore find ourselves needing to suppress the nulls, or to substitute other values for nulls, which, in fact, represent a state of "empty," versus "zero," (an important distinction with which most of us are already familiar, whether from experience with relational or OLAP environments, or a combination of the two), to create pleasing, user-friendly reports.

One of several "learning curve" items in Reporting Services that initially trip some of us up is filtering a DataSet inhabited by nulls. This difficulty can be compounded in situations where we may not want to simply strip out all items with null values - we may want to report upon the very fact that there was no activity in certain cases. Reporting Services manages aggregations fine when nulls are involved, working in conjunction with MSAS; what we are looking for with null filtering is often the accomplishment of presentation objectives.

In the following sections, we will perform the steps required to filter nulls from a report that contains an example of such an "empty" field. To provide a report from whose DataSet we can base our null management practice exercises, we will begin with the FoodMart Sales sample report that is available with the installation of Reporting Services. This report uses the FoodMart 2000 Sales cube that comes along with the installation of MSAS; in the odd event of an installation of Reporting Services without an installation of MSAS (yes, I'm sure someone will remind me that it is, indeed, a plausible scenario), you can find the cube on the MSAS installation disk, or from numerous other sources. (If you have installed Reporting Services, you will need it anyway, to make any use of the FoodMart Sales report, in general.).

The FoodMart Sales report is a reasonably good sample report, just as the cube that underlies it, Sales, is a reasonably good sample cube. We might notice, however, that the cube does not reflect many real world scenarios, as it has very few nulls in operating year 1997 (minimal expense data exists for 1998, which is why we will be working with 1997). However, I did locate one null, and that is all we need for this lesson. The null under consideration is a Food item, CDR Grape Jelly, which we can see from the Data tab of the Sales cube, with Products in the row axis, and with 1997 in the Time dimension. The drilldown path pictured in Table 1 will reveal the null cell in the Data view.

Once drilled down in the Data view, we see the empty cell, as depicted in Illustration 1.

For purposes of our practice procedure, we will assume that information consumers within the Marketing department of the FoodMart organization have expressed the need for modifications of the existing FoodMart Sales report, which currently drills down to the Product Brand Name level, as partially shown in Illustration 2.

The consumers expressed overall delight with the report, but wanted to alter a few features within a similar report with a more limited focus. First, the information consumers wish to be able to drill directly from Product Subcategory to Product Name, instead of Product Brand Name, to be able to see more clearly the Sales results for each product. Second, they tell us that they wish to filter the report to show Cost and Sales values for Products with positive sales for 1997, by Product Name. They do not want the report to list any items that had no sales for the year. Moreover, the consumers specifically state that they do not need the capability to drill down to Quarters, a feature of the current report. The information consumers tell us that they like the other features of the report, however, and prefer for the new report to resemble it in all other respects.

As part of our typical business requirements gathering process, we listen attentively to the details, formulating, in the background, an idea of the steps we need to take in modifying a copy of the report to produce the desired results. Once we grasp the stated need, and confirm our understanding with the intended audience, we begin the process of modifying the FoodMart Sales report to satisfy the information consumers.

Considerations and Comments

Before we can work with the FoodMart Sales sample report, we need to create a Reporting Services project in the Visual Studio.Net 2003 Report Designer environment, within which we will make the requested modifications to a copy of the report. Making the modifications to the report to change the drilldowns, and to filter the displayed results to restrict nulls, are relatively straightforward procedures. However, a few twists lie ahead that will come to light when we attempt the "intuitive" approach to filtering - eccentricities of the filtering process will emerge, for which we will present options for resolution.

If the sample FoodMart 2000 MSAS database was not created as part of the initial MSAS installation, or was removed prior to your beginning this article, please see the MSAS Books Online for the procedure to restore the database, together with the sample cubes. As of this writing, a copy of the samples can be obtained from the installation CD or via download from the appropriate Microsoft site(s).