Support Parameterization from Analysis Services

Tuesday Feb 26th 2008 by William Pearson
Share:

BI Architect Bill Pearson demonstrates a straightforward solution – from the Analysis Services layer - for supporting parameterization within Reporting Services.

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.

Introduction

As we learned in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, a common enterprise reporting requirement is the capability to filter reports at run time for specific information. This is typically managed via parameterization, also known as “prompting”, where the filter criteria is requested (and hence the consumer is “prompted”) when the report is run. Depending upon the parameter type (the most common are type-in and picklist), the filters are typically enacted when the consumer types or selects a value, or a series of values.

In Customize Automatically Created Parameter Support Objects Pt. I, we reviewed type-in and picklist parameters in general, and then concentrated our focus, once again, upon picklist parameters, which we noted to be a frequent choice among information consumers for user-friendly operation within reports. Picklist parameters add value in several ways, including the inherent elimination of typing errors, as well as the enforcement of standard selections for report execution. We noted that the design of the picklist is important in making runtime selections easy, and that picklists presenting long scrolling processes or other cumbersome characteristics can negatively affect consumer perceptions, particularly when they are components within frequently accessed reports. We then focused upon the effective use of parameter defaults in making reports that contain parameters even more user-friendly and efficient at runtime.

In Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, we discussed the fact that Parameter defaults are important to consider for multiple reasons. Primary among these reasons, we learned, are the elimination of confusion (especially among less experienced users, who may be startled by simple blank boxes for input, and the like), and the provision for more likely, “dynamically intelligent” inputs (such as “most recent period containing data within the cube”) – that is, the more likely desired runtime selections, considering the nature of the report and its typical usage by information consumers. We then continued working with the basic OLAP report we created in Pt. I, establishing a scenario within which we exposed the steps involved in meeting a basic need of a hypothetical client for the automatic display of default selections within the parameter picklists of the report at runtime.

In this article, we will work with an identical copy of the basic OLAP report we created in Customize Automatically Created Parameter Support Objects Pt. I. We will continue working within the scenario we established within that article, again with an objective of meeting the need of the hypothetical client to support parameterization, including picklists, within the report. This time, however, we will expose the steps involved in offering a simple means of supporting our solution from within the Analysis Services layer of the integrated Microsoft BI solution. In examining the support of parameterization from Analysis Services, we will:

  • Review the concept of parameterization in general, and briefly overview options for supporting parameterization from among the three primary layers of the integrated Microsoft business intelligence solution;
  • 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 OLAP 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 session;
  • Discuss and implement an approach, from within Analysis Services, to meet the need of our hypothetical client to support Report Parameters from the Analysis Services layer of the integrated Microsoft BI solution;
  • Access and employ the new Analysis Services parameter support components from within Reporting Services;
  • Overview how the various components of the parameter support solution we propose are tied together, as a part of a hands-on practice session where we create and align the necessary components within Analysis Services and Reporting Services to support report parameterization;
  • Preview the report to observe the effectiveness of our solution in runtime action.

Support Parameterization from Analysis Services

Objective and Business Scenario

As I have stated within numerous articles of this series, I have implemented parameters in many ways to fit client business needs, ranging from the most basic picklist prompts to sophisticated prompts that “push the envelope,” with regard to going beyond out-of-the-box uses for these components. In Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, we noted that one of the most impressive enhancements introduced with Reporting Services 2005 is its capability to automatically create the complete “chain” of components, including filter, report parameter, and supporting dataset(s), that we need to quickly add basic parameterized filters to our reports. We discussed the fact that the automatically created objects serve us well with minimal modification in many cases; we emphasized, however, the frequent need to “tweak” the components supporting parameterization a bit beyond mere cosmetics to obtain more sophisticated features. In two subsequent articles of our series, Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, we outlined some of the customization needs that might arise, and got some hands-on exposure to the steps required to make the modifications involved.

As we mentioned within these two articles, as well as within many others throughout my various Database Journal series’, we might offer solutions to meeting reporting requirements that are completely contained within the reporting layer of the integrated Microsoft business intelligence solution, but that other options exist at the Analysis Services and MSSQL Server RDBMS levels. An important consideration during the design phase of any implementation effort, as we have noted repeatedly, is that, while we can still manage much customization within the reporting / presentation layer of our integrated business intelligence solution, we also have multiple options for placing the intelligence behind parameterization (as well as other functionality) in other layers of the solution. For example, I have often supported parameter picklists with support objects I have created within the Analysis Services cube that serves as a data source for the report(s) under consideration. As I have emphasized in many other of my articles, one of the numerous advantages of “placing the intelligence” into layers outside Reporting Services lies within the fact that we can thus avoid adding the intelligence to support our needs within every individual report where it is useful; it also means a central point of maintenance, the enforcement of consistent application of the logic across multiple reports, and other benefits.

We will undertake the steps to provide an example of similar support within our practice session that follows. (For another example of implementing support for a hierarchical picklist in this manner, see my Database Journal article Create a Cube-Based Hierarchical Picklist.)

NOTE: When we consider the fact that we have many options for “where to install the intelligence” within the multi-layered BI solution, we can begin to see that a grasp of all layers is critical. For a general summary of my opinions surrounding the importance of thinking "multi-dimensionally" within the design and implementation of a business intelligence system, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

As most of us realize, the reporting / presentation layer of the integrated Microsoft business intelligence solution is often the only point of interaction with corporate information stores for organizational information consumers. Parameterization not only makes this interface more user-friendly, but it affords the tandem benefit of enabling knowledgeable report authors to leverage single reports for multiple uses, delivering “more for less” from a development and maintenance perspective.

Many of the reports we author for our employers or clients depend upon time / date parameters that are based upon the “current” date in some regard. As an illustration, a sales report might typically be executed at multiple period “cutoffs” (weekly, monthly, quarterly, annually, and so forth) to present data from the previous period. Because Reporting Services supports dynamic default parameters, as we shall see, the tasks involved in providing information consumers with user-friendly, intuitive parameters, by which timely, relevant results can be retrieved become far easier for knowledgeable authors.

In this article we will continue our extended examination of Parameters in Reporting Services, concentrating upon another specific refinement we can make to our parameters to “fine tune” them to local business environments. We will first create a basic sample OLAP report containing a matrix data region, to which we will focus upon adding parameterization to meet additional business requirements of a hypothetical client, the AdventureWorks organization. The requirements will be largely identical to those which we met completely within the Reporting Layer in Reporting Services: Customize Automatically Created Parameter Support Objects.

To detail the specifics, a client team with which we have consulted for some time, the Adventure Works Customer Care department, has made a request for our support in accomplishing its reporting objectives. Our client colleagues inform us that information consumers have asked that they refine an existing report to add user-friendly parameterization. They tell us that the vast majority of information consumers accessing this report seek to return results based upon date parameters (including Year, Quarter, and Month). This would, of course, allow consumers with different requirements the capability of specifying their own specific needs at given runtimes.

While this is a relatively common desire on the part of information consumers, there are, as I have implied, multiple ways to approach the support of parameterization within the integrated Microsoft business intelligence solution, examples of which include the procedures we took within Reporting Services: Customize Automatically Created Parameter Support Objects, where we demonstrated an option that is completely contained within Reporting Services. In this article we will expose a means for supporting parameterization from within the Analysis Services layer of the integrated BI solution.

We confirm our understanding of the foregoing needs, and propose to construct working examples of a way to provide the runtime picklists that our colleagues have requested for the new Report Parameters, supported from within components that we add into the Analysis Services layer – examples, we add, that can be modified to specify different defaults (we address this is our next article), should the consumers request them after “road testing” the initial picklists, and so forth.

Preparation

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 Development 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 exercises. Creating a “clone” of the report 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. We can, therefore, use the original 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: Add the Analysis Services Database to the Project

We will continue our preparation by adding the Analysis Services database – with which we ascertained connectivity in the “Ascertain Connectivity of the Shared Data Source” section above - within our newly created project in the Business Intelligence Development Studio. I typically like to set up a lab environment for each of my client or research projects where I have both the respective UDM and reports involved with the engagement within an integrated solution in Visual Studio. This ensures ease in testing cube modifications through to the report layer from a single, central location, as well as providing the advantage of effective source control, among numerous other conveniences. For example, in this particular case, I will have both a copy of the sample Adventure Works DW and the AdventureWorks Sample Reports projects added into a single solution within the Business Intelligence Development Studio, where I can access all member objects from one point, the Solution Explorer.

Continuing within our newly created project in the Business Intelligence Development Studio, take the following steps:

1.  Select File -> Open from the main menu.

2.  Click Analysis Services Database ... from the cascading menu, as shown in Illustration 1.


Illustration 1: Selecting an Analysis Services Database into the Project ...

The Connect to Database dialog appears.

3.  Ensure that the radio button to the immediate left of Connect to existing database (atop the dialog) is selected.

4.  Type the appropriate name within the Server input box.

5.  Select the appropriate name within the Database selector (the Analysis Services database with which we ascertained connectivity of our report clone above), just underneath the Server input box.

6.  Click the radio button to the immediate left of Add To Solution (in the bottom section of the dialog), to select this option.

The Connect to Database dialog appears similar to that depicted in Illustration 2.


Illustration 2: The Connect to Database Dialog, with Our Input

7.  Click OK to accept our input, and to dismiss the dialog.

The Reading database from the server... message box appears briefly, as shown in Illustration 3.


Illustration 3: Reading the Database from the Server ...

The Adventure Works DW Analysis Services project opens, and we see the various associated objects appear within Solution Explorer, as depicted in Illustration 4 (with Dimensions folder collapsed).


Illustration 4: The Adventure Works DW Analysis Services Project Joins the Solution ...

We can now access our sample report and its underlying Analysis Services database, and thus test cube enhancements through to the report layer, from a single, central development environment.

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 basic parameterized filters as required by our client colleagues (just as we did in preparing the report to support its own parameters within the articles we cited earlier). This time, however, we will only be creating the parameters within Reporting Services to serve as skeletal placeholders from which we will reference support objects that we will add to our Analysis Services data source in the respective procedural section below. Some of the support structure that is automatically created within Reporting Services as part of this process will be discarded / ignored. While there are other ways of approaching the creation of the parameters in Reporting Services, we are simply taking a short cut to finalize preparation and get to our focus subject matter, the creation and alignment of the parameter support objects in Analysis Services, more rapidly.

Let’s open the report (for those of us not already there) and make the necessary settings to place it into a state from which we can commence our “from scratch” 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 5, as necessary.


Illustration 5: Opening the New Report ...

We will begin by adding additional parameters to our sample report. These parameters will initially be of a standard, basic picklist variety. We will then work with these parameters to align them with support objects we later construct within the report’s underlying Analysis Services data source.

Add Additional Parameters through the Query Builder Interface and Review the Automatic Creation of Support Objects

As we stated in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and elsewhere within this, and other of my, series’, 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 OLAP 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 have begun with a basic report with core datasets, and are ready to add three rudimentary, date-related prompts that will meet our objectives. We will create these prompts within Design view, by taking the following steps:

1.  Click the Data tab.

We enter the Data view, which defaults to the presentation of the first of two pre-existing datasets, ProductData. To state what is probably obvious, we can tell that we are in Design view, primarily because we see the column headings for our query components in the Data grid, and we do not see MDX syntax. We also see that the Design Mode button is depressed, as depicted in Illustration 6.


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

We have accessed the Data tab to add three date-related filters (each of which we shall parameterize): one each for Calendar Year, Calendar Quarter and Calendar Month. Let’s first run the existing query to ascertain that all is in order with regard to the retrieval of data.

2.  Click the Execute Query button atop the Data tab, as shown in Illustration 7.


Illustration 7: Click Execute Query to Retrieve Data with the Existing Query ...

Data populates the Data pane, and appears similar to that depicted in Illustration 8.


Illustration 8: Initial Query Operates Successfully in a Quick Test

Having ascertained that the core query is in working order, we will now add the date-related filters, as well as the “vanilla” parameters that we intend for them to support.

3.  In the Metadata pane, expand the Date dimension, as required.

4.  Within the expanded Date dimension, expand the Calendar folder.

5.  Right-click the Date.Calendar Year attribute hierarchy that appears within the expanded Calendar folder.

6.  Select Add to Filter within the context menu that appears, as shown in Illustration 9.


Illustration 9: Select Add to Filter in the Context Menu

The Date dimension populates the Dimension column, within a newly created row of the Filter pane. The Calendar Year attribute hierarchy appears within the Hierarchy column, as well.

7.  Leave the Operator setting (to the immediate right of the Hierarchy column) at its default of “Equal.”

8.  Place a checkmark in the checkbox that appears in the Parameters setting (to the immediate right of the Filter Expression setting), the rightmost column in the new row of the Filter pane.

Our completed filter-with-parameter entry appears in the new row of the Filter pane, as depicted in Illustration 10.


Illustration 10: The Filter Pane Reflects Our Addition of the Calendar Year Attribute Hierarchy

We have successfully added a new parameter, in conjunction with a filter, within the primary dataset underlying our report. The addition of the parameter within the graphical interface, as we have seen in previous articles, has already begun a sequence of events that will trigger the automatic creation of a corresponding Report Parameter. A supporting dataset will also be created, as soon as we move to the Layout tab. But first, we need to add entries within the Filter pane for the additional Date filters we intend to add.

9.  Click the leftmost box (which contains the placeholder <Select dimension>, as before), within the Dimension column, in the bottom row of the Filter pane (this time, beneath the box containing Date, which we added in creating the first entry in the Filter pane earlier).

The dropdown selector is again enabled.

10.  Select Date from the list that appears in the selector.

11.  Select Date.Calendar Quarter of Year within the Hierarchy box to the immediate right, using the selector that is built in.

12.  Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal” (checking the box will make it appear), as we did within the first entry.

13.  Place a checkmark in the checkbox that appears in the Parameters setting, once again.

We will now create the final parameterized filter within the Filter pane.

14.  In the bottom row of the Filter pane, click the leftmost box within the Dimension column, once again.

15.  Select Date from the list that appears in the selector, as we did before.

16.  Select Date.Month of Year within the Hierarchy box to the immediate right, using the selector that is built in.

17.  Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal,” as we did within the first entry.

18.  Place a checkmark in the checkbox that appears in the Parameters column, as we have done with the previous row entries we have made in the Filter pane.

The Filter pane, with all four entries, appears as shown in Illustration 11.


Illustration 11: The Four Parameterized Filters in the Filter Pane

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

The Report Parameters dialog opens, this time revealing four Report Parameters, as depicted in Illustration 12.


Illustration 12: The Four Report Parameters, including the Three New Date Parameters

We will make adjustments to each of the new Report Parameter entries in later steps, where it will be most efficient to accomplish all at one time.

20.  Click OK to dismiss the Report Parameters dialog.

21.  Click the Layout tab to transit temporarily away from the Data tab.

22.  Click the Data tab, once more, to return immediately.

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

Based upon our discussions in earlier articles, we see the behavior we have expected. All five Datasets, including the originally existing two Datasets and the three we have added via the enactment of parameterized filters within the Filter pane (and subsequent departure from the Data tab), now appear, as shown in Illustration 13.


Illustration 13: All Datasets Appear as Expected ...

We have successfully added three rudimentary parameters, based upon filters we added within the Filter pane of the primary Dataset underlying our report. The addition of the parameters within the graphical interface, as we have verified, triggered the automatic creation of corresponding Report Parameters. Supporting datasets were also created, as soon as we moved away from the Data tab. The timing of the automatic creation of objects can thus be understood; the sequencing becomes very important in the event that we need to modify or remove any of the objects after they are initially created and aligned.

Because we will be referencing parameter support objects outside Reporting Services, which we will create later in Analysis Services, we will not need the automatically created parameter support datasets. We will remove these later – we can thus reference their structures, in the meantime, which are already aligned to the existing Report Parameters, as a part of quick construction of the Analysis Services replacements.

24.  Leave the report open at the Data tab for later steps.

We are now ready to transit to the Analysis Services Cube Designer to add parameter support to the Adventure Works cube.

Practice

Practice: Add Parameter Support Objects to the Analysis Services Layer

We will next add parameter support within the Analysis Services database upon which our sample report is based. To do so, we will access the Cube Designer within the Business Intelligence Development Studio, taking the following steps:

1.  Right-click the Adventure Works cube within the Solution Explorer.

2.  Select Open from the context menu that appears, as depicted in Illustration 14.


Illustration 14: Opening the Cube Designer ...

Add Calculated Members in Analysis Services to Support Parameters within Reporting Services

As we learned in Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, as well as in other recent articles within our series, when we create parameterized filters within our dataset(s), Reporting Services automatically creates support objects for self-contained parameterization at the reporting layer. These support objects include Report Parameters, such as those we have examined in our preparation steps above, as well as datasets to support each of those Report Parameters. The datasets use the WITH MEMBER keywords to create calculated members at runtime, when the datasets are instantiated in Reporting Services, which are then referenced, by default, in the Value and Label fields within the associated Report Parameter settings page.

Because we took the shortcut approach of having Reporting Services generate the support objects automatically in our creation of the date-related parameters earlier, we not only have the Report Parameters in place, ready to be pointed to the calculated members we will create in Analysis Services, but we also have a useful set of calculated member definitions already in place upon which we can easily model our Analysis Services calculated members. Let’s take a look at the DateCalendarYear dataset for starters, as we create the calculated members we will need in Analysis Services. In our practice session, we will only create those we actually intend to use in the associated parameters of our report, ignoring the additional calculated members that are provided in the automatically created datasets. We could, of course, include all, or create custom calculated members that are entirely different, should the need arise in our local business environments.

We review the requirements with the client representatives, reiterating that, while parameterization can certainly be managed using the automatically created objects provided within Reporting Services, several benefits accrue to us in creating the support objects within the Analysis Services layer. Positing the intelligence within the cube will, we assert, mean that the same logic can be carried forward to multiple reports by simply referencing the calculations in their respective data sources, versus performing the calculations for every affected measure in the report (and adding to processing time, etc.). Moreover, we emphasize that maintaining the logic in a single location in the Analysis Services layer means a single point of maintenance: we can modify the logic in one place and rely upon the changes to “ripple through” to all reports that reference that logic, versus having to mechanically modify each individual report. This also ensures consistency of application of the logic to all affected reports: None will be overlooked for prospective modifications in the logic, for instance, when modifications become necessary, nor will different logic be accidentally applied to different reports.

From within the now open Cube Designer, we will begin creating calculated members to support parameterization in our practice report.

1.  Select the Calculations tab within the Cube Designer.

The Calculations tab opens.

2.  Click the bottom entry in the Script Organizer pane, to anchor the cursor, as shown (the name of the bottom item will likely differ in your own environment) in Illustration 15.


Illustration 15: Click the Bottom Entry in the Script Organizer to Position the New Calculation

3.  Click the New Calculated Member button atop the Calculations tab, as depicted in Illustration 16.


Illustration 16: Click the New Calculated Member Button

Clicking the New Calculated Member button adds a new calculation to the Script Organizer pane (by default named “[Calculated Member]”) and displays fields for its definition in the calculations form in the Calculations Expressions pane.

4.  Leaving the Cube Designer open at the Calculations tab, return to the Data tab of the report with which we are working in Reporting Services.

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

6.  Select the DateCalendarYear dataset within the selector.

The MDX for the dataset, which was automatically generated as one of a chain of events triggered by our creation of the parameterized filter for Year in the report’s primary dataset (ProductData), appears (rearranged, so as to make examination of the various sections easier), as shown in Illustration 17.


Illustration 17: MDX for the DateCalendarYear Dataset

Many will recognize the WITH MEMBER clauses as representing the definition of various calculated members, which we also specify within our SELECT clause for the purposes of supporting our parameter defaults. These work the same as the calculated members that appeared within the automatically generated dataset queries that we examined in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Pt. II.

NOTE: For and introduction to Calculated Members, particularly from an MDX syntax perspective, please see the following articles:

Each of these articles is a member of my MDX in Analysis Services series at Database Journal.

In this dataset, the following calculated members are created:

  • ParameterCaption
  • ParameterValue
  • ParameterLevel

We will, as we have already noted, not be referencing these fields in the DateCalendarYear Report Parameter definitions, but the underlying MDX for the parameter caption and value can be used as a template to create a calculated member in Analysis Services, as we shall see. The following portion of the MDX is of immediate use to us in our next steps.

 WITH 
 MEMBER 
    [Measures].[ParameterCaption] 
 AS 
    '[Date].[Calendar Year].  CURRENTMEMBER.MEMBER_CAPTION' 
 
 MEMBER 
    [Measures].[ParameterValue] 
 AS 
    '[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME'

7.  Return to the Calculations tab of the Cube Designer.

8.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

YearParamCaption

9.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Year]. CURRENTMEMBER.MEMBER_CAPTION

The new YearParamCaption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 18.


Illustration 18: The YearParamCaption Calculation in the Calculations Expressions Pane

10.  Click the Check Syntax button atop the Calculations tab, as shown in Illustration 19.


Illustration 19: Click the Check Syntax Button

The Check Syntax message box appears, indicating that the syntax check was successful, as depicted in Illustration 20.


Illustration 20: Our Syntax Appears to Have Passed Checking ...

11.  Click OK to dismiss the message box.

We have successfully added support for the caption display of the Year Report Parameter. We will now add a calculated member to house the logic behind the value (the “qualified” MDX name) we wish to pass for the same Report Parameter, based upon our caption selection at runtime.

12.  From our current position within the Calculations tab, once again, click the bottom entry in the Script Organizer pane, to anchor the cursor.

NOTE: Once a calculation is created, we can always move it up or down within the Script Organizer pane using the Move Up or Move Down buttons, respectively. These buttons appear together atop the Calculations tab, as shown in Illustration 21.


Illustration 21: Move Up and Move Down Buttons, atop the Calculations Tab

13.  Click the New Calculated Member button, once again.

14.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

YearParamValue

15.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

 [Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME

The new YearParamValue calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 22.


Illustration 22: The YearParamValueCalculation in the Calculations Expressions Pane

16.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

17.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have successfully added support for the value display of the Year Report Parameter. This means we have now added support for both the caption and value fields of the Report Parameter within our report for Year. We will next add, while we are “under the hood” within Analysis Services, a similar pair of calculated members for each of the two remaining, date-related Report Parameters we have selected for our practice exercise, DateCalendarQuarterofYear and DateMonthofYear.

18.  Click the New Calculated Member button, once again.

19.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

QtrParamCaption

20.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

 [Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION

The new QtrParamCaption calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 23.


Illustration 23: The QtrParamCaption Calculation in the Calculations Expressions Pane

21.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first pair of calculated members we added.

22.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

23.  Click the New Calculated Member button, once again.

24.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

QtrParamValue

25.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Quarter of Year].CURRENTMEMBER.UNIQUENAME

The new QtrParamValue calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 24.


Illustration 24: The QtrParamValue Calculation in the Calculations Expressions Pane

26.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the calculated member we added earlier.

27.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

28.  Click the New Calculated Member button, once again.

29.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

MoParamCaption

30.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION

The new MoParamCaption calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 25.


Illustration 25: The MoParamCaption Calculation in the Calculations Expressions Pane

31.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the other calculated members we added.

32.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

33.  Click the New Calculated Member button, once again.

34.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

MoParamValue

35.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

 [Date].[Month of Year].CURRENTMEMBER.UNIQUENAME

The new MoParamValue calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 26.


Illustration 26: The MoParamValue Calculation in the Calculations Expressions Pane

36.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

37.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have completed the addition of the Analysis Services objects required to support the targeted date-related Report Parameters in the Reporting layer.

Process the Analysis Services Database

Our next step will be to process the sample database, so as to make the new calculated members available to Reporting Services. To do so, we will take the following steps.

1.  Within the Solution Explorer, right-click the Adventure Works DW project.

2.  Select Process ... from the context menu that appears, as shown in Illustration 27.


Illustration 27: Processing the Adventure Works DW Project

3.  Click the Yes button on the message box appearing next, asking if we wish to save all changes before processing, as depicted in Illustration 28.


Illustration 28: Saving Changes First ...

The Process Database – Adventure Works DW dialog appears, as shown in Illustration 29.


Illustration 29: The Process Database – Adventure Works DW Dialog

4.  Click Run ... on the Process Database – Adventure Works DW dialog, to begin processing.

The Process Progress viewer appears, and details the processing steps as they take place. Once processing is completed, a Process succeeded message appears in the Status bar at the bottom of the dialog, as depicted in Illustration 30.


Illustration 30: Process Succeeded Message Appears ...

5.  Click Close to close the Process Progress viewer.

6.  Click Close on the Process Database – Adventure Works DW dialog.

We can now move back to our report to complete the steps required to access our new Analysis Services parameter support objects, and to reference them in our targeted Report Parameters.

Practice: Support Parameters in Reporting Services via Analysis Services Objects

We will next access and reference our new Analysis Services support objects from within our sample report.

Access Analysis Services Parameterization Support via New Datasets

We will return to the Data tab of our still open report within Reporting Services, where we will create three new datasets, one for each of the Report Parameters for Year, Quarter, and Month, within the following steps. (While we might achieve our ends through other arrangements, we will provide a straightforward solution example in this practice session, which we will build upon in subsequent articles).

1.  Leaving the Cube Designer open at the present position, return to the Data tab of the report with which we are working in Reporting Services.

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

3.  Select <New Dataset> within the selector, as shown in Illustration 31.


Illustration 31: Creating a New Dataset

The Dataset dialog opens.

4.  Type the following into the Name box of the dialog (replacing the default name of “DataSet1”).

AS_DateYrParams_Support

5.  Click OK to dismiss the dialog and to open the graphical query designer.

6.  Click the Design Mode button to shift to the generic query designer, as depicted in Illustration 32.


Illustration 32: Shifting to the Generic Query Designer ...

7.  Click the Refresh button on the Data tab toolbar, as shown in Illustration 33.


Illustration 33: Refresh the Environment for the Newly Processed Data Source ...

8.  Type (or cut and paste) the following into the Query pane of the generic query designer, replacing the default, shell syntax:


SELECT 
   {[Measures].[YearParamCaption], [Measures].[YearParamValue] } ON COLUMNS, 
   {[Date].[Calendar Year].MEMBERS} ON ROWS 
FROM 
   [Adventure Works]

9.  Click the Execute Query (!) button to run the query against the Analysis Services data source, as depicted in Illustration 34.


Illustration 34: Execute the New Query ...

The query runs, and the data is returned, as shown in Illustration 35.


Illustration 35: The New Dataset Appears ...

As we can see, we have the workings of a good support dataset for the Year parameter picklist – both a caption and a value. Let’s assume we will want to eliminate the “All Periods” option within this (and the other) datasets. One way to accomplish this can be handled with a query filter.

10.  Click the ellipses (...) button to the immediate right of the Dataset selector (currently showing our new AS_DateYrParams_Support dataset), as depicted in Illustration 36.


Illustration 36: Click the Ellipses Button to the Right of the Dataset Selector ...

The tabbed Dataset dialog opens.

11.  Click the Filters tab.

12.  Type the following syntax into the Expression column of the Filters table, top row:

=CSTR(Fields!Calendar_Year.Value)

13.  Select the “>” (“greater than”) sign in the adjacent Operator box of the top row.

14.  Type the following into the Value column to the immediate right of the Operator column, in the top row:

=”0”

The Filters tab, with our input, appears as shown in Illustration 37.


Illustration 37: Our Newly Added Filter ...

15.  Click OK to accept our input, and to dismiss the Dataset dialog.

In like manner, we will add a filtered dataset to support each of the two remaining Report Parameters we have selected for our practice exercise, DateCalendarQuarterofYear and DateMonthofYear.

16.  Click the downward selector arrow on the right side of the Dataset selector, once again.

17.  Select <New Dataset> within the selector, as we did earlier.

The Dataset dialog opens.

18.  Type the following into the Name box of the dialog (replacing the default name of “DataSet1”).

AS_DateQtrParams_Support

19.  Click OK to dismiss the dialog and to open the graphical query designer.

20.  Click the Design Mode button to shift to the generic query designer, as before.

21.  Type (or cut and paste) the following into the Query pane of the generic query designer, replacing the default, shell syntax:


SELECT 
   {[Measures].[QtrParamCaption], [Measures].[QtrParamValue] } ON COLUMNS,
   {[Date].[Calendar Quarter of Year].MEMBERS} ON ROWS 
FROM 
  [Adventure Works]

The query appears in the Query pane of the query designer, as depicted in Illustration 38.


Illustration 38: The Query within the Query Pane

22.  Click the Execute Query (!) button to run the query against the Analysis Services data source, as we did for the earlier dataset.

The query runs, and the data is returned, as shown in Illustration 39.


Illustration 39: The New Dataset Appears ...

We will again eliminate the “All Periods” selection from our parameter picklist at runtime by adding a query filter entry.

23.  Click the ellipses (...) button to the immediate right of the Dataset selector (currently showing our new AS_DateQtrParams_Support dataset), as we did before.

24.  Click the Filters tab on the Dataset dialog that opens next.

25.  Type the following syntax into the Expression column of the Filters table, top row:

=CSTR(Fields!Calendar_Quarter_of_Year.Value)

26.  Select the “>” (“greater than”) sign in the adjacent Operator box of the top row.

27.  Type the following into the Value column to the immediate right of the Operator column, in the top row:

=”0”

The Filters tab, with our input, appears as depicted in Illustration 40.


Illustration 40: Our Newly Added Filter ...

28.  Click OK to accept our input, and to dismiss the Dataset dialog.

29.  Click the downward selector arrow on the right side of the Dataset selector, once again.

30.  Select <New Dataset> within the selector, as we did earlier.

The Dataset dialog opens.

31.  Type the following onto the Name box of the dialog (replacing the default name of “DataSet1”).

AS_DateMoParams_Support

32.  Click OK to dismiss the dialog and to open the graphical query designer.

33.  Click the Design Mode button to shift to the generic query designer, as before.

34.  Type (or cut and paste) the following into the Query pane of the generic query designer, replacing the default, shell syntax:


SELECT 
   {[Measures].[MoParamCaption], [Measures].[MoParamValue] } ON COLUMNS,
   {[Date].[Month of Year].MEMBERS} ON ROWS 
FROM 
  [Adventure Works]

The query appears in the Query pane of the query designer, as shown in Illustration 41.


Illustration 41: The Query within the Query Pane

35.  Click the Execute Query (!) button to run the query against the Analysis Services data source, as we did for the earlier two datasets.

The query runs, and the data is returned, as depicted in Illustration 42.


Illustration 42: The New Dataset Appears ...

We will again eliminate the “All Periods” selection from our parameter picklist at runtime by adding a query filter entry.

36.  Click the ellipses (...) button to the immediate right of the Dataset selector (currently showing our new AS_DateMoParams_Support dataset), as we did before.

37.  Click the Filters tab on the Dataset dialog that opens next.

38.  Type the following syntax into the Expression column of the Filters table, top row:

=CSTR(Fields!Month_of_Year.Value)

39.  Select the “>” (“greater than”) sign in the adjacent Operator box of the top row.

40.  Type the following into the Value column to the immediate right of the Operator column, in the top row:

=”0”

The Filters tab, with our input, appears as shown in Illustration 43.


Illustration 43: Our Newly Added Filter ...

41.  Click OK to accept our input, and to dismiss the Dataset dialog for the final time.

We are now ready to “hook up” our new datasets to the Report Parameters they will support. We will accomplish this within the next subsection.

Reference Analysis Services Support within Report Parameters

We will return to each of the three Report Parameters at this juncture, where we will reference each of the supporting datasets we created in the previous subsection within the respective Dataset, Value field, and Label field settings. We will also make a few additional changes to the pre-existing settings to make the Report Parameters a bit more user-friendly at runtime.

1.  From the main menu, select Report ->Report Parameters, as depicted in Illustration 44.


Illustration 44: Select Report ->Report Parameters

The Report Parameters dialog opens.

2.  Within the Parameters list box, on the left side of the dialog, click DateCalendarYear.

The Properties, Available values, and Default values settings for the selected Report Parameter appear.

3.  Within the Properties section on the right side of the dialog, change the Prompt setting from Date.Calendar Year to the following:

Year

4.  Ensure that all five checkboxes underneath the Prompt setting are unchecked.

5.  Within the Available values section, underneath the Properties section, ensure that the From query radio button is selected.

6.  Select AS_DateYrParams_Support in the Dataset selector, as shown in Illustration 45.


Illustration 45: Select AS_DateYrParams_Support as the Dataset ...

7.  Underneath the Dataset selector, select the following in the Value field selector:

YearParamValue

8.  Underneath the Value field selector, select the following in the Label field selector:

YearParamCaption

9.  Within the Default values section, underneath the Available values section, ensure that the radio button to the immediate left of Null is selected.

The Properties, Available values, and Default values settings for the DateCalendarYear Report Parameter appear, with our modifications, as depicted in Illustration 46.


Illustration 46: DateCalendarYear Report Parameter Settings, with Modifications

10.  Within the Parameters list box, as before, click DateCalendarQuarterofYear.

The settings for the selected Report Parameter appear.

11.  Within the Properties section on the right side of the dialog, change the Prompt setting from Date.Calendar Quarter of Year to the following:

Quarter

12.  Ensure that all five checkboxes underneath the Prompt setting are unchecked.

13.  Within the Available values section, underneath the Properties section, ensure that the From query radio button is selected.

14.   In the Dataset selector, select the following:

AS_DateQtrParams_Support

15.  Underneath the Dataset selector, select the following in the Value field selector:

QtrParamValue

16.  Underneath the Value field selector, select the following in the Label field selector:

QtrParamCaption

17.  Within the Default values section, underneath the Available values section, ensure that the radio button to the immediate left of Null is selected.

The Properties, Available values, and Default values settings for the DateCalendarQuarterofYear Report Parameter appear, with our modifications, as shown in Illustration 47.


Illustration 47: DateCalendarQuarterofYear Report Parameter Settings, with Modifications

18.  Within the Parameters list box, once more, click DateMonthofYear.

The settings for the selected Report Parameter appear.

19.  Within the Properties section on the right side of the dialog, change the Prompt setting from Date.Month of Year to the following:

Month

20.  Ensure that all five checkboxes underneath the Prompt setting are unchecked.

21.  Within the Available values section, underneath the Properties section, ensure that the From query radio button is selected.

22.  In the Dataset selector, select the following:

AS_DateMoParams_Support

23.  Underneath the Dataset selector, select the following in the Value field selector:

MoParamValue

24.  Underneath the Value field selector, select the following in the Label field selector:

MoParamCaption

25.  Within the Default values section, underneath the Available values section, ensure that the radio button to the immediate left of Null is selected.

The Properties, Available values, and Default values settings for the DateMonthofYear Report Parameter appear, with our modifications, as depicted in Illustration 48.


Illustration 48: DateMonthofYear Report Parameter Settings, with Modifications

26.  Click OK to accept all our Report Parameter modifications, and to dismiss the Report Parameters dialog.

As a final cleanup item, we will remove the datasets created automatically by Reporting Services to support the parameters we created as a part of preparation for our practice session (when we added the parameterized filters to the report). We can accomplish this by taking the following steps within the Data tab.

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

We note that the three automatically created datasets appear, including DateCalendarYear, DateCalendarQuarterofYear, and DateMonthofYear.

28.  Select the DateCalendarYear dataset within the selector.

29.  Once the dataset is selected, click the Delete Selected Dataset button to the right of the selector, as shown in Illustration 49.


Illustration 49: Deleting an Unwanted Dataset ...

30.  Click Yes on the message box that appears, asking if we are sure we want to perform the deletion, as depicted in Illustration 50.


Illustration 50: Confirm Wishes to Delete with a “Yes” ...

The automatically created dataset DateCalendarYear disappears.

31.  Delete the DateCalendarQuarterofYear and DateMonthofYear datasets in the same manner.

Having created the parameter support structures within Analysis Services, we added supporting datasets, based upon those structures, within our sample report. We then referenced those datasets to the respective Report Parameters, and, as a cleanup step, removed the datasets that Reporting Services had automatically created for each parameterized filter we set up as a part of our preparation for the practice session. We are now ready to verify the effectiveness of our solution. We will verify that the date-related Report Parameters – now supported by objects within the Analysis Services layer of our integrated BI solution – perform as expected at runtime.

Verification: Preview the Report and Inspect the Effectiveness of Our Solution

Let’s preview the report to inspect the results of our handiwork.

1.  Click the Preview tab.

DBJ_OLAP_Report.rdl initializes, and the first prompt (based upon the pre-existing ProductCategory parameter), becomes enabled.

2.  Leave the Product Category prompt selection at its default of” Bikes, Components.”

3.  Click the downward pointing arrow on the right side of the Year selector.

4.  Select CY 2004 within the Year parameter picklist.

We notice the absence of an “All Periods” selection within the Year parameter’s picklist – as we might have expected, because we filtered out that row of the respective dataset (as we did for the Quarter and Month parameter supporting datasets).

5.  Select CY Q2 from the Quarter parameter picklist.

6.  Select the month of June in the Month parameter picklist.

We notice at this stage that the parameter picklists do not cascade. Moreover, it might occur to us that intelligent defaults would give our consumers a better runtime experience. We will address these two items in an article devoted specifically to these subjects.

7.  Click the View Report button.

The report executes quickly and returns the data for the selections we have made within our parameter picklists, in a manner similar to that shown in Illustration 51.


Illustration 51: The Report Parameters Operate Largely as Expected ...

Our verification process has demonstrated the effective support of Report Parameters through the creation and use of objects within the Analysis Services layer of our BI solution. We will extend our examination of Analysis Services supported parameterization yet further in a subsequent article, where we will generate a solution to provide cascading picklists, and to meet the need of our colleagues to provide intuitive parameter defaults to information consumers at report runtime.

NOTE: Please consider saving the .rdl file we have created to this point for use in the article that follows, so as to avoid the need to repeat the preparation process we have undertaken above.

8.  Experiment further with the report, if desired.

9.  When finished with the report, click the Layout tab.

10.  Select File -> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this point, to a location where it can be easily accessed for the next article within our series.

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

Conclusion ...

In this article, we continued the extended examination of Parameters in Reporting Services that we began in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, and which we continued in Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets.

Working with the basic OLAP report we created in Reporting Services: Customize Automatically Created Parameter Support Objects, we once more began within the scenario we established within Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, with an objective of meeting the need of the hypothetical client to support parameterization, including picklists, within the report. This time, however, we exposed the steps involved in offering a simple means of supporting our solution from within the Analysis Services layer of the integrated Microsoft BI solution.

In examining the requested addition of parameter defaults within a sample OLAP report, we began with a review of the concept of parameterization in general, and briefly overviewed options for supporting report parameterization among the three primary layers of the integrated Microsoft business intelligence solution. We then opened the sample Report Server project, AdventureWorks Sample Reports, and ascertained connectivity of its shared Analysis Services data source. We continued our preparation for the practice session by creating a clone of an existing sample OLAP report, containing a Matrix data region, with which to perform our practice exercises. We then made structural modifications to the clone report, to prepare for our practice exercise session.

We discussed, and then implemented, an approach, from within Analysis Services, to meet the need of our hypothetical client to support Report Parameters from the Analysis Services layer of the integrated Microsoft BI solution. We then accessed and employed the new Analysis Services parameter support components from within Reporting Services. Throughout our article, we overviewed how the various components of the parameter support solution we proposed are tied together, as a part of our hands-on practice session, where we created and aligned the necessary components within Analysis Services and Reporting Services to support our runtime parameters. Finally, we previewed the report to observe the effectiveness of our solution in runtime action, and looked ahead to our next article, where we will examine the establishment of cascading picklists into our current solution, together with the addition of intuitive parameter defaults at runtime, for greater user-friendliness and overall operational efficiencies.

» 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