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

July 29, 2008

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers