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:

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.

Let's add a filter (which we will parameterize) 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.

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

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

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.

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.

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

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.

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.

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

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.

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.