Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 16, 2008

Reporting Services: Customize Automatically Created Parameter Support Objects

By William Pearson

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:

  • 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 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 exercise session with the customization of Parameter Picklists within our report;
  • Create, within the graphical Design Mode of the MDX Query Builder, multiple filters for which parameterization is enabled via the Filter pane setting;
  • Examine the automatically created Report Parameters and their settings;
  • Review the automatically created Datasets underlying the new Report Parameters;
  • Discuss how the various components are tied together, and potential challenges we face in modifying these objects without consideration of the resulting dependencies;
  • Make modifications to remove the “All” selection that appears by default in our newly created parameters’ picklists at runtime;
  • Preview the report to observe the effectiveness of our solution, as well as the cascading nature of the parameters created, in runtime action.

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM