Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 17, 2004

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

By William Pearson



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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM