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 Aug 15, 2005

MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 8

By William Pearson

Now that we have put the primary group, from which we will effect our default drill down presentation, in place and labeled, we will turn to the control of visibility in the next group level, BrandSales_Product_Department. We will do so from the Matrix Properties dialog – Group tab, to which we will return once again.

17.  From the Layout view, once again, click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible, as we did earlier.

18.  Right-click the upper left corner of the matrix.

19.  Select Properties from the context menu that appears.

The Matrix Properties dialog opens, defaulted to the General tab, once again.

20.  Click the Groups tab.

21.  Click the BrandSales_Product_Department group in the Rows list box to select it.

22.  Click Edit.

The Grouping and Sorting Properties dialog for the group opens.

23.  Click the Visibility tab.

24.  Under Initial Visibility, click the radio button to the immediate left of the Expression field, to select it.

25.  Type the following expression into the Expression field:

= Fields!Product_Family.Value="Non-Consumable"

(Note that the logic behind "visibility" seems reversed. It is as if we have established a drilled down state based upon the return of a "false" Boolean response. As we shall see, we receive a collapsed state for the Non-Consumable Product Family.)

26.  Click the checkbox the immediate left of the label "Visibility can be toggled by another report item" below the Expression field.

A checkmark appears in the checkbox, and the Report Item selector becomes activated.

27.  Select the Product_Family item in the selector.

This step allows ad hoc drilldown when the default state is collapsed (as will be the case for the Non-Consumable family.).

The Visibility tab - Grouping and Sorting Properties dialog appears as shown in Illustration 31.

Illustration 31: The Visibility Tab – with our Input

28.  Click OK to accept changes and to return to the Groups tab of the Matrix Properties dialog.

29.  Click OK to accept modifications and close the Matrix Properties dialog.

Finally, let's modify the title of the report to represent its new use, with a caption similar to the one that appeared in the draft presented at the conclusion of business requirements gathering for the report. In addition, we will add a report total underneath the rolled up "reconciling" line item for added report utility.

30.  Right-click the textbox containing the report title FoodMart Sales.

31.  Select Properties from the context menu that appears, as we did in an earlier step for the Product Family textbox.

The Textbox Properties dialog appears.

32.  Type the following into the Value box of the dialog:

FoodMart Consumables Analysis

The Textbox Properties dialog appears, with new Value, as depicted in Illustration 32.

Illustration 32: The Textbox Properties Dialog for the Report Title

33.  Click OK to accept changes and close the dialog.

34.  Right-click the Product Family (leftmost) label textbox, once again.

35.  Select Subtotal from the context menu that appears, as shown in Illustration 33.

Illustration 33: Creating a Subtotal for the Product Family Level – and the Report Itself ...

We return again to the Layout view in Report Designer, where we see the new Total in evidence. We are now ready to verify the results of our work.

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