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 Aug 18, 2008

Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II - Page 3

By William Pearson

Next, we will add a similar filter to the TimeMonth dataset. The steps are almost identical as those we took for the TimeYear dataset; the only material difference is that the ParameterLevel for the Month level within the Date dimension is identified by the number “4”.

16.  Select TimeMonth, within the Dataset selector on the Data tab.

17.  Click the Edit Selected Dataset (“...”) button to the right of the Dataset selector, as we did for the TimeYear dataset earlier.

The Dataset dialog appears, once again.

18.  Click the Filters tab on the Dataset dialog.

19.  Click the Expression box in the top row of the table labeled Filters on the Filters tab, to activate its downward pointing selector arrow, as we did for the TimeYear dataset earlier.

20.  Using the selector, select the “=Fields!ParameterLevel.Value” option, exactly as we did for the TimeYear dataset.

21.  Ensure, once again, that an “equals” (“ = ”) sign appears in the Operator column, to the right of the Expression column, within the same row of the Filters table.

22.  Type the following into the Value column, to the right of the Operator column, within the same row:

=4

The Dataset dialog, along with our settings, appears this time as depicted in Illustration 8.


Illustration 8: The Dataset Dialog, with Our Input

23.  Click OK to accept our input, and to dismiss the Dataset dialog.

Let's take a quick look at the runtime picklists for the date-related parameters, to ascertain the effectiveness of our dataset filters, as well as to verify their satisfactory operation.

24.  Click the Preview tab next, to execute the report, once again.

The report runs, and then displays the drop-down parameters as specified in the preceding steps, including the defaults requested.

25.  Click the downward pointing arrow to the right of the Month parameter selector, to expose the parameter picklist.

The parameter picklists appears, displaying months as partially shown in Illustration 9.


Illustration 9: The Month Parameter Picklist Appears...

As we can see, our filter has been effective in populating the parameter picklist with months only. (If we click the downward pointing arrow to the right of the Year parameter selector, we can see that there, too, the correct parameter level only is displayed.) But while our dataset filters appear to be effective, we note in the Month parameter picklist that the months displayed are not restricted to FY 2005. This indicates that the Month parameter picklist is not cascading appropriately.

As is probably obvious, we must perform some additional steps before the new Month parameter picklist cascades correctly. Moreover, even though the parameter picklists in general appear to function at the Preview tab level, we will need to perform the steps to make them actually work (to make the month / date selections and the “top count” capability actually restrict the presentation to the number of “top” values specified, etc.). These steps lie within the realm of the underlying dataset query, within which we will be working in the next section.

Before we do that, let’s return to the report parameters dialog to conclude this section with a few cosmetic changes.

26.  Click the Data tab to return to the Data view.

27.  Click Report --> Report Parameters to re-open the Report Parameters dialog, as before.

28.  Ensure selection of the TimeYear parameter in the Parameters pane of the dialog.

29.  Within the Prompt box, found within the Properties section (the third input box from the top) of the Report Parameters dialog, replace the existing prompt name (“TimeYear”) with the following:

Year:

30.  Within the Label field selector, the third setting from the top within the Available values section of the dialog (about midway down and underneath the Properties section), select Parameter Caption as presented in Illustration 10.


Illustration 10: Select Parameter Caption for the Label Field Reference ...

The Report Parameters dialog, including our setting changes, appears as depicted in Illustration 11.


Illustration 11: Modified Settings for the First Automatically Created Report Parameter

31.  Select the TimeMonth parameter within the Parameters pane on the left side of the Report Parameters dialog.

32.  Within the Prompt box, found within the Properties section of the Report Parameters dialog, once again, replace the existing prompt name (“TimeMonth”) with the following:

Month:

33.  Within the Label field selector, the third setting from the top within the Available values section of the dialog, select Parameter Caption, as we did for the TimeYear report parameter.

The Report Parameters dialog, including our setting changes, appears as shown in Illustration 12.


Illustration 12: Settings for the Second Automatically Created Report Parameter

Finally, let’s make some settings for the new LookBackSpan parameter. As we noted in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I, the TopCountSpan parameter will differ from the other two automatically created parameters, particularly in the fact that it will not reference a corresponding, automatically created dataset – a situation we both described and explained in Part I. Because the parameter picklist will simply offer a range of numerals as selection options, we will reference those numerals through direct specification within the Report Parameters dialog (although other approaches exist to support such a parameter, such as using a manually created dataset, etc.)

34.  Select the TopCountSpan parameter within the Parameters pane on the left side of the Report Parameters dialog.

35.  Within the Prompt box, again within the Properties section of the Report Parameters dialog, replace the existing prompt name (“TopCountSpan”) with the following:

No. Top Items:

36.  Within the Available values section of the dialog, ensure that the Non-queried radio button is selected.

37.  Populate the table that appears to the right of the Non-queried radio button as specified in Table 1 below.

Label

Value

1

1

2

2

3

3

4

4

5

5

6

6

7

7

8

8

9

9

10

10


Table 1: Label and Value Input for the Non-queried Available Values Table

The Report Parameters dialog, including our setting changes, appears as partially depicted in Illustration 13.


Illustration 13: Settings for the Third Automatically Created Report Parameter (Partial View)

38.  Click OK to accept our changes and to dismiss the Report Parameters dialog.

As we mentioned earlier, we must perform some additional steps before the new parameters actually work. These steps will need to be accomplished within the syntax of a couple of underlying dataset queries, within which we will be working in the next section.



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