NewsArticles Minimize
11
In Part 1 of this three-part article, we kicked off an examination of parameterization in Reporting Services in general, with a concentration on reports written to extract data from an Analysis Services data source. We opened and prepared a sample Report Server project in the Business Intelligence Development Studio, and positioned ourselves to add an OLAP report sample, from which to begin our introduction to parameterization within an Analysis Services report. In Part 2, we continued our preparation by adding a sample OLAP report to the Report Server project, and further modified the report the steps of our practice session, which follows.
 
Procedure: Adding Basic Parameters in Reporting Services 2008 R2
 
As we noted in earlier sections, 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 Designer 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 Designer 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 Design tab.

We enter the Design tab, as we did earlier. We must be here to access the datasets in the Report Data pane – the dataset labels are “grayed out” if we are on the Preview tab.

2.    Right-click the ProductDatadataset.
3.    Select Query… from the context menu that appears, as we did earlier in our preparation section.

The Query Designer appears, once again. 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 47
 
 
Illustration 47: Design View of the Query Designer
 
Let's add a filter (which we will parameterize) for Sales Territory Country

4.    In the Metadata pane, expand the Sales Territory dimension.
5.    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 48.
 
 
Illustration 48: 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 Country 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.

6.    Click the Filter Expression box to enable its selector.
7.    Click the downward arrow that appears on the right side of the Filter Expression box.
8.    Expand All Sales Territories within the dropdown selector.

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

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

 
 
Illustration 49: Select United States as Default …

10.  Click OK to accept our selection, and to close the selector
11.  Place a checkmark in the Parameters box to the immediate right of the Filter Expression, now populated by {United States}.
12.  Right-click a point within the shading of the new row.
13.  Select Move Up from the context menu that appears, as depicted in Illustration 50.

 
 
Illustration 50: 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 51.
 
 
 
Illustration 51: 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 Design 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 Query Designer (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.

14.  Click OK to save our modifications, dismiss the Query Designer, and return to the Design tab.
15.  Expand the Parameters folder within the Report Data pane, if necessary.

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).

16.  Right-click the new SalesTerritorySalesTerritoryCountryparameter.
17.  Select Parameter Properties from the context menu that appears, as depicted in Illustration 52.

 
 
Illustration 52: Select Parameter Properties …

The Report Parameter Properties dialog opens, defaulted to the General page.

18.  Click Available Values in the left-hand pane of the dialog.

Another important observation we might make is that the Get values from a query setting atop the Available values page 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 has also been automatically created. The purpose of this Dataset is to support the selection checklist, as well as the “qualified” value passed based upon the selection made from the picklist at runtime, for the new Report Parameter

These settings appear upon the Available Values page of  the Report Parameter Properties dialog as shown in Illustration 53.
 
 
Illustration 53: Available Values Settings for the Automatically Created Report Parameter

19.  Click Default Values in the left-hand pane of the dialog.

Here 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 Properties dialog when we set a default, the “label” name - a “consumer-friendly” name, generally - is what actually appears within the parameter selector at runtime.)

The default value settings appear upon the Default Values page of  the Report Parameters Properties dialog as depicted in Illustration 54.
 
 
Illustration 54: Default Values Settings for the Automatically Created Report Parameter

20.  Click OK to dismiss theReport Parameters Properties dialog.
21.  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 55.



Illustration 55: The New Runtime Parameter Appears …

22.  Click the downward arrow to the right of the Sales Territory Country parameter to expose the picklist of Country choices.
23.  Select United Kingdom, in addition to the already selected United States.
24.  Click the View Report button in the upper right corner of the Preview tab.

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

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

25.  Click the Design tab once again.
26.  Expand the Datasets folder within the Report Data pane, if necessary, to see the datasets it contains.

We initially note only the ProductData dataset we had seen earlier.

27.  Right-click the Datasets folder within the Report Data pane.
28.  Select Show Hidden Datasets from the context menu that appears, as depicted in Illustration 56.

 


Illustration 56Show Hidden Datasets …

A new dataset, SalesTerritorySalesTerritoryCountry, appears, exploded to display its underlying fields, underneath the pre-existing ProductData dataset, as shown in Illustration 57.



Illustration 57: The New Parameter Support Dataset Appears

29.  Right-click the SalesTerritorySalesTerritoryCountry dataset.
30.  Select Query… from the context menu that appears, as we did earlier.

The Query Designer loads, and presents the MDX syntax in the Query pane (We are not in Design Mode: we can see the actual query, and can note that the Design Mode button is not depressed, as depicted in Illustration 58.
 
 
Illustration 58: View of the MDX Query Underlying the Parameter Support Dataset
 
This is the query that has been automatically created to generate the picklist to support the new SalesTerritorySalesTerritoryCountry Dataset Report Parameter.

31.  Click the Execute Query button in the toolbar, as shown in Illustration 59.

 
Illustration 59: Execute the Query

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

 
 
Illustration 60: The Dataset Automatically Created for Report 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 Properties dialog (Available Values page) 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 Mastering Microsoft BI series, please refer to the articles of my Elemental MDX and Molecular MDX series’, whose member articles are published monthly here at SSWUG, for detailed information about MDX.

32.  Click OK to dismiss the Query Designer.
33.  Select File --- Save Sales Reason Comparisons As …, and save the report in a convenient location.

In summary, it is important, as we go forward in our extended examinations of Parameters, to realize how Reporting Services automaticallycreates 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.

34.  Experiment further with the report, as desired.
35.  Select File --- Save All to save our work to this point.
36.  Select File --- Exit to leave the design environment, when ready.

Conclusion …

In this article we began an extended examination of Parameters in Reporting Services 2008 R2. 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 Query Designer, 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 Design tab, 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 Designer. Finally, we noted that a Dataset had also been created automatically (its creation having been triggered upon our enablement of  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 Query Designer in Design Mode.
 

Post Comment

Only registered users may post comments.

LatestArticles Minimize
Computed column 'mydate' in table 'test2' Cannot be Persisted because the Column is Non-deterministic. by Wayne Snyder
How to create a calculated column which converts a string to a date and persist it. The problem is that the calculated column must be deterministic.

SSAS Process Full from SSIS with Parameter for DatabaseName by Wayne Snyder
We have recently had a need to process tabular models programmatically, but we needed it to be easily done by an end user – self service is the key – right? To solve this problem we have a...

Using Linked Tables in PowerPivot by Wayne Snyder
PowerPivot allows you to create a table in an Excel worksheet, and import it into your PowerPivot model. You can import data from Excel spreadsheets, but this differs in that the Excel data we wish to...

Errors Creating and Maintaining Relationships with PowerPivot Tables by Wayne Snyder
In this article you will learn how to create relationships in PowerPivot. More Importantly, you will learn about many-to-many relationships. Since PowerPivot only supports 1-to-many relationships, you...

Importing Flat Files into PowerPivot– Overcoming Problems by Wayne Snyder
You may run into issues when trying to import flat files into PowerPivot. This article documents some of the issues, and includes workarounds when available. As I bump into new problems, I will try to...

Importing Flat Files Into PowerPivot by Wayne Snyder
Bringing flat files into your Powerpivot model is easy. We will go through the process step by step, so that you can begin mashing up data to get your job done more quickly, more effectively, and with...

Changing Granularity of Leaf Level Calculations in SSAS Tabular by Jason Thomas

Finding Nearest Stores using SSRS Map Reports by Jason Thomas

Heat Maps for SSRS using Map Control by Jason Thomas

Parameters for Analysis Services Reporting: Introduction, Pt. 3 by William Pearson
BI Architect Bill Pearson continues an extended examination of parameterization within Analysis Services reports. In this, Part 3 of the article, we continue to get hands-on practice cr...


Advertisement Minimize

Advertisement Minimize

Copyright 2004-2013 MSBICentral.com Terms Of Use Privacy Statement