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

Monday Nov 20th 2006 by William Pearson
Share:

BI Architect Bill Pearson begins an extended examination of parameterization within Analysis Services reports. In this article, we get hands-on practice creating a parameter within the graphical user interface, and then examine the construction that Reporting Services performs behind the scenes.

About the Series...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

About the Mastering OLAP Reporting Articles...

One of the first things that become clear to "early adopters" of Reporting Services is that the "knowledgebase" for Analysis Services reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts (where I treated the subject of cascading parameters for Reporting Services 2000), the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services 2005 in recent months, continues to represent a serious "undersell" of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

In this article, we will get hands-on exposure to providing the most basic Parameter support, from the perspective of reports based upon an Analysis Services 2005 cube, for the information consumers we support. Our hands-on practice will specifically focus upon how to create a Parameter (or, more precisely, a "parameterized filter") through the use of the graphical user interface, the MDX Query Builder. As a part of our examination we will take a look at some of the processes that transpire "out of sight" within Reporting Services, primarily to form a foundation for subsequent articles, where we build upon this knowledge to create parameterization capabilities which we can finely customize to the needs of our local environment. While some of the practical concepts we cover in this article are quite basic, a good understanding of the nature and sequencing of object creation, based upon settings we make within the MDX Query Builder, is essential to the more extended procedures we undertake elsewhere. In this article, we will discuss the general concepts, and then set up a scenario within which we work with a basic OLAP report, to expose the steps involved. In examining the rudiments of parameterization within an OLAP report containing a Matrix data region, we will:

  • Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
  • Create a clone of an existing sample report, containing a Matrix data region, with which to perform our practice exercise;
  • Make structural modifications to the clone report, to prepare for our practice exercise session with Parameters within a Matrix data region;
  • Create, within the graphical Design Mode of the MDX Query Builder, a filter for which parameterization is enabled via the Filter pane setting;
  • Preview the report to observe the runtime Parameter in action;
  • Inspect the automatically created Report Parameter and its settings;
  • Examine the automatically created Dataset underlying the new Report Parameter;
  • Discuss the interaction of the various components in supporting the runtime Parameter that the end consumer sees;
  • Discuss the results obtained with the development techniques that we exploit.

Parameters for Analysis Services Reporting

Objective and Business Scenario

Throughout many past articles of the MSSQL Server Reporting Services series, we have leveraged parameters within the context of MDX queries. Reporting Services 2000, initially intended as a component of the MSSQL Server 2005 "BI Release", was released early, as many of us are aware, with resulting limitations in some of its capabilities, as most realistic practitioners would expect. One of the challenges that faced many of us was the absence of an MDX editor: while those of us who were comfortable with MDX syntax were not impaired significantly (although we had to deal with circumstances that accompanied parameterization in Reporting Services 2000, such as the need to string our queries for passage from Reporting Services to the Analysis Server, and the inability to "test generate" our Datasets, once we had Parameters in place within the MDX queries), those who were already challenged with MDX as a language almost certainly found no amusement in dealing with the added mechanics. Reporting Services 2005 introduced the MDX Query Builder, a tool that appeals, due to its "on-off" flexibility, to most practitioners who are comfortable writing direct MDX queries, as well as the MDX- challenged among those authors and developers who need to work with OLAP data sources. This enhancement has, unsurprisingly, changed the way that many of us had become accustomed to writing the underlying MDX queries for our Analysis Services reports.

In this article, we begin an extended examination of Parameters in Reporting Services 2005. Our initial objective will be to explore the basics surrounding Parameters, revealing several events that take place in conjunction with their creation in the design environment. Within our examination of the interaction among the various components that comprise and support run-time parameterization in Reporting Services 2005 in general, and with a specific focus upon the interaction of these components in conjunction with the use of MDX as the query language, we will establish the foundation for more sophisticated parameterization concepts that we introduce in prospective articles. We will begin by adding a simple filter / Parameter combination to a basic OLAP report, containing a Matrix data region, via the graphical interface of the MDX Query Builder. We will then, more importantly, examine the objects that Reporting Services automatically creates, in conjunction with our addition, to support parameterization at runtime. An understanding of the relationship of these objects will serve as the foundation for more elaborate parameterization techniques in sessions that follow.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement basic Parameters. The focus of our efforts will be the addition of parameterization into an OLAP report containing a Matrix data region (the mechanics behind adding the capability, not the design of the report itself). Because of time limitations, we will be working with a simple, pre-existing sample report – in reality, the business environment will typically require more sophistication. The process of setting up basic parameterization is the same in real world scenarios, with perhaps a more complex set of underlying considerations. (I virtually never encounter a client reporting requirement that does not involve at least basic parameterization.)

We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to keep to the focus of the article more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) the MSSQL Server 2005 integrated business intelligence suite. Making preparatory modifications, and then making the enhancements to the report to add the functionality that forms the subject of our lesson, can be done easily within the Business Intelligence Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and will leave us with a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project

For purposes of our practice session, we will open the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 suite. We will complete our practice session within the sample project so as to save the time required to set up a development environment from scratch within the Business Intelligence Development Studio.

To open the AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:

Open the Sample Report Server Project

Ascertain Connectivity of the Shared Data Source

Let’s ensure we have a working data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone. (The default for the Adventure Works DW project sample’s connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default.)

If you do not know how to ascertain or modify connectivity of the Analysis Services data source, please perform the steps of the following procedure in the References section of my articles index:

Ascertain Connectivity of the Analysis Services Data Source

Create a Copy of the Sales Reason Comparisons Report

We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercise. Creating a "clone" of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Reporting Services (particularly an OLAP report using an Analysis Services data source), and other components of the Microsoft integrated business intelligence solution in general.

If you do not know how to create a copy of an existing report, please perform the steps of the following procedure in the References section of my articles index:

Create a Copy of a Sample OLAP Report

We now have a clone OLAP report file within our Reporting Services 2005 Project, with which we can proceed in the next section to make modifications for our subsequent practice session.

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no Parameters, and which is fitted with a simple filter. Let’s open the report in Layout view (for those of us not already there) and make the necessary settings to place it into a state upon which we can commence our practice steps.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.


Illustration 1: Opening the New Report...

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

3.  Click the Data tab.

We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which are labeled in Illustration 2 below.


Illustration 2: The MDX Query Builder

4.  With the Dataset selector, select the ProductList dataset, as depicted in Illustration 3.


Illustration 3: Select the ProductList Dataset...

5.  Once the Dataset loads, click the Delete button, as shown in Illustration 4.


Illustration 4: Deleting the Unneeded Dataset...

6.  Click Yes on the Microsoft Report Designer warning message dialog that appears next, as depicted in Illustration 5.


Illustration 5: Confirm Intention to Delete...

The primary (and sole remaining) dataset, ProductData opens.

7.  Within the Query pane of the MDX Query Builder, right-click the column heading for Internet Total Product Cost.

8.  Click Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 6.


Illustration 6: Deleting the Unwanted Measure from the Dataset

9.  Within the Calculated Members pane, right-click the sole Calculated Member, Profit.

10.  Click Delete on the context menu that appears, as depicted in Illustration 7.


Illustration 7: Deleting the Calculated Member

11.  Right-click the single entry in the Filter pane (directly atop the Query pane).

12.  Click Delete to eliminate the existing Product Category Parameter from the Filter pane, as shown in Illustration 8.


Illustration 8: Deleting the Product Parameter from the Filter Pane

We will make an addition to the Dataset next.

13.  Within the Metadata pane, expand the Date dimension, by clicking the "+" sign to its immediate left.

14.  Expand the Fiscal folder that appears within the Date dimension.

15.  Expand the Fiscal hierarchy within the Fiscal folder.

16.  Drag the Month level into the Data pane, dropping it to the left of the Sales Reason column, as depicted in Illustration 9.


Illustration 9: Adding Months to the Dataset

The new Month column appears, as desired.

We will now add in a simple filter, to make our report a bit more manageable for the practice session.

17.  Click and drag the Fiscal hierarchy into the leftmost box (currently containing the phrase "<Select dimension>"), of the bottom row of the Filter pane, as shown in Illustration 10.


Illustration 10: Adding the Date.Fiscal Hierarchy to the Filter Pane

18.  Click the Filter Expression box within the same row of the Filter pane to enable the selector.

19.  Click the downward arrow of the Filter Expression selector to expose the selection tree.

20.  Expand All Periods by clicking the "+" sign to its immediate left.

21.  Select FY 2004 and FY 2005 by checking the boxes to the left of each, as depicted in Illustration 11.


Illustration 11: Select FY 2004 and FY 2005 as the Filter Expression

22.  Click OK to accept our settings, and to close the selector.

The newly added row in the Filter pane appears, as shown in Illustration 12.


Illustration 12: Our New Entry in the Filter Pane

Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.

23.  Click the Layout tab, as depicted in Illustration 13.


Illustration 13: Click the Layout Tab

24.  On the Layout tab, within the Matrix Data Region, select the value appearing underneath the Internet Total column heading.

25.  Right-click the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value) ).

26.  Select Delete from the context menu that appears, as shown in Illustration 14.


Illustration 14: Delete the Value for Internet Total Product Cost

27.  Right-click the upper left-hand corner of the Matrix Data Region (the gray square).

The gray column and row bars disappear, as a light, patterned outline forms around the Matrix Data Region, and the context menu appears.

28.  Select Properties from the context menu, as depicted in Illustration 15.


Illustration 15: Select Properties from the Context Menu...

The Matrix Properties dialog appears, defaulted to the General tab.

29.  Click the Groups tab.

30.  In the Columns section of the Groups tab (in the lower half of the tab), select the middle group, named matrix1_Sales_Territory_Group.

31.  Click the Edit button, as shown in Illustration 16.


Illustration 16: Editing the Matrix1_Sales_Territory_Group Column Group...

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

32.  Atop the General tab, change the existing Name to the following:

matrix1_Month

33.  Click the row containing the Expression value (currently the expression is "Fields!Sales_Territory_Group.Value"), within the Group on list, to enable the selector.

34.  Select =Fields!Month.Value within the selector, as depicted in Illustration 17.



Illustration 17: Replacing the Existing Group Expression...

35.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

36.  Click OK on the Groups tab, to close the Matrix Properties dialog.

37.  Once again on the Layout tab, within the Matrix Data Region, right-click the value appearing underneath the Internet Orders column heading (the leftmost of the two remaining value cells).

38.  Select Properties on the context menu that appears, as shown in Illustration 18.



Illustration 18: Modifying Properties for the Count Value...

39.  On the Textbox Properties dialog that next appears, click the Format tab.

40.  Replace the existing Format code setting (in the upper left corner of the Format tab) with the following string:

#,###

Here we are simply changing the existing format to one more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as depicted in Illustration 19.


Illustration 19: Replacing the Existing Format Code...

41.  Click OK to accept our modifications, and to dismiss the Textbox Properties dialog.

We next need to change the column heading associated with the Group we modified earlier, so that it reflects Months, versus the previous grouping criteria of Sales Territory Group.

42.  Right-click the text box containing the column label (immediately above the two measure values’ column labels) in the Layout tab (the text box currently contains the expression =Fields!Sales_Territory_Group.Value).

43.  Select Expression... from the context menu that appears, as shown in Illustration 20.


Illustration 20: Modifying the Primary Column Label

The Expression Editor opens.

44.  Replace the expression in the upper portion of the Editor with the following:

=Fields!Month.Value

Recall that the intent is to label the column to reflect the nature of the Group we have put in place: Months instead of Sales Territory Group.

45.  The Expression Editor appears, with our modification, as depicted in Illustration 21.


Illustration 21: The Expression Editor with Our Substitution in Place

46.  Click OK to accept our modification, and to dismiss the Expression Editor.

47.  Click the column heading for which we have just changed the expression, if necessary, simply to select the textbox.

48.  Change the Font Size, in the Format toolbar atop the design environment, from 12pt to 10pt, to make the presentation more compact, as shown in Illustration 22.


Illustration 22: Change the Font to 10pt...

All that remains to complete our preparatory steps is to remove the Report Parameter that remains. Recall that one of our earlier steps was to remove the Product entry from the Filter pane (the sample report from which our report was cloned came equipped with a parameterized Product filter). This entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we shall see. Because parameterization was selected for the Product filter, it follows that a Report Parameter might well have been created at the time the entry was made. We will now remove the remaining Report Parameter.

49.  From the main menu, select Report -> Report Parameters..., as shown in Illustration 23.


Illustration 23: Select Report -> Report Parameters...

The Report Parameters dialog opens, and we see a single Report Parameter listed.

50.  Ensure that the ProductCategory Parameter is selected.

51.  Click the Remove button, as depicted in Illustration 24.


Illustration 24: Removing the Residual Report Parameter...

52.  Click OK to accept our modifications, and to dismiss the (now empty) Report Parameters dialog.

We will execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

53.  Click the Preview tab, as shown in Illustration 25.


Illustration 25: Click the Preview Tab to Execute the Report

The Report is being generated message briefly appears, and then the report displays. The modified report appears as partially depicted in Illustration 26.


Illustration 26: The Modified Report (Partial View)

Our report is now ready for the practice session, which we began in the next section.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved