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 Sep 23, 2008

Mastering OLAP Reports: Parameterized Grouping - Page 9

By William Pearson

Set Up the Grouping and Group Member Sorting Report Parameters

We will move now to the Layout tab, where we have several adjustments to make to leverage our new report parameters.

1.  Click the Layout tab.

2.  Click within the Matrix data region, to cause the gray header bars to appear on top and to the left.

3.  Right-click the upper left corner, where the gray bars meet, as we did in the preparation steps above.

4.  Select Properties from the context menu that appears, as we did earlier.

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

5.  Click the Groups tab.

6.  In the Rows section of the Groups tab (in the upper half of the tab), select the second group, named matrix1_Category.

7.  Click the Edit button, once again.

The Grouping and Sorting Properties dialog appears, as before, defaulted to its General tab.

8.  Atop the General tab, change the existing Name to the following:

matrix1_RowGroupBy

9.  Click the row containing the Expression value (currently the expression is “=Fields!Category.Value”), within the Group on list, to enable the selector.

10.  Select <Expression...> within the selector (the top entry), as shown in Illustration 36.

Illustration 36: Replacing the Existing Group Expression ...
Illustration 36: Replacing the Existing Group Expression ...

The Expression Editor opens.

11.  Replace the expression in the upper portion of the Editor with the following:

=Fields(Parameters!RowGroupBy.Value).Value

Recall that the intent is to enforce grouping upon our selection via the runtime report parameter (RowGroupBy). Reporting Services once again allows us to answer the need via an expression.

The Expression Editor appears, with our modification, as depicted in Illustration 37.

Illustration 37:  The Expression Editor with Our Substitution in Place
Illustration 37: The Expression Editor with Our Substitution in Place

12.  Click OK to accept our modification, and to dismiss the Expression Editor.

13.  Click the Sorting tab.

14.  In the Expression box of the section labeled Sort on, select <Expression...> within the selector (the top entry), as shown in Illustration 38.

Illustration 38: Replacing the Existing Sorting Expression ...
Illustration 38: Replacing the Existing Sorting Expression ...

The Expression Editor opens.

15.  Type, or cut and paste, the following expression into the upper portion of the Editor with the following:

=Fields(Parameters!RowSortBy.Value).Value 

The Expression Editor appears, with our addition, as depicted in Illustration 39.

Illustration 39:  The Expression Editor with Our Newly Added Syntax
Illustration 39: The Expression Editor with Our Newly Added Syntax

16.  Click OK to accept our modification, and to dismiss the Expression Editor.

We return to the Sorting tab, where we see, in the Direction setting adjacent to the Expression setting, a default of Ascending, as shown in Illustration 40.

Illustration 40:  The Sorting Tab with Our Input ...
Illustration 40: The Sorting Tab with Our Input ...

17.  Leaving the Direction setting at its default of Ascending, click OK to accept our input and to dismiss the Grouping and Sorting Properties dialog.

18.  Click OK on the Matrix Properties dialog next, to accept all changes and dismiss the dialog.

We are returned to the Layout tab, where we will perform a couple of remaining modifications to make the new report more consumer-friendly at runtime. First, let’s modify the subgroup’s label (we left it at “=Fields!Category.Value” when we created the column in earlier steps).

19.  Right-click the cell containing “=Fields!Category.Value”.

20.  Select Expression ... from the context menu that appears, as depicted in Illustration 41.

Illustration 41: Replacing the Existing Label Expression ...
Illustration 41: Replacing the Existing Label Expression ...

The Expression Editor opens.

21.  Replace the expression within the upper pane (currently “=Fields!Category.Value”) with the following:

=Fields(Parameters!RowGroupBy.Value).Value

The Expression Editor appears, with our substitution, as shown in Illustration 42.

Illustration 42:  The Expression Editor with Our Substituted Syntax
Illustration 42: The Expression Editor with Our Substituted Syntax

22.  Click OK to accept our modification, and to dismiss the Expression Editor.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date