Support Parameterization from Analysis Services

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

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:

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:

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:

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:

The Connect to Database dialog appears.

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

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.

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:

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.

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.

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

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.

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.

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

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.

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

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

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

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.

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.

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.

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:

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.

The Calculations tab opens.

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.

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.

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.

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'

YearParamCaption

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

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

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

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.

YearParamValue

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

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

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.

QtrParamCaption

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

QtrParamValue

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

MoParamCaption

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

MoParamValue

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

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.

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

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.

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

The Dataset dialog opens.

AS_DateYrParams_Support

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

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

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.

The tabbed Dataset dialog opens.

=”0”

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

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.

The Dataset dialog opens.

AS_DateQtrParams_Support

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.

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

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

=”0”

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

The Dataset dialog opens.

AS_DateMoParams_Support

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.

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

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

=CSTR(Fields!Month_of_Year.Value)

=”0”

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

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.

The Report Parameters dialog opens.

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

Year

YearParamValue

YearParamCaption

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

The settings for the selected Report Parameter appear.

Quarter

AS_DateQtrParams_Support

QtrParamValue

QtrParamCaption

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

The settings for the selected Report Parameter appear.

Month

AS_DateMoParams_Support

MoParamValue

MoParamCaption

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

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.

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

The automatically created dataset DateCalendarYear disappears.

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.

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

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

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.

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.

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.

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.