Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets

Join BI Architect Bill Pearson as he continues his examination of parameterization within Analysis Services reports. In this article, we provide a solution for supporting parameter defaults that represent the last periods of data entry within our cube.

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 the first half of this article, 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 that present long scrolling processes or other cumbersome methods can negatively affect consumer perceptions, particularly when they are components within commonly requested reports.

When working with clients to design and create virtually any enterprise report, I ask questions about the report’s objectives and intended appearance; moreover, I ask questions about source data, filtering, and other considerations involved with the information retrieved by the underlying datasets. When I reach the point at which we discuss parameterization (the vast majority of reports contain parameters), I virtually always follow up with questions about desired parameter defaults.

Parameter defaults are important to consider for a couple of primary reasons. First, and certainly the most basic, is that the absence of parameter defaults within a report results in one of two things at runtime: either all sits idle until the information consumer makes parameter selections and executes the reports, or a blank report is generated (assuming the Report Parameters dialogs involved allow blank values within the parameter input / selector boxes). While neither of these conditions presents an issue to consumers understanding underlying settings, each can still mean inconvenience in general and perhaps confusion to less experienced users.

The second design consideration of importance that surrounds Parameter defaults goes beyond their simple absence or presence, and focuses more upon their intuitiveness. By “intuitive,” I mean “dynamically intelligent.” While it is quite desirable to select among numerous criteria at runtime, we can add finesse to the selections we offer by having the more likely desired selections, considering the nature of the report and its typical usage by information consumers, appear as defaults at runtime. The obvious objective is to make the steps involved in report execution more efficient, for most of the consumers, most of the time.

In this article, we will continue working with the basic OLAP report we created in the first half of this article, Customize Automatically Created Parameter Support Objects Pt. 1. We will establish a scenario within which we expose the steps involved in meeting a basic need of a hypothetical client in adding runtime default selections that will appear within the parameter picklists of the report. In examining the requested addition of parameter defaults within an OLAP report containing a Matrix data region, we will:

• Reopen the sample Report Server project, AdventureWorks Sample Reports, and access the existing sample report we prepared in Pt. I.
• Discuss options for supporting intelligent parameter picklist defaults among the three primary layers of the integrated Microsoft business intelligence solution.
• Discuss an approach to meeting the need of our hypothetical client to present parameter picklist defaults that represent the last period of data entry in our cube.
• Create a dataset to provide parameter default support in the Reporting Services layer of the client’s BI solution.
• Overview how the various components of the default support solution we propose are tied together, as a part of a hands-on practice session where we create and align the necessary components to support our parameter defaults.
• Preview the report to observe the effectiveness of our solution in runtime action.

Support OLAP Parameter Defaults Using Datasets

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. As I discussed in detail in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, 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) we need to quickly add basic parameterized filters to our reports. While the automatically created objects serve us well with minimal modification in many cases, we often have to “tweak” the components supporting parameterization a bit to obtain more sophisticated features. Our practice session within this article contains an example of the sort of customization to which I refer.

Another important consideration during the design phase of any implementation effort, too, 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 is used as a data source for the reports under consideration, and so forth. (For an example of implementing support for a hierarchical picklist in this manner, see my Database Journal article Create a Cube-Based Hierarchical Picklist.) 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 we have emphasized in earlier articles, 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.

Reporting Services offers a robust Report Parameter interface that is exposed through the Report Designer, through its Web service interface, through the Report Manager, and via the reports we schedule. 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, the second half of a two-part 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. Having created a basic sample OLAP report containing a matrix data region, to which we added simple filter / parameter combinations, in Pt. I, and having reviewed, as part of our preparation of the sample report for our practice session, the objects that Reporting Services automatically creates, in conjunction with our additions, to support runtime parameterization, we will next delve into the modifications we need to make to meet additional business requirements of a hypothetical client, the AdventureWorks organization.

To detail the specifics, the same client team with which we consulted in Pt. I, the Adventure Works Customer Care department, has made an additional request for our support in accomplishing its objectives. Our client colleagues inform us that information consumers have asked that they refine the report we created together in Pt. I to make the existing parameterization even more user-friendly by providing intuitive parameter picklist defaults. They tell us that, because the vast majority of information consumers accessing this report seek to return results based upon the “most recent period” (including Year, Quarter, and Month) for which our cube contains data, parameter defaults reflecting these most recent periods would mean more rapid report execution for most users, while still allowing consumers with different requirements the capability of specifying their own specific needs.

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 intelligent parameter defaults within the integrated Microsoft business intelligence solution. In this article we will demonstrate a means for a accomplishing our ends that is completely contained within Reporting Services. But it is important to remember that we might also add objects to support dynamic parameter defaults within the Analysis Services layer (via calculated members / named sets, etc.) or even from the relational layer (where we might support the needs via tables constructed for that purpose, User Defined Functions (“UDFs”), or via other mechanisms). One of the numerous advantages of “placing the intelligence” into layers outside Reporting Services lies within the fact that we would thus avoid adding the intelligence to support our needs within every 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 provide demonstrations of these approaches within other articles of this series.)

Because our client colleagues tell us that they want a solution which is contained completely within Reporting Services, we decide to take a relatively straightforward path, the creation of a dataset within our sample report, from which we will populate the targeted Report Parameter default settings. The client team requests a brief overview of the MDX involved in the creation of such a dataset, as well as help in understanding the relationships between the components involved in the overall solution, to allow for ongoing maintenance, as well is for the extrapolation of the concepts to meet other reporting needs.

We confirm our understanding of the foregoing needs, and propose to construct working examples of a way to provide the intuitive defaults that our colleagues have requested for the new report parameters – examples, we add, that can be modified to specify different defaults, should the consumers request them after “road testing” the initial labels.

Preparation

Preparation: Locate and Open the Report Project and Report Created in Pt I

As a part of preparation for our practice session in Pt. I, we created a clone of a sample OLAP report, containing a matrix data region, for which we ascertained connectivity of its shared Analysis Services data source. We then made structural modifications to the report, to prepare for our practice exercise session with three date-related parameters, whose additions to the report were requested by our hypothetical client. We created, within the graphical Design Mode of the MDX Query Builder, three filters for which parameterization was enabled via the Filter pane setting.

In conjunction with the creation of the parameterized filters, we inspected the automatically created Report Parameters and their settings, as well as the subsequently created datasets underlying the new Report Parameters. We then focused upon the modification of the respective datasets to filter the “All” parameter level from the data rows retrieved and presented within the parameter picklists at runtime. Throughout the steps we undertook, we discussed how the various components were tied together, and the potential challenges we face in modifying these objects without consideration of the resulting dependencies. Finally, we previewed the report to observe the effectiveness of our solution in eliminating the “All” selection in the parameter picklists, as well as the added benefit of the general cascading nature of the parameters at runtime.

In this half of the practice session, we will resume where we left off at the end of Pt. I, modifying our newly created Report Parameters further to support the dynamic parameter picklist defaults requested by the client, as described in the section above. We will perform the preparation and practice steps which follow within the SQL Server Business Intelligence Development Studio, as we did within Pt. I.

DBJ_OLAP_Report opens in Layout view, as shown in Illustration 1.

We are now ready to begin working with the existing report to add support for dynamic parameter picklist defaults.

Practice

Having already created a clone report containing date-related parameters, we can go directly to create a dataset that we can use, in much the same manner as we used those automatically created to support our Report Parameters (see Pt. I for an in-depth examination of the relevant objects), this time to support the default fields within each of the associated Report Parameter settings.

Procedure: Create a Dataset to Support Dynamic Parameter Picklist Defaults at Runtime

Recall that we noted, within our earlier discussion, that there are multiple ways to support dynamic parameter defaults within Reporting Services, as well as within other layers of the integrated Microsoft business intelligence solution. Our client colleagues have requested that we deliver a means of supporting defaults for the time parameters that reflect the “most recent period” (including Year, Quarter, and Month) for which our cube contains data.

One of the many versatile features of Reporting Services lies within its support of expressions throughout the various objects with which it accomplishes its mission. We can easily exploit this utility by creating a dataset, based upon an MDX query that we construct, to retrieve the Year, Quarter, and Month labels and values identifying the “most recent” of each of these periods for which data exists within our cube. We will obtain some practical experience with this within the following steps.

We arrive at the Data view.

The Dataset dialog opens with the cursor defaulted at the Name input box.

DefaultDateSupport

The Dataset dialog, with our input, appears as shown in Illustration 3.

WITH 
SET
   [Last Period Sales]
AS
   'TAIL(
      FILTER(
         [Date].[Calendar].[Month].MEMBERS,
            NOT ISEMPTY ([Measures].[Internet Sales Amount])
          ),
      1
   )'

MEMBER
   [Measures].[YearParamDefaultCaption] 
AS
   '[Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER 
   [Measures].[YearParamDefaultValue] 
AS 
   '[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME' 

MEMBER
   [Measures].[QtrParamDefaultCaption] 
AS
   '[Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER 
   [Measures].[QtrParamDefaultValue] 
AS 
   '[Date].[Calendar Quarter of Year].CURRENTMEMBER.UNIQUENAME' 

 style="color: green; background: transparent;"MEMBER
   [Measures].[MonthParamDefaultCaption] 
AS
   '[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER 
   [Measures].[MonthParamDefaultValue] 
AS 
   '[Date].[Month of Year].CURRENTMEMBER.UNIQUENAME' 



SELECT 
   {[Measures].[YearParamDefaultCaption],  [Measures].[YearParamDefaultValue],
       [Measures].[QtrParamDefaultCaption], [Measures].[QtrParamDefaultValue],         
           [Measures].[MonthParamDefaultCaption], [Measures].[MonthParamDefaultValue]} 
       ON AXIS(0),

   [Last Period Sales]  ON AXIS(1)

FROM  
   [Adventure Works]

The Query pane, with our input, appears as shown in Illustration 5.

Our query accomplishes several things. First, it creates a named set (“Last Period Sales”), via the WITH SET keywords, within which is the logic to determine the “most recent period” (including Year, Quarter, and Month) for which our cube contains data.

WITH 
SET
   [Last Period Sales]
AS
   'TAIL(
      FILTER(
         [Date].[Calendar].[Month].MEMBERS,
            NOT ISEMPTY ([Measures].[Internet Sales Amount])
          ),
      1
   )'

The expression that defines Last Period Sales leverages the MDX Tail() function in conjunction with the Filter() function, along with NOT ISEMPTY(), to determine the latest (in time) Calendar Month in the cube containing data. (Because we are retrieving data at the month level, the corresponding row set returned by Reporting Services will also include, as we shall see, higher levels within the Date dimension (Calendar hierarchy) - a characteristic of Reporting Services that produces a dataset ideal for many purposes.

Many will recognize the WITH MEMBER keywords as representing the creation of various calculated members, which we also specify within our SELECT clause for the purposes of supporting our parameter defaults. These work in a manner that is very similar to the calculated members that appeared within the automatically generated dataset queries that we examined in Pt. I. They create the following six data columns within the returned dataset:

• YearParamDefaultCaption
• YearParamDefaultValue
• QtrParamDefaultCaption
• QtrParamDefaultValue
• MonthParamDefaultCaption
• MonthParamDefaultValue

The “default caption” columns will not be used within our parameter definitions, but they represent a possibly useful “label” that I would typically add into my design of datasets of this sort - mostly for use in labeling within the report, where it might be helpful to list our parameters so that “secondary” information consumers are made aware of the filters we have placed upon the data when looking at, say, a printed or exported version of the report. The “caption” version of the “default value,” (the value itself represents the actual qualified names that we pass as a filter to Analysis Services at runtime) would perhaps serve as a more understandable form of the values when used in this manner. We will see how the default values are used within the Report Parameters in our next steps.

Let's test our query for general operability at this stage, where it's convenient to do so, before going forward.

Data populates the Data pane, and appears similar to that partially shown in Illustration 7.

Procedure: Align the Value Columns within the Parameter Default Support Dataset to the Respective Default Values Settings within the Report Parameters Definitions

Having created a dataset to feed the parameter defaults, we will next go to the Report Parameters dialog to make the necessary alignments to entrain the individual default value data fields to the perspective default value selectors.

The Report Parameters dialog appears, with our adjustments highlighted, as depicted in Illustration 8.

We will next follow the same steps for each of the Quarter and Month Report Parameters.

The Report Parameters dialog appears, with our modifications, as shown in Illustration 9.

All that remain are the same steps for Month Report Parameter.

The Report Parameters dialog appears, with our adjustments, as depicted in Illustration 10.

We are now ready to leave the Report Parameters dialog.

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 prompts (including the pre-existing ProductCategory parameter), become enabled.

We see that the Calendar Year, Calendar Quarter and Month are populated with CY 2004, CY Q3, and July, respectively, as depicted in Illustration 12.

These are, in fact, the last member of each Date dimension (Calendar hierarchy) level containing data in the sample cube.

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

Our verification process has demonstrated the effectiveness of our solution. (As a further test, we can select August (the last Month option) within the Month selector and re-run the report. No results are returned.) Our client colleagues express satisfaction with, and understanding of, the steps we have taken and the results we have obtained, and state that they will be able to extend the concepts to other reports. We have also noted that the Reporting Services 2005 environment, along with the integrated Microsoft business intelligence solution as a whole, supports flexible design of parameter picklist default support at multiple places within each of the database, Analysis Services, and Reporting Services layers.

We will extend our examination of parameterization yet further in subsequent articles.

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, Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, and which we continued in the first half of this article, Customize Automatically Created Parameter Support Objects Pt. I. Continuing to work with the basic OLAP report we created in Pt. I, we established a scenario within which we exposed the steps involved in meeting a basic need of a hypothetical client in adding runtime defaults that appear within the parameter picklists of the report.

In examining the requested addition of parameter defaults within an OLAP report containing a matrix data region, we reopened the sample Report Server project, AdventureWorks Sample Reports, and accessed the existing sample report we prepared in Pt. I. We then touched upon options for supporting intelligent parameter picklist defaults among the three primary layers of the integrated Microsoft business intelligence solution, before focusing upon an approach to meeting the need of our hypothetical client to present parameter picklist defaults representing the last periods of data entry within our cube.

We created a dataset to provide parameter default support in the Reporting Services layer of the client’s BI solution, and then overviewed how the various components of the parameter default support solution we proposed were tied together, as a part of a hands-on practice session where we created and aligned the necessary components to support our parameter defaults. Finally, we previewed the sample report to observe the effectiveness of our solution in runtime action.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.