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 Jul 29, 2008

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

By William Pearson

Next, let’s add a query parameter for Month.

10.  Once again within the Query Parameters dialog, in the newly appearing second row of the table underneath the caption “Specify details of the parameters defined in the query,” click the leftmost cell (containing the placeholder “<Enter Parameter>”) to select it, as we did in the first row of the table for the TimeYear parameter.

11.  Type the following into the selected cell:

TimeMonth

12.  Within the Dimension cell, again to the immediate right of the Parameter cell, select Date, as we did in the first row.

13.  Within the Hierarchy cell, once more to the immediate right of the Dimension cell, select Date.Fiscal, as we did in the row above.

14.  Leave the Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy cell just populated, unchecked, once again.

15.  Using the selector in the Default cell (the rightmost cell in the row within which we have been working), expand the All Periods entry by clicking the “+” sign to its immediate left.

16.  Expand the FY 2005 member.

17.  Expand the H1 FY 2005 member that appears underneath the expanded FY 2005 member.

18.  Expand the Q1 FY 2005 member that appears underneath the expanded H1 FY 2005 member.

19.  Select the July 2004 member, as depicted in Illustration 34.


Illustration 34: Select July 2004 as the Default ...

20.  Click OK to accept our selection, and to dismiss the Default selector.

Next, we will add a query parameter for the span of desired top count (for purposes of our illustration, we will select three items – keeping in mind that the default here, in effect, serves only to provide a value to substitute for the associated query parameter value at run time (as well as for the associated report parameter default). This parameter will have some different settings than the more “typical” settings above – including blanks in a couple of places (a fact that has not, at the time of this writing, been well documented ...).

21.  Once again within the Query Parameters dialog, in the newly appearing third row of the table underneath the caption “Specify details of the parameters defined in the query,” click the leftmost cell (containing the placeholder “<Enter Parameter>”) to select it, as we did in the rows of the table for the two parameters we have already added above.

22.  Type the following into the selected cell:

TopCountSpan

23.  Leave the Dimension cell (again to the immediate right of the Parameter cell) blank.

24.  Leave the Hierarchy cell, (again, to the immediate right of the Dimension cell) blank.

25.  Leave the Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy cell just populated, unchecked, as we have done in the TimeYear and TimeMonth rows above.

26.  Click within the Default cell (the rightmost cell in the row, once again), to place the cursor there.

27.  Type the following into the selected cell:

3

as shown in Illustration 35.


Illustration 35: Input “3” as the Default Span for Look Back ...

28.  Click the Parameter cell on the same row (where we typed “LookBackSpan” earlier), once again, to “set” the “3” within the Default cell.

The third row, within which we have been working in the Query Parameters dialog, appears as depicted in Illustration 36.


Illustration 36: The Query Parameters Dialog with Our Input ...

29.  Click OK to accept our selection, and to dismiss the Query Parameters dialog.

We are returned to the Data tab, where we will make some modifications to the query syntax in the section that follows. Before doing so, let’s quickly confirm some events that have transpired behind the scenes, as a part of our creating the three query parameters in the preceding steps of this section.

30.  Select Report -> Report Parameters ..., from the main menu, as shown in Illustration 37.


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

We note the appearance of the three new query parameters within the Parameters pane of the Report Parameters dialog, which appears next, as depicted in Illustration 38.


Illustration 38: The Report Parameters Dialog with New Entries ...

31.  Click OK to dismiss the Report Parameters dialog for now.

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

We note that only the ProductData selection appears within the selector, as shown in Illustration 39.


Illustration 39: The Single Existing Dataset within the Selector ...

33.  Click the Layout tab in the Report Designer.

34.  Click the Data tab immediately to return to our former position.

35.  Click the downward pointing arrow on the Dataset selector, once again.

We note that the TimeYear and TimeMonth datasets (automatically created when we moved to the Layout tab) join the ProductData selection within the selector, as depicted in Illustration 40.


Illustration 40: The Newly Created Datasets Also Appear within the Selector ...

The datasets we see have been automatically created to support parameters that we have defined with simple datasets that we may or may not choose to use “out of the box.” (See the various articles I have published surrounding parameters in Reporting Services 2005, within my MSSQL Server Reporting Services series at Database Journal.)

NOTE: it is important to remember that, although the report parameter and its underlying Dataset are created automatically when we create the underlying query parameters within the Query Parameters dialog of the MDX query designer (Query mode), removal of a corresponding, supporting row from the Query Parameters dialog will not produce an opposite effect. The Report Parameter and the Dataset will remain until they are manually removed. Moreover, the disablement of parameterization in this manner from within the Query Parameters dialog, followed by re-creation of the affected row will, unless we intervene before we recreate the Parameter, result in the creation of two Report Parameters.

One might wonder why the third query parameter we created, TopCountSpan, did not trigger the creation of a third dataset. Because the simple parameter is based upon a mere number, in this case, and not upon a dimensional structure, no basis exists within the “auto create” capability upon which to build the dataset. We will, of course, be able to manage parameter picklist support via another avenue, as we shall see in a later section in Part II. Our first step in Part II, will be to overview the two datasets that were created, and ensure their adequacy to support the associated date-related report parameters in meeting the business requirements of our client colleagues. We will then conclude by working with underlying dataset queries to enforce cascading, and to support the parameterized “number of top items” selection capability that is the primary focus of this article.

NOTE: Please consider saving the .rdl file we have created to this point for use in the article that follows, so as to avoid the need to repeat the preparation process we have undertaken above.

36.  Experiment further with the report, if desired.

37.  When finished with the report, click the Layout tab.

38.  Select File -> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this point, to a location where it can be easily accessed for the next article within our series.

39.  Select File -> Exit to leave the design environment, when ready.

Conclusion ...

In this article, we began another extended examination of Parameters in Reporting Services 2005. This time, we set out to obtain some hands-on exposure to parameterizing an MDX function, TopCount(), within a preexisting sample OLAP report. Beginning with the general concepts, we continued into a practice session where we set up a scenario within which we would work with a basic OLAP report, to expose the steps involved. In examining the rudiments of specific function parameterization within an OLAP report containing a matrix data region, we performed a brief overview of the MDX TopCount() function, which we stated that we would use to support a stated reporting need of a hypothetical client. We discussed the parameterization of MDX functions in general, and the TopCount() function specifically.

We next prepared a copy of a sample report sourced from Analysis Services, containing a Matrix data region, with which to perform our practice exercise. We then added the required query parameters to support date and function parameterization, which we stated we would complete in Part II of this article. Throughout our various preparation and other steps, we discussed the interaction of the various components in supporting the runtime parameters that the end consumer sees, as well as touching upon the results obtained within the development techniques that we exploited.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.



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