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 Jan 16, 2008

Reporting Services: Customize Automatically Created Parameter Support Objects - Page 4

By William Pearson

Practice

Having added the requested date-related parameters in the foregoing Preparation section, we are ready to begin making modifications to the client’s existing report to meet the two requirements they have communicated. We will first eliminate the “All Periods” selection that appears within the picklists for the new parameters at runtime (the “focus procedure” in this, the first half of our article). Then, in Part II of this article, we will begin the construction of example intuitive defaults for the new report parameters, to provide illustrations of ways our client colleagues might approach similar reporting needs.

Procedure: Removing the “All” Selection from Auto-Created Parameter Picklists in Reporting Services 2005

We noticed in our earlier “trial run” of the report (where our intent was to verify the operation of our newly added Date parameters) that an “All Periods” option appeared atop each picklist. While I have had myriad clients state that they did not want this option to be available, the fact is that Reporting Services is delivering exactly what is requested via the datasets that were automatically created to support the newly added parameters. The dataset created to support each parameter simply does not filter, by default, the dimensional levels retrieved from the dataset and presented in the runtime picklist, which, as we shall see, means that the “All” level is retrieved among the members of the desired level.

The good news is that provision has been made, within the dataset, to entrain the level number as a data column. We can easily perform our own filtering via this column, as we will do in the following steps.

1.  Click the Data tab.

We arrive at the Data view, as we did earlier.

2.  Using the Dataset selector on the left side of the Data tab toolbar, select the DateCalendarYear dataset, as shown in Illustration 16.


Illustration 16: Select the DateCalendarYear Dataset ...

The MDX Query Builder opens, exposing the MDX that was created (to populate the Report Parameter supporting dataset for the new parameter) as an activity in the chain of events triggered by our addition of the parameterized filter for DateCalendarYear in the Preparation section above. Let’s retrieve the dataset to examine its composition.

3.  Click the Execute Query button atop the Data tab.

4.  Data populates the Data pane, and appears similar to that depicted in Illustration 17.


Illustration 17: The Retrieved Parameter Support Dataset

The column upon which we will focus is the ParameterLevel column. This column contains the values of a calculated member, [Measures].[ParameterLevel], which represents the data retrieved from the AdventureWorks cube by the following expression: [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL. This expression simply generates a number for the dimensional level at which the ParameterCaption (the label seen in the picklist by the information consumer at runtime) and the ParameterValue (the corresponding MDX value passed by Reporting Services as filter criteria in generating the data selected in the report at runtime) belong.

NOTE: For more detail surrounding the MDX .Ordinal function, see MDX Numeric Functions: The .Ordinal Function. For information on the .Level function, see various articles throughout my MDX Essentials series (of which the .Ordinal function article is also a member) at Database Journal.

To eliminate the “All Periods” selection from our Year parameter picklist, we have only to implant a dataset filter at this stage.

5.  Ensuring that the DateCalendarYear dataset appears within the Dataset selector, click the ellipses (“...”) button that appears to the selector’s immediate right, as shown in Illustration 18.


Illustration 18: Opening the Dataset Dialog

The multi-tabbed Dataset dialog for the DateCalendarYear dataset opens.

6.  Click the Filters tab.

7.  In the Filters table on the tab, click the top row in the Expression column to activate the downward-pointing selector.

8.  Select =Fields!ParameterLevel.Value within the selector, as depicted in Illustration 19.


Illustration 19: Selecting the Parameter Level Value Expression ...

9.  Ensure that the “=” sign occupies the Operator column to the immediate right (it is populated by default when we select an entry for the Expression column).

10.  Click the Value column to the right of the Operator column for the same row, and type the following into the box:

=1

The Filters tab of the Dataset dialog appears, with our input, as shown in Illustration 20.


Illustration 20: The Filters Tab of the Dataset Dialog with Our Input

11.  Click OK, to accept our modifications, and to close the Dataset dialog.

The dialog closes, and we return to the MDX Query Builder. At this point, we should note that, should we execute the query once again, we will see no difference in the dataset that is returned: the filter is not applied at this level, although it is enforced when we run the report, as we shall see a few steps below.

12.  Select the DateCalendarQuarterofYear dataset within the dropdown Dataset selector.

13.  Click the Execute Query button atop the Data tab to retrieve the dataset.

The Query Parameters dialog appears. It is here that we provide a default value for the Year parameter, to enable the query to execute. In effect, passage of the Year parameter value, at execution time of the Quarter dataset, is the mechanism behind the cascading nature of the parameter picklists.

14.  In the table labeled Specify details of the parameters defined in the query, click the second row in the Default column (the row containing DateCalendarYear) to activate the downward-pointing selector.

15.  Expand All Periods in the selector picklist, and place a check in the box to the left of CY 2003 within the selector, as depicted in Illustration 21.


Illustration 21: Select a Default for the DateCalendarYear Parameter ...

16.  Click OK to accept the new default.

17.  Click OK on the Query Parameters dialog to save our input and to dismiss the dialog.

Data populates the Data pane, and appears similar to that shown in Illustration 22.


Illustration 22: The DateCalendarQuarterofYear Dataset Appears ...

Here again we see that the “All” level value is zero (“0”). We will, therefore, filter the data supporting the corresponding parameter picklist to Level 1, as we did for the DateCalendarYear dataset earlier.

18.  Click the ellipses (“...”) button that appears to the immediate right of the Dataset selector, as we did earlier.

The Dataset dialog for the DateCalendarYear dataset opens.

19.  Click the Filters tab.

20.  In the Filters table on the tab, click the top row in the Expression column to activate the downward-pointing selector, once again.

21.  Select =Fields!ParameterLevel.Value within the selector, as we did for the DateCalendarYear dataset earlier.

22.  Ensure that the “=” sign occupies the Operator column to the immediate right, once again.

23.  Click the Value column to the right of the Operator column for the same row, and type the following into the box:

=1

24.  Click OK, to accept our modifications, and to close the Dataset dialog.

The dialog closes, and we return to the MDX Query Builder, once again. We are now ready to perform level filtering on the last of our date-related datasets, DateMonthofYear.

25.  Select the DateMonthofYear dataset within he dropdown Dataset selector.

26.  Click the Execute Query button atop the Data tab to retrieve the dataset.

The Query Parameters dialog appears, as it did for the DateCalendarQuarterofYear dataset earlier. We will provide a default value for both the Month and Quarter parameters, at this point, to enable the query to execute.

27.  In the table labeled Specify details of the parameters defined in the query, click the second row in the Default column (the row containing DateCalendarYear) to activate the downward-pointing selector.

28.  Expand All Periods in the selector picklist, and place a check in the box to the left of CY 2003 within the selector, as we did earlier.

29.  Click OK to accept the new default.

30.  Click the third row in the Default column (the row containing DateCalendarQuarterofYear) to activate the downward-pointing selector.

31.  Expand All Periods in the selector picklist, and place a check in the box to the left of CY Q3 within the selector, as we did earlier.

32.  Click OK to accept the new default.

The Query Parameters dialog appears, with our input, as that depicted in Illustration 23.


Illustration 23: The Query Parameters Dialog for the DateMonthofYear Dataset with Our Input

33.  Click OK on the Query Parameters dialog to save our input and to dismiss the dialog.

Data populates the Data pane, and appears similar to that shown in Illustration 24.


Illustration 24: The DateMonthofYear Dataset Appears ...

Once again we see that the “All” level value is zero (“0”). We will filter the data supporting the corresponding parameter picklist to Level 1, as we did for the DateCalendarYear and DateCalendarQuarterofYear datasets earlier.

34.  Click the ellipses (“...”) button that appears to the immediate right of the Dataset selector, once again.

35.  Click the Filters tab on the Dataset dialog for the DateMonthofYear dataset, which opens next.

36.  In the Filters table on the tab, click the top row in the Expression column to activate the downward-pointing selector.

37.  Select =Fields!ParameterLevel.Value within the selector, as we did for the DateCalendarYear and DateCalendarQuarterofYear datasets earlier.

38.  Ensure that the “=” sign occupies the Operator column to the immediate right, as we did earlier.

39.  Click the Value column to the right of the Operator column for the same row, and type the following into the box, once again:

=1

Having applied the filters required to the new parameters’ supporting datasets, we are now ready to verify the effectiveness of our solution, and to ascertain the removal of the “All Periods” option within the date-related parameters’ picklists at runtime.



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