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 8

By William Pearson

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:


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:


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:


27.  Select the following by clicking the selector for Value, just below Name, to replace the current "= Fields!Brand_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.

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