Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I - Page 5

November 20, 2006

Procedure: Adding Basic Parameters in Reporting Services 2005

As we noted earlier, our intent within this article is to begin our examination of Parameters by taking the simplest approach: we will add a Parameter using the Query Builder interface in Design Mode. Moreover, we will examine the internal processes that take place "behind the scenes." In prospective articles, we will add Parameters via more manual, less "visually direct" channels (some of the steps of which will take place within the Query view versus the Design view). We will compare and contrast methods throughout the related articles, introducing, within each, new concepts upon which we can build more sophisticated parameterization support into our organizations’ reports.

Add a Parameter through the Query Builder Interface

As many of us know already, the Design Mode button in the toolbar of the Data tab allows us to easily shift between Design view and Query view, when working with our MDX queries. The idea, obviously, is to provide those who are not at ease with direct MDX a means of creating queries within a graphical interface.

We will create our first Parameter within Design view, by taking the following steps:

1.  Click the Data tab.

We enter the Data tab, as we did earlier. We can tell that we are in Design view, primarily because we see the column headings for our query components in the data area, and we do not see MDX syntax. We also see that the Design Mode button is depressed, as shown in Illustration 27.


Illustration 27: MDX Query Builder in Design Mode (Compressed View)

Let's add a filter (which we will parameterize) for Sales Territory Country.

2.  In the Metadata pane, expand the Sales Territory dimension.

3.  Drag the Sales Territory Country attribute hierarchy into the box marked <Select dimension>, within the second row of the Filter pane (the row underneath the Date filter that we inserted earlier), as depicted in Illustration 28.


Illustration 28: Adding a Filter for Sales Territory Country...

We might have begun the creation of this Parameter by selecting Sales Territory using the selector in the Dimension column, and then specifying Sales Territory Region in the Hierarchy column. Dragging an attribute hierarchy into the filter row, as we have done, results in the simultaneous population of the Dimension and the Hierarchy settings.

4.  Click the Filter Expression box to enable its selector.

5.  Click of the downward arrow that appears on the right side of the Filter Expression box.

6.  Expand All Sales Territories within the dropdown selector.

We see that the individual Countries appear. We will set the default here to United States.

7.  Click-check United States within the list of Countries, as shown in Illustration 29.


Illustration 29: Select United States as Default...

8.  Click OK to accept our selection, and to close the selector.

9.  Place a checkmark in the Parameters box to the immediate right of the Filter Expression, now populated by {United States}.

10.  Right-click a point within the shading of the new row.

11.  Select Move Up from the context menu that appears, as depicted in Illustration 30.


Illustration 30: Move the Parameter Row to the Top...

Our completed Filter-with-Parameter entry appears in the top row of the Filter pane, as shown in Illustration 31.


Illustration 31: The Completed Row in the Filter Pane

We have successfully added a new Parameter, based upon a filter, within the Dataset underlying our report. The addition of the Parameter within the graphical interface, as we shall see, has already triggered the automatic creation of a Report Parameter. A supporting Dataset will also be created, as soon as we move to the Layout tab.

NOTE: it is important to remember that, although the Report Parameter and its underlying Dataset are created automatically when we enable parameterization within the Filter pane of the MDX Query Builder (Design Mode), the removal of the "Parameter" checkmark within the associated row of the Filter pane, or even the deletion of the entire corresponding filter row, will not produce an opposite effect. The Report Parameter and the Dataset will remain until they are manually removed. Moreover, the disablement of parameterization within the Filter pane, followed by re-enablement and / or recreation of the Filter pane entry will, unless we intervene before we re-enable / recreate the Parameter, result in the creation of two Report Parameters.

12.  Click the Layout tab.

13.  Select Report -> Report Parameters from the main menu.

We recall that we previously removed all Report Parameters, as part of our preparation for the practice exercise. Yet we note, within the Report Parameters dialog that has appeared, the presence of a new Report Parameter, called SalesTerritorySalesTerritoryCountry. This Report Parameter was created automatically when we designated our new row within the Filter pane as a Parameter (via the enabling checkbox).

Another important observation we might make is that the From query setting within the Available values section of the dialog is active (the radio button is selected), and that the setting references a Dataset (and the associated Value and Label fields) with the same name as the new Report Parameter - a Dataset, we will discover, that also been automatically created. The purpose of this Dataset is to support the selection checklist for the new Report Parameter.

Finally, we note that the MDX qualified name for Sales Territory Country United States, ( [Sales Territory].[Sales Territory Country].&[United States] ), is referenced as the Default Value for the Report Parameter. (Even though we use the MDX qualified name within the Default Value here, as at any other time in the Report Parameter dialog when we set a default, the "label" name - a "consumer-friendly" name, generally - is what actually appears within the parameter selector at runtime.)

These settings appear within the Report Parameters dialog, as depicted in Illustration 32.


Illustration 32: Settings for the Automatically Created Report Parameter

14.  Click OK.

15.  Click the Preview tab next, to execute the report.

The report runs, and then displays a drop-down parameter called Sales Territory Country, defaulted to United States, as shown in Illustration 33.


Illustration 33: The New Runtime Parameter Appears...

16.  Click the downward arrow to the right of the Sales Territory Country parameter to expose the picklist of Country choices.

17.  Select United Kingdom, in addition to the already selected United States.

18.  Click the View Report button in the upper right corner of the Preview tab.

The report runs again, and returns data with higher values than when we ran it for the United States alone.

We will now return to the Data tab, to examine the Dataset which has been automatically created (to populate the picklist) to support the new Report Parameter.

19.  Click the Data tab once again.

20.  Click the downward selector arrow on the right side of the Dataset selector.

21.  Select the new SalesTerritorySalesTerritoryCountry Dataset that appears, as depicted in Illustration 34.


Illustration 34: Select the New Dataset

The Dataset loads, and presents the MDX syntax in Query view. This is the query that has been automatically created to generate the picklist to support the new SalesTerritorySalesTerritoryCountry Dataset Report Parameter.

22.  Click the Execute Query button in the toolbar, as shown in Illustration 35.


Illustration 35: Execute the Query

The query executes, and a data grid becomes populated, as depicted in Illustration 36.


Illustration 36: The Dataset Created for Parameter Picklist Support

An examination of the query and the data it returns reveals that, in addition to retrieving data from the cube, the automatically generated query creates additional fields whose sole purpose is support of the parameter picklist, from which information consumers make the selections at runtime. These fields are:

  • ParameterCaption
  • ParameterValue
  • ParameterLevel

All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination). As we discover within other articles of the series, there are multiple ways to approach picklist support (I very often construct members such as the above at the Analysis Services level, to afford central maintenance and reusability, among other, perhaps less obvious, advantages). This is simply the mechanism through which Reporting Services accomplishes support of the automatically generated Report Parameter. As we saw earlier, the Value and Label references within the Report Parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.

NOTE: While we will discuss MDX functions, expressions, queries, and related subject matter throughout the MSSQL Reporting Services series, please refer to the articles of my MDX Essentials series, whose member articles are published monthly here at Database Journal, for detailed information about MDX.

In summary, it is important, as we go forward in our extended examination of Parameters, to realize how Reporting Services automatically creates both the Report Parameter and the Dataset that supports the Parameter at runtime. As we venture into custom parameterization within various practice sessions elsewhere, we will need to keep in mind how the parts knit together within this basic context, so as to understand the relationships that we need to establish manually.

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

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

Conclusion...

In this article, we began an extended examination of Parameters in Reporting Services 2005. In this, the introductory article for a subseries that we will devote to the subject, we explored the basics surrounding Parameters, revealing several actions that take place in conjunction with their creation in the design environment. After preparing a copy of a sample report for our practice session, we added a standard filter to the existing Dataset. Keeping this and subsequent efforts within the MDX Query Builder, the graphical interface within Reporting Services for creating Datasets from an Analysis Services data source, we next created another filter-this time a filter with a Parameter.

We previewed the report, noting the appearance of the runtime parameter during execution. We then examined, from within the Layout view, the Report Parameter that had been automatically created in the background by Reporting Services when we enabled parameterization within the Filter pane of the MDX Query Builder. Finally, we noted that a Dataset had also been created automatically (its creation having been triggered upon our initial entry to the Layout tab, once we had enabled the Parameter within the associated row of the Filter pane); this Dataset, we recalled, had been referenced within the new Report Parameter, where both the Value and Label fields used within the runtime Parameter picklist are specified. Our examination of these basic underlying processes served to arm us with a working knowledge of how Report Parameters interact with their underlying Datasets in general, and, specifically, how these components are created when we design parameters into a report from within the MDX Query Builder in Design Mode.

» 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