Reporting Services: Customize Automatically Created Parameter Support Objects
January 16, 2008
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.
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.
Type-in parameters accept directly typed user input for the values upon which the report is based. Alternatively, the picklist presents a selection of choices to a consumer based upon a static file, a dataset from a larger data source, or through other means. The picklist is often the tool of choice, because of its inherent elimination of typing errors, as well as the enforcement of standard selections. A well-constructed picklist makes selection easy for the consumer, who is not often pleased with a long scrolling process, or other cumbersome method, as the initial step in generating a commonly requested report.
Over the years, I have implemented parameters in many ways to fit many 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.
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 picklist parameterization 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.
In this article, we will discuss the general concepts, and then set up a scenario within which we work with a basic OLAP report, to expose the steps involved in meeting a basic need of a hypothetical client in modifying the default behavior of automatically created parameter picklists to display an All option at runtime. We will review the mechanics that underlie the automatic creation of parameter support objects by Reporting Services, as well as touching upon potential issues that might arise when we modify elements of those objects once they are created. In examining the requested addition and customization of parameters within an OLAP report containing a Matrix data region, we will:
Customizing Auto-Created Parameter Support Objects in Reporting Services 2005
Objective and Business Scenario
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.
We introduced parameterization in general, discussing the challenges that faced many of us within the Reporting Services 2005 environment, in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I. We continued our general discussion of parameterization in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, where we explored the underlying mechanics that support parameters, focusing particularly upon several events that take place in conjunction with parameter creation in the design environment. We further extended our Part I examination of the interaction among the various components that comprise and support run-time parameterization, and focused, in Part II, upon the actions that Reporting Services takes behind the scenes in our creation of cascading parameters.
In this two-part article, we continue the extended exmination of Parameters in Reporting Services 2005, concentrating upon a couple of specific refinements we can make to our parameters to fine tune them to local business environments. We will begin, once again, by adding simple filter / parameter combinations to a basic sample OLAP report, containing a Matrix data region, via the graphical interface of the MDX Query Builder. We will review, 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 then delve into the modifications we need to make to meet the requirements of a hypothetical client, the AdventureWorks organization.
To detail the specifics, we have been contacted by a team with which we consult regularly, the Adventure Works Customer Care department, whose work centers around analyzing and reporting upon customer satisfaction indicators, among various other information, in support of the overall organization in accomplishing its objectives. Our client colleagues inform us that information consumers have asked that they refine an existing OLAP report, Sales Reason Comparisons, to make the existing parameterization a bit more user friendly. First, they wish to be able to select Year, Quarter and Month for which the report is run (the initial report, a sample authored by an intermediate-level employee who has since returned to his home country, presents data for all time, allowing users the capability of selecting Product Class(es) only at runtime). Moreover, the team wants a brief overview of the mechanics involved in the automatic creation of the support objects that occurs as we create the new parameterized filters, primarily so that they can understand the relationships between these components to allow for ongoing maintenance.
Because the client representatives have worked with date-related parameterization in other reports and systems, they already know that, in addition to merely being able to specify dates through standard picklists, they want to request a couple of enhancements to the simple prompts they have seen thus far in reports with similar runtime parameters. First, they have noted in several OLAP reports that, even though the authors did not design the reports to provide multi-value selection in the picklists (something they so not want in the report under consideration, either), an All Periods selection appears in each of the date selectors (say, for Year, Quarter, Month, etc.), which confuses some consumers (reporting requirements will be restricted to single selections within each level of the Date dimension to meet the current, simple needs of the group). Moreover, the group tells us that they want the prompts to reflect intelligent, intuitive defaults, and not just arbitrary, hard-coded values or the <Select a value> label that might otherwise appear, if no defaults at all are in place.
We confirm our understanding of the foregoing needs, and propose to first add the requested date-related parameters, and then to eliminate the All Periods selection from the respective picklists entirely. Having accomplished this, we will construct working examples, in the second half of this article, of ways 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.