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.