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;



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



Note: Current Service Pack updates are assumed for the operating system, along with the applications and components listed above and the related Books Online and Samples. Images are from a Windows 2003 Server environment, within which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.


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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles