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 8

By William Pearson

Set Up the Grouping and Group Member Sorting Report Parameters

We are ready at this point to add a couple of new report parameters. We will reference these parameters later in our procedure.

1.  Select Report -> Report Parameters ..., from the main menu, as shown in Illustration 32.

Illustration 32:  Select Report --> Report Parameters ...
Illustration 32: Select Report --> Report Parameters ...

The now empty Report Parameters dialog opens.

2.  Click Add to begin adding our first report parameter.

3.  Type the following into the Name box in the Properties section (at the top of the right side of the dialog):

RowGroupBy

4.  Type the following into the Prompt box in the Properties section (the second box down from the Name box ):

Group Rows By:

5.  Clear any check marks in the lower part of the Properties section.

6.  In the Available values section (just below the Properties section on the right side of the dialog), click the From query radio button to select it.

The Dataset, Value field and Label field selectors appear.

7.  Click the downward pointing arrow on the Dataset selector (currently displaying the ProductData selection) within the Data tab.

8.  Select the new RowGroup dataset.

9.  In like manner, select ParameterValue within the Value field.

10.  Select ParameterCaption within the Label field.

11.  Leave all other settings at default.

The Report Parameters dialog for the new RowGroupBy parameter appears, with our input, as depicted in Illustration 33.

Illustration 33:  Settings for the New RowGroupBy Parameter
Illustration 33: Settings for the New RowGroupBy Parameter

We will next create a report parameter to for use in sorting the members of the group we select at runtime.

12.  Click Add, once again, in the bottom left corner of the still open Report Parameters dialog.

13.  Type the following into the Name box in the Properties section (at the top of the right side of the dialog):

RowSortBy

14.  Type the following into the Prompt box in the Properties section (the second box down from the Name box ):

Sort Rows By:

15.  Clear any check marks in the lower part of the Properties section, as we did with the first report parameter above.

16.  In the Available values section (just below the Properties section on the right side of the dialog), click the From query radio button to select it.

The Dataset, Value field and Label field selectors appear, as we saw earlier.

17.  Ensure that the new RowGroup dataset is selected within the Dataset selector.

18.  In like manner, select ParameterValue within the Value field.

19.  Select ParameterCaption within the Label field.

20.  Leave all other settings at default.

The Report Parameters dialog for the new RowSortBy parameter appears, with our input, as shown in Illustration 34.

Illustration 34: Settings for the New RowSortBy Parameter
Illustration 34: Settings for the New RowSortBy Parameter

21.  Click the OK button on the Report Parameters dialog to accept the new parameters and to dismiss the dialog.

Let’s preview the report at this stage, simply to ascertain that the new parameters appear to work properly (we will have to do some further work on the Layout tab before they actually affect the report display – we just want to determine that the picklists appear appropriate, etc.).

22.  Click the Preview tab, once again.

The two new report parameters appear atop the report - actually execution is held up until we make a selection here. We can click the selector arrows and see that the three grouping / sorting criteria appear in each parameter picklist, as depicted (for the Group Rows By parameter) in Illustration 35.

Illustration 35:  The Parameter Picklists (Only the Group Rows By Parameter is Shown Here) Appear to Work Properly
Illustration 35: The Parameter Picklists (Only the Group Rows By Parameter is Shown Here) Appear to Work Properly

We are now ready to “hook things up” within the Layout tab of the report so that the new parameters actually work, and so that the report reflects our selections in a user-friendly manner.



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