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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 17, 2004

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

By William Pearson

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.


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

MS SQL Archives

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