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

Multiple Value Selection in a Parameter Picklist

Introduction and Business Scenario

We will begin our examination of using Reporting Services 2005 to support multiple value input in a Report Parameter by proposing, and then satisfying, a business need within our usual context of a hypothetical scenario. The scenario will at least partially represent a situation that many of those who have implemented Reporting Services 2000 will confront in the near term, as it will involve converting an existing Reporting Services 2000 .rdl file to Reporting Services 2005, to take advantage of new features and enhanced performance within the new version.

Dubbed the "BI Release," MSSQL Server 2005 serves as the foundation of the integrated Microsoft BI solution. One of the most compelling improvements manifested in this release is the dramatic increase in the integration of its components, MSSQL Server, Analysis Services and Reporting Services, among others. Indeed, the easy juxtaposition of the various tools and applications within the SQL Server Business Intelligence Development Studio, within whose unified, central interface we will be performing the steps of our practice session, make development between the various parts of the solution more organized and sophisticated.

To establish a backdrop for the exercise ahead, we will assume that we have received a request from a group of client information consumers in the Operations department of the FoodMart organization. The request is for support in upgrading a popular existing Reporting Services 2000 report to Reporting Services 2005. In an enterprise strategy meeting prior to the general Operations upgrade, where this and other departments' reporting libraries were identified and scheduled to be upgraded, we presented the new features of Reporting Services 2005 to a committee whose members represented the majority of the affected departments. One of the new features we mentioned was Reporting Services 2005's support of multi-value parameter input. We were asked, with few exceptions, to leverage this enhancement in all reports that contain parameters, as part of the upgrade process for each department's libraries.

The report under immediate consideration, the first of the report library of the Operations unit to undergo upgrade, is the FoodMart Sales report, an OLAP report that presents sales, costs and profit information for the FoodMart product offerings. The report offers drilldown to various levels within the Product hierarchy, as well as a simple Report Parameter, from which we can select a single Product Family group, among three possibilities, to display in the report. It is this parameter, together with some other settings, that will be affected in our upgrade.

In addition, the consumers have asked that we add a textbox, to appear atop the report at runtime that displays the parameter selection made when the report is executed. This "labeling," they reason, will allow a casual reader of a printed, or otherwise deployed report, to discern instantly the filters applied to the report whose data they were reviewing.

We confirm our understanding of the expressed needs of the Operations group, and consider the steps we will need to undertake to provide the requested deliverables.

Considerations and Comments

Because setup of a data source and reporting environment to prepare for a hands-on walkthrough of the procedure that forms the focus of this article would be time consuming, we will base our efforts upon a copy of the FoodMart Sales report that can be installed with Reporting Services 2000. Use of this report, which comes with a data source included within its definition will allow us to skip many steps of setup that would accrue to a "from scratch" construction effort. We will also need access to the Sales sample cube that underlies the report, and which is installed along with Analysis Services 2000. The steps of this article assume, therefore, the presence of Analysis Services 2000, Reporting Services 2000 and Reporting Services 2005 (either "side-by-side" on the same PC, or with network interconnectivity).

Keep in mind that, while the above combinations are not uncommon in an upgrade scenario (I typically do them in very similar fashion for several reasons), I realize that first time implementations of the relevant components are a reality, and that some of the assumptions will not apply. In addition, remember that we can upgrade a 2000 report to 2005 without a 2000 data source, assuming we have a substantially identical surrogate data source established within the 2005 environment already. However, the establishment of the prerequisites to make this happen are beyond the scope of this lesson, whose focus is to meet requirements similar to those outlined in the hypothetical business requirements above.

Practice

Our first objectives are to create a copy of the FoodMart Sales report from Reporting Services 2000, which we will then upgrade to Reporting Services 2005, so that we can assimilate the new Report Parameter enhancements into the report design from the perspective of the powerful SQL Server Business Intelligence Development Studio. Once we have a Reporting Services 2000 report upgraded to this new environment, we can take advantage of many other new features, including the integration of entire BI solutions (among whose components might number Analysis Services cubes and other objects, Integration Services packages, Reporting Services report files, and many other objects) into common source control.

Before we can upgrade the report, we will need to create a Project within the design environment to house it.