MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist

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"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a presentation component within an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise business intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed / accessible, according to the respective documentation that accompanies MSSQL Server 2005:

•   Microsoft SQL Server 2005 Database Engine;

• Related Articles

  • MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
  • MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
  • MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting

    Microsoft SQL Server 2005 Reporting Services;

•   Business Intelligence Development Studio.

  To successfully replicate the steps of the article, you also need to have:

•   Microsoft SQL Server 2000 Analysis Services;

•   Microsoft SQL Server 2000 Sample Analysis Services Databases and Cubes;

•   Microsoft SQL Server 2000 Reporting Services Sample Reports;

•   Business Intelligence Development Studio.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application commoditizes business intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

As most organizations implementing or evaluating MSSQL Server Reporting Services 2000 discovered, the application did not afford us an "out-of-the-box" way to support multiple value selections within a Report Parameter by a consumer at runtime. This caused frustration among many of the clients for whom I implemented Reporting Services 2000, as well as with many of my readers, who have corresponded with me regarding workarounds. Many of these organizations were migrating, or proving the concepts of migrating, from enterprise reporting systems whose relational and / or OLAP reporting solutions offered multi-value selections within parameter picklists and so forth. Having become accustomed, within a given report, to having the capability to simply hold down the CTRL key, and then to select multiple, not-necessarily-contiguous items from a picklist that appeared at runtime, had established the selection of multiple values as a standard feature. The angst I witnessed at various client sites, and in e-mails and calls, did not surprise me; if I had not known, from the days of my beta participation in Reporting Services, that 2000 was, in fact, an accelerated release to begin with, I would have probably thought that the absence of this basic consumer staple was a bizarre oversight.

In this article, we will look at the multiple selection support within Report Parameters that arrived with Reporting Services 2005. Moreover, we will gain some hands-on exposure to migrating a Reporting Services 2000 sample report with a simple Report Parameter in place. The steps we take within the context of the sample report will allow us to experience, in detail, what is involved in bringing the new capability to select multiple values to existing Reporting Services 2000 reports in our own environments.

And so we witness the introduction of another enterprise reporting "equalizer." The voices that propose arguments against implementing Reporting Services are becoming fewer, as Reporting Services advances to close many of the remaining gaps that retreating competitors attempted to use as distinguishing capabilities of their products. The lack of support for multi-value parameter input was one tiny example of "shortcomings" touted by organizations with significant investments in other enterprise tools, such as Cognos, Business Objects, Crystal, and MicroStrategy. I will be exploring other such "equalizers" that have been added in Reporting Services 2005 in articles to come in the MSSQL Server Reporting Services series.

In examining the accommodation of multi-value parameter input in Reporting Services 2005, we will:

• Prepare for our practice session by creating a project within Reporting Services, and by creating a "clone" report (based upon an existing Reporting Services 2000 sample OLAP report to save time), within which we will perform our exercises;
• Upgrade the report to Reporting Services 2005;
• Remove the existing Report Parameter, together with a filter that references it, to allow for recreation of both with the new multi-value input capabilities;
• Add a textbox to the report containing an expression to display our parameter picklist selections on the face of the executed report;
• Verify the operation of our enhancements in a test of report operation.