Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 20, 2006

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM