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

Wednesday Nov 17th 2004 by William Pearson
Share:

MSAS Architect Bill Pearson provides hands-on guidance in managing nulls at the report level, when filtering for non-string values from an OLAP data source.

About the Series ...

This article is a member of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, along with the applications upon which it relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

This article also relies upon MSSQL Server Analysis Services ("MSAS") for a sample cube that will be used as a data source by the report with which we work in our practice exercise. For information surrounding the installation of MSAS and the sample OLAP databases and cubes that accompany the typical installation of MSAS, see the associated Books Online.

About the BlackBelt Articles ...

One of the greatest challenges in writing tutorial / procedural articles is creating each article to be a freestanding document that is complete unto itself. This is important, because it means that readers can complete the lesson without reference to previous articles or access to objects created elsewhere. A casual visitor to any article within a given series, who has presumably found it via a search, should have no more difficulty completing the article (and meeting immediate needs) than a visitor who is completing the articles in sequence, to learn the concepts presented in an ongoing manner. To meet the needs of both types of readers, while keeping articles concise and yet complete, becomes particularly challenging when we set as an objective the coverage of a specific technique that surrounds one or more components of a report, a given administrative function surrounding all reports, and other scenarios where the focus of the session is not the creation of reports, per se, but where a report or reports has to be in place before we can begin to cover the material with which the article concerns itself.

Many other factors add even more preparatory baggage, including the need in a given lesson to have a particular condition (such as null fields in a data source, for example) for which we set about providing a remedy, or providing a means of exploit. When we combine the overhead involved in just "setting the scene," the focus of the article is drowned in myriad steps simply to get to a reasonable starting point.

The BlackBelt articles represent an attempt to circumvent this. We will attempt to use existing report samples or other "prefabricated" objects that either come along as part of the installation of the applications involved, or that are readily accessible to virtually any organization that has installed the application. While we will often have to make modifications to the sample involved (we will actually create a copy, to allow the original sample to remain intact), to tune it to provide the backdrop we need to proceed with the object or procedure upon which we wish to concentrate, we will still save a great deal of time and distraction in getting to our objective. In some cases, we will still have to start from scratch with preparation, but my intention with the BlackBelt articles will be to avoid this, if at all possible.

Overview

As we discuss in my article MDX Essentials: Logical Functions: The IsEmpty() Function in working with multidimensional data sets, we are often confronted with empty cells - data is often sparse in these sets by their very physical nature. Because, as a simple example, every product might not be sold at every store in every time period (to cite an instance from the dimensions of the FoodMart2000 sample environment), we will see empty cells in abundance in a data set that contains intersects of these dimensions. (Particularly in working with crossjoins of any magnitude, we will encounter many empty cells, as a general rule.) Empty cells mean nulls, and nulls can mean incorrect results in using MDX to support analysis in reporting.

Issues that range between failure of the report to execute and mere inconveniences (such as inordinate white space, bizarre characters, and other formatting gaffes) can be the consequence of entraining these nulls into a reporting application. Reporting Services is no exception, and sooner or later, most report authors are going to find themselves facing a need to manage null fields. While one of the many beauties of the MSAS / MSSQL Server / Reporting Services combination is the fact that this is easily done within more than one layer (for instance, within the OLTP database, OLAP cube, or the report itself), we may find ourselves working with a combination or environment where we do not have all of these options.

It is the type of scenario where we have to manage the nulls within Reporting Services, for a report whose data source is an OLAP cube, which this article addresses. Management of nulls in reports based upon relational sources can be accomplished in similar ways. (My tendency to focus on OLAP reporting is based upon the fact that references abound that teach reporting for relational sources in Reporting Services, but virtually no references to OLAP reporting exist in any of the new books that have recently appeared; articles on the subject are also universally, well, sparse.) In this session, we will:

  • Make structural changes to a copy of a sample report, based upon a sample cube, to meet the business requirements of a hypothetical group of information consumers;
  • Modify the report to exhibit null fields as a basis for our practice example;
  • Discuss management of nulls from the DataSet of the report;
  • Practice two approaches to managing the nulls by applying filtering techniques that work within Reporting Services;
  • Discuss the results that each of the filtering techniques can provide;
  • Preview the report to ascertain the effectiveness of each of the techniques.

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.

Product Family

Product Department

Product Category

Product Sub-Category

Product Brand Name

Product Name

Food

>

Baking Goods

>

Jams and Jellies

>

Jelly

>

CDR

>

CDR Grape Jelly

Table 1: Drilldown Path to an Example Null in the Sales Cube

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


Illustration 1: Empty Cells in Data View (Partial Display)

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.


Illustration 2: Current FoodMart Sales Report - Partial View

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).



Hands-On Procedure

Preparation


Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.


2.  Navigate to the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

The equivalent on my PC appears as shown in Illustration 3.



Click for larger image

Illustration 3: Beginning in Microsoft Visual Studio .NET 2003 ...

3.  Select File -> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 4.


Illustration 4: Selecting a New Project

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

RS011

8.  Navigate to a location in which to place the Report Project files.

The New Project dialog appears, with our additions, as shown in Illustration 5.


Illustration 5: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 6.


Illustration 6: The New Project Appears in the Solution Explorer

Having created a Report Project, we are ready to proceed with creating the new report.



Modify the FoodMart Sales Report


In this section, we will copy the existing FoodMart Sales report, and then open it in Reporting Services' Report Designer, where we will begin the modifications that the information consumers have requested.



Set up a Data Connection for the FoodMart Sales Report

Our next step is to set up a Data Connection. As we have noted numerous times throughout our series, Reporting Services can connect with, and create the DataSets it needs from, virtually any ODBC or OLE DB-compliant data source (in addition to the obvious MSSQL Server and MSAS data stores). .NET-based API's add the potential for other data sources, assuming that you have a legacy, or otherwise "alternative," scenario on your hands.

Let's set up a Connection to support the DataSet within our practice example.

1.  Right-click the Shared Data Sources folder underneath the new RS011 project tree in the Solution Explorer.

2.  Select Add New Data Source from the context menu that appears, as depicted in Illustration 7.




Illustration 7: Select Add New Data Source from the Context Menu

The Data Link Properties dialog appears, defaulted to the Connection tab.

3.  Click the Provider tab, to select it.

4.  Select Microsoft OLE DB Provider for OLAP Services 8.0 from the Provider list box, as shown in Illustration 8.


Illustration 8: Selecting Microsoft OLE DB Provider for OLAP Services 8.0 - Providers Tab

5.  Click Next, to move to the Connection tab.

6.  In the Data Source box, type the name of the server upon which MSAS resides (mine is MOTHER1 in the illustration that follows).

7.  Type in the server name again in the Location box.

8.  Provide the appropriate authentication information to access the MSAS environment on the server you have selected.

I am using Windows NT Integrated Security, as this is not a production environment. For a production environment, selections here require careful consideration.

9.  Select FoodMart 2000 in the list of data sources that appear when we click the down-arrow selector button at the next box (and thus receive confirmation that our server / authentication information is adequate to display the sources) at the top of the Data tab).

The completed Data Link Properties - Connection tab appears as depicted in Illustration 9.


Illustration 9: Data Link Properties - Connection Tab

10.  Click the Test Connection button to confirm connectivity.

The Microsoft Data Link message box appears, indicating a successful test, as shown in Illustration 10.


Illustration 10: Testing Positive for Connectivity ...

11.  Click OK to close the message box.

12.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

Having established the data source, we are now ready to create a copy of the FoodMart Sales report, which we will then modify to meet the expressed requirements of the information consumers.



Create a Copy of the FoodMart Sales Report


As we have noted, we will be working with a copy of the FoodMart Sales report, to keep the original sample intact for easy access and use at another time.


1.  Right-click the Reports folder underneath the newly created shared data source, in the Solution Explorer.


2.  Select Add -> Add Existing Item from the cascading context menus that appear, as depicted in Illustration 11.



Click for larger image

Illustration 11: Editing the New DataSet

The Add Existing Item - RS011 dialog appears.

When we installed Reporting Services, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

 C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports

3.  Using the Add Existing Item - RS011 dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - RS011 dialog, having been pointed to the Samples folder (which contains the FoodMart Sales report we seek), appears as shown in Illustration 12.


Illustration 12: Navigating to the Sample Reports Folder ...

4.  Right-click the FoodMart Sales report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 13.


Illustration 13: Performing a Quick Copy of the FoodMart Sales Report

6.  Right-click somewhere in the white space to the right of the report files within the dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 14.


Illustration 14: Select Paste to Complete Copying ...

A copy of the FoodMart Sales report appears, with "Copy of" appended at the front of the name of the file, within the dialog.

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

10.  Type the following name in place of the highlighted existing name:

Foodmart Sales - Filtered Dataset.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The copy of the FoodMart Sales sample report appears as depicted in Illustration 15.


Illustration 15: The New Report, FoodMart Sales - Filtered Dataset.rdl

11.  Select the new file by clicking it, if necessary.

12.  Click Open on the dialog box to add the new report to report project RS011.

FoodMart Sales - Filtered Dataset.rdl appears in the Reports folder, within the RS011 project tree in the Solution Explorer, as shown in Illustration 16.


Illustration 16: FoodMart Sales - Filtered Dataset.rdl Appears in Solution Explorer

Having created a copy of the functional report, we are now ready to make the modifications requested by the FoodMart information consumers.

Modify the FoodMart Sales Report Structure to Meet Business Requirements

Let's open the new clone of the FoodMart Sales report, and begin our alterations. We will first make the layout changes to bring about the required drilldown, and then we will focus upon the filtering considerations that form the nucleus of our session.

1.  Double-click the FoodMart Sales - Filtered Dataset.rdl, within the Reports folder in Solution Explorer, to open it in Report Designer.

The report opens, displaying the report on the Layout tab.

2.  Select the Preview tab, to begin a quick check of connectivity and overall report operation.

3.  Select Food in the parameter selector box that appears atop the Preview area, as depicted in Illustration 17.


Illustration 17: Select the Food Product Family in the Parameter Picklist

4.  Click the View Report button to execute the clone report.

The report runs and returns the data associated with the Food Product Family that resides in the MSAS Sales cube. This indicates that our data source is functional, and that the DataSet that the report is retrieving is operating.

5.  Drill down on Baking Goods (click the "+" sign to its left), which appears near the top left of the report, to expose its underlying groups.

6.  Drill down on Jams and Jellies group that appears to the right of Baking Goods.

7.  Drill down on Jelly, which appears to the right of Jams and Jellies.

At this point we see the lowest level of the row axis, Product Brand Name, appear. We note no empty cells at this stage. We know that we need to alter the drill down capability to go one level below, and instead of the Product Brand Name, substituting Product Name, to meet the requirements we have been given. A portion of our view of the report at this stage appears in Illustration 18.


Illustration 18: The FoodMart Sales - Filtered Dataset.rdl Report with Drill-down Example

Our next step involves swapping the Product Name level of the Product dimension with the current lowest level of the report drill down, Product Brand Name. Before we can make the field assignments on the Layout tab, we must ascertain that the needed level is included in the DataSet.

8.  Click the Data tab.

9.  Ensure that the ProductData DataSet is selected.

The DataSet definition (an MDX query) appears as shown in Illustration 19.


Illustration 19: ProductData DataSet - Current Definition

In examining the MDX behind the DataSet, we note a limiting factor in reaching our objectives. The level to which the Descendants() function extends is the Product Brand Name ([Product].[BrandName]) level, as I have circled above. This means that we need to adjust the MDX to include the next lower level, Product Name. Let's make the changes with the following steps.

10.  Modify the second line of the MDX query (the Row Axis definition) from its present form of:

{  Descendants([Product].[All Products], [Product].[Brand Name], LEAVES)  } ON ROWS,

to the following:

{  Descendants([Product].[All Products], [Product].[Product Name], LEAVES)  } ON ROWS,

(The only change is the switch of [Product Name] for [Brand Name] in one place, circled in Illustration 19 above.)

The modified DataSet appears as shown in Illustration 20. (I have circled the change).


Illustration 20: The FoodMart Sales - Filtered Dataset.rdl Report with Modification (Circled)

11.  Click the Run (!) button atop the Data tab to execute the query.

The query executes. An examination of the returned DataSet confirms the selection of the level element we require to modify the report, Product Name, as depicted in Illustration 21.


Illustration 21: The Needed Field Appears in the Returned DataSet (Partial View)

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 ...

19.  Click the Edit button to the right of the highlighted group.

The Grouping and Sorting Properties dialog - General tab appears.

20.  Replace the contents of the Name box atop the tab, BrandSales_Brand_Name, with the following:

Product_Product_Name

21.  In the Group On: list, use the selector button to the right of the top line in the list, to modify the existing expression, =Fields!Brand_Name.Value, to the following expression:

=Fields!Product_Product_Name.Value

Illustration 26 depicts scrolling down on the selector to select the replacement expression.

Click for larger image

Illustration 26: Select the New Row Group Expression ...

The Grouping and Sorting Properties dialog - General tab appears, as shown in Illustration 27.


Illustration 27: Grouping and Sorting Properties Dialog - General Tab

We are returned to Matrix Properties dialog - Group tab, where we note that the bottom row group now reflects the name change we made in the Grouping and Sorting Properties dialog.

22.  Click OK to accept modifications, and to close the dialog.

23.  Click OK to close the Matrix Properties dialog.

We return to the Layout tab. We now need to make another modification to continue with the drill-down-related changes requested by the information consumer group.

24.  Right-click the textbox fourth column from the left / right (currently showing "= Fields!Brand_Name.Value" in the Layout view.

25.  Select Properties from the context menu that appears.

The Textbox Properties dialog appears.

26.  Replace "Brand_Name" in the Name box with the following:

Product_Name

27.  Select the following by clicking the selector for Value, just below Name, to replace the current "= Fields!Brand_Name.Value" :

=Fields!Product_Product_Name.Value

The Textbox Properties dialog appears (modifications circled) as depicted in Illustration 28.


Illustration 28: Textbox Properties Dialog - Modifications Circled

28.  Click OK to close the Textbox Properties dialog.

We return to the Layout tab. We have one more modification to make to complete the structural changes that the intended audience has requested: to remove the capability to drill down from Year to Quarters.

29.  Click the Quarter textbox, containing the expression =Fields!Quarter.Value, which appears below the Year textbox (containing expression =Fields!Year.Value), as shown in Illustration 29.


Illustration 29: Select the Quarter Textbox

30.  Press the Delete key.

The Quarter textbox vanishes. This removes the capability to drill down to Quarters from the Year, as requested by the information consumers for this report.

We are now ready to test the report to preview the results of our layout changes.

31.  Click the Preview tab.

32.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

33.  Click the View Report button to execute the report.

The report runs and returns the data associated with the Food Product Family that resides in the MSAS Sales cube. We will drill down again, to ascertain the effectiveness of our layout modifications.

34.  Drill down on Baking Goods (click the "+" sign to its left), which appears near the top left of the report, to expose its underlying groups.

35.  Drill down on the Jams and Jellies level that appears to the right of Baking Goods.

36.  Drill down on Jelly, which appears to the right of Jams and Jellies.

At this point we see the new lowest level of the row axis, Product Name, appear. We note, too, the appearance of the nulls we identified earlier. A portion of our view of the report at this stage appears in Illustration 30.


Illustration 30: The FoodMart Sales - Filtered Dataset.rdl Report (Partial View) with Layout Modifications

Therefore, we confirm that we have met the structural portion of the business requirements of the intended audience. We now need to restrict the report to present only those products with sales (or, in other words, to filter out any products whose Sales values are null). We will concentrate upon the construction of the filters to restrict the presentation as requested in the following section - and then explore the effects of null fields on filtering in general, together with approaches for overcoming those effects to deliver effective filters within our reports.

37.  Select File -> Save All to save our work so far.

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



The Visual Basic CStr() function is simply typed in to enclose the expression that we selected / typed before. CStr() acts to convert the Store Sales value to a string. This allows a "string-to-string" comparison that, as we shall see, appears to obtain the results we seek.


12.  Click OK to save the Filter setting and close the DataSet Properties dialog.


13.  Click Preview to begin report execution.


The report begins to process, rapidly returning the data selected via the default parameter selection. Let's see if we can locate the known null value that we identified above, to ascertain the effectiveness of the new filter expression.


14.  Select Food in the Product Family parameter atop the report Preview.


15.  Click View Report to execute the report once again.


The report executes and presents the "rolled up" view, as expected.


16.  Drill down on Baking Goods (click the "+" sign to its left), which appears near the top left of the report, to expose its underlying groups.


17.  Drill down on Jams and Jellies group that appears to the right of Baking Goods.


18.  Drill down on Jelly, which appears to the right of Jams and Jellies.


At this point we see the lowest level of the row axis, Product Name, appear. We note that the entry for CDR Grape Jelly, the null we identified to exist in the Sales cube earlier, does not appear. Our filter has thus been shown to be effective, from the perspective of nulls, as depicted in the view of the relevant portion of the report in Illustration 35.



Illustration 35: The FoodMart Sales - Filtered Dataset.rdl Report with Drilldown

NOTE: You can always return to the DataSet dialog - Filters tab, and delete the filter line, and re-run the report to see that the null values would appear with a similar drilldown path, should you wish to verify the operation of the filter.

Let's examine another method for achieving the same end. As in any filter we establish, we can also induce a filter of nulls using a Boolean expression that returns a True or False. One way to do so is construct the expression with an "if-then" component that enables the filter to screen out null values as a part of its operation. Let's return to the filter and explore this approach.

19.  Click the Data tab.

20.  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.

21.  Click the Filters tab on the DataSet Properties dialog.

22.  Click the selector to the right of the Expression cell in the first row of the Expression definition area of the dialog.

23.  Select <Expression> from the dropdown list that appears.

24.  Replace the existing expression with the following expression, in the Expression box (right side of the dialog):

=IIF(Fields!Store_Sales.Value IS NOTHING, 0,    
    Fields!Store_Sales.Value)>0

The Edit Expression dialog appears as shown in Illustration 36.


Illustration 36: Edit Expression Dialog - Expression Added

25.  Click the OK button on the Edit Expression dialog to accept changes and close the dialog.

We are returned to the DataSet Properties dialog, once again, where we will fill out the rest of the Filter line, as follows:

26.  Replace the contents of each of the respective columns of the first line of the Filters section with the details depicted in Table 4 (the Expression column is already complete).

Expression

Operator

Value

(See immediately preceding step)

=

=True

Table 4: Input for the Respective Columns of the Filter Definition

The DataSet Properties dialog, Filters tab, appears as shown in Illustration 37.


Illustration 37: DataSet Properties Dialog - Filters Tab: New Input

All that remains is to test the alternative filter.

27.  Click OK to save the Filter setting and close the DataSet Properties dialog.

28.  Click Preview to begin report execution.

The report begins to process, rapidly returning the data selected via the default parameter selection. Let's see if we can locate the known null value that we identified above, to ascertain the effectiveness of the new filter expression.

29.  Select Food in the Product Family parameter atop the report Preview.

30.  Click View Report to execute the report once again.

The report executes and presents the "rolled up" view, as expected.

31.  Drill down on Baking Goods, to expose its underlying groups.

32.  Drill down on Jams and Jellies, once again.

33.  Drill down on Jelly.

At this point we see the lowest level of the row axis, Product Name, appear, once again. We note, as we did with our first filter, that the entry for CDR Grape Jelly does not appear. Our filter has thus been shown to be effective, eliminating the known nulls in our example, and providing a result set identical to the one afforded via our first filter.

Conclusion ...

In this article, we discussed management of nulls from the DataSet of a report in general, and then focused our attention to those specific cases where we are applying filters in a report whose underlying data source, an OLAP cube, contains nulls. To prepare for our practice example, wherein we met the expressed business requirements of a hypothetical group of information consumers to modify the structure and content of an already existing report, we first made a copy of the report upon which we enacted our modifications.

In addition to structural changes, we took steps to filter out null values in the data source, to present data for only those products experiencing sales in the year under examination. We thus illustrated the complications that are inherent in the process of filtering non-string values, when null cells are present in the OLAP cube from which the report draws its supporting DataSet. We then illustrated two approaches that, while instructive as to the creation of filters in general, also proved effective in the handling of non-string, null values. In each case, we discussed the results obtained in a preview we performed to verify that the filters did, indeed, manage the known nulls in our data source as intended.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved