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

August 18, 2008

Verification: Preview the Report and Inspect the Effectiveness of Our Modifications and Enhancements

Let’s preview the report to inspect the results of our handiwork.

1.  Click the Preview tab.

DBJ_OLAP_Report.rdl initializes, and the Year prompt becomes enabled.

We note that the Year parameter is once again displaying the ParameterCaptionIndented Label field - something we had already changed to the standard ParameterCaption Label field within the Report Parameters dialog for the respective parameter TimeYear earlier. It is, perhaps, useful to note, at this stage, that the Label field has reset itself to this, the Reporting Services default, when we “declared” the TimeYear query parameter within the TimeMonth dataset, as a part of making the latter cascade properly, based upon the selection we made within the TimeYear picklist. To restore the standard ParameterCaption Label field, we can simply return to the Report Parameters dialog for the TimeYear parameter, and reselect ParameterCaption via the Label field selector, as shown in Illustration 23.


Illustration 23: Adjusting the Label Field, which has Reset to Default ... (Optional)

2.  Click the Preview tab, once again, if you have made the change noted above.

3.  Click the downward pointing arrow on the right side of the Month parameter selector.

We note that only two months appear within the Month parameter picklist - the only populated member months of FY 2005 within the Adventure Works sample cube.

NOTE: Ignore the results that appear if the report auto-executes at this point. The default year / month parameter selections are for a period that experienced limited (less than three) Sales Reasons associated with its activity.

Let's try changing the Year at this point to demonstrate further the fact the cascading is working effectively:

4.  Select FY 2004 within the Year parameter picklist.

Once we make a selection within the Year dropdown selector, the next parameter within the cascading chain, Month, becomes enabled, once again.

5.  Click the downward selector button that appears on the right of the Month parameter, as we did before, to expose the parameter picklist, as partially depicted in Illustration 24.


Illustration 24: The Parameter Picklist Cascades to Display the Member Months of FY 2004 (Partial View)

We note (scrolling down, as necessary) that twelve months appear within the Month parameter picklist - the populated member months of FY 2004 within the Adventure Works sample cube. We can easily see that the Month parameter picklist cascades appropriately, based upon the selection we make in the Year parameter picklist.

6.  Select June 2004 within the Month parameter picklist (the bottom entry appearing within the selector).

Let's verify the operation of the “top count” capability we have added via the No. Top Items parameter. As we can see the default of “3” appears in the selector.

7.  Leaving the No. Top Items parameter selection at default (“3”), click the View Report button in the upper right corner of the Preview tab, to execute the report with current settings.

The report executes, and displays its output, including a “top count” of three Sales Reasons (based upon Internet Sales Amount), as shown in Illustration 25.


Illustration 25: The Report Displays a “Top Count” Span of Three Sales Reason Items

Let's perform another quick test of the parameterized “top count” capability.

8.  Click the downward pointing arrow to the immediate right of the No. Months to Report parameter selector.

9.  Select the number “5” within the No. Top Items parameter picklist.

10.  Click the View Report button in the upper right corner of the Preview tab, to execute the report with current settings.

The report executes, displaying its output, this time presenting the Sales Reasons associated with five largest Internet Sales Amounts, as depicted in Illustration 26.


Illustration 26: The Report Displays the Five Largest (Based upon Internet Sales)

Sales Reason Items

We see, therefore, that the “top sales” parameter we have put into place, No. Top Items, accomplishes the intended ends, and enables us to meet the business need expressed by the information consumers. More generally, we can easily see that Reporting Services 2005, with its graphical design environment, supports easy and flexible design of innovative parameters, which can be based upon a sizable inventory of MDX functions and operators, among other options. Further, we have seen how we can design parameters to cascade, and to otherwise interact in an intuitive, user-friendly fashion. While we might have gone significantly further in designing function-based defaults for cascading parameter picklists, or even more elaborate, dynamically adjusting “top count” spans, based upon other criteria we selected, and a host of other possibilities, we assure our client colleagues that, once they understand the basics, we can certainly help them to further enjoy the myriad more advanced capabilities that are exposed within the current version of Reporting Services.

11.  Experiment further with the report, if desired.

12.  Select File -> Save All to save our work to this point.

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

Conclusion ...

In this article, we concluded another extended examination, which we began in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I, of parameterization in Reporting Services 2005. We continued toward our primary objective of getting hands-on exposure to parameterizing TopCount() within the sample OLAP report that we cloned in the first half of the article, where we began our practice session by setting up a scenario within which to work with a basic OLAP report in exposing the steps involved.

We picked up, in this article, with the clone of an existing sample report, containing a matrix data region, which we had created, and structurally modified further for our practice session, in Part I. We also briefly recounted an overview of the MDX TopCount() function we had performed in the first half of this article, discussing details of the use we intended for the function to support the stated reporting needs of a hypothetical client. In so doing, we touched upon general concepts surrounding the parameterization of MDX functions in general, and the TopCount() function specifically.

In this, the second half of our article, we continued our practice session by reviewing and ensuring the adequacy of the datasets (automatically created when we added the required query parameters to support date and function parameterization in Part I) to support report parameters and meet the stated business requirements. We next added syntax to the Month dataset query to enforce cascading, based upon the selection made for the Year parameter by an information consumer at runtime. Finally, we added syntax to enforce the Month parameter selection and leveraged the MDX TopCount() function, containing the set from which we wished to present the “top” items, the count of items desired) and the numeric expression (the measure Internet Sales Amount, in our example) upon which we wished to base our “top” ranking, to provide the “top count” capability requested by our client. At appropriate junctures throughout both Part I and Part II of this article, we discussed the interaction between the various components in supporting the runtime parameter that the end consumer sees, as well as discussing the results obtained with 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