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

November 17, 2004



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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers