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

Monday Nov 14th 2005 by William Pearson
Share:

Architect Bill Pearson conducts hands-on practice in creating a report parameter that allows users to enter multiple values at runtime. First, we migrate a Reporting Services 2000 report to Reporting Services 2005 ...

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.

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.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of the article more efficiently.

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.

Preparation: Set up the Reporting Services Environment

For purposes of our practice session, we will create a copy of the Foodmart Sales report, one of several samples that are available for installation with Reporting Services 2000. Creating a "clone" of the report means we can make changes to our report while retaining the original sample in a pristine state – perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Before we can work with a clone of the Foodmart Sales report, we need to create a Report Server project in the new SQL Server Business Intelligence Development Studio. Making preparatory modifications, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments. (If you already have a project within which you like to work with training or development objects, you can simply skip the Create a Report Server Project section, and add the FoodMart Sales report copy to the project of your choice.)

If the sample FoodMart 2000 Analysis Services database or the Foodmart Sales report was not created / installed as part of the initial installation of the associated application, if either was removed prior to your beginning this article, or if either or both applications have yet to be installed, etc., see the respective Books Online and other documentation supplied with MSSQL Server 2000, Analysis Services 2000 and / or Reporting Services 2000 for the necessary procedures to prepare for the exercises that follow.

Create a Report Server Project

To begin, we will launch the new SQL Server Business Intelligence Development Studio.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

The equivalent on my PC appears as depicted in Illustration 1.


Illustration 1: Launching SQL Server Business Intelligence Development Studio

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 2.


Illustration 2: Selecting a New Project

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Server Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

RS023_MULTI_VAL_PARAM

8.  Navigate to a location in which to place the Report Project files.

The New Project dialog appears, with our input, as depicted in Illustration 3.


Illustration 3: The New Project Dialog, with Our Input

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 4.


Illustration 4: The New Project Appears in the Solution Explorer

10.  Close the Start page within the Visual Studio .NET 2005 interface to clear space in which to work.

Having created a Reporting Services 2005 Report Project, we are ready to proceed with creating the clone of a Reporting Services 2000 report.

Create a Copy of the Foodmart Sales Report

As we have noted, we will begin with a copy of the Reporting Services 2000 Foodmart Sales report, which we will upgrade to Reporting Services 2005 for purposes of our practice exercise.

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Existing Item ... from the cascading context menus that appear, as depicted in Illustration 5.

Click for larger image

Illustration 5: Adding the Report to the Project ...

The Add Existing Item – RS022_MULTI_VAL_PARAM dialog appears.

When we installed Reporting Services 2000, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Microsoft SQL Server\MSSQL\
Reporting Services\Samples\Reports

3.  Using the Add Existing Item - RS022_MULTI_VAL_PARAM dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - RS022_MULTI_VAL_PARAM dialog, having been pointed to the sample Reports folder (which contains the Foodmart Sales report we seek), appears as partially shown in Illustration 6.

Click for larger image

Illustration 6: Navigating to the Sample Reports Folder ...

4.  Right-click the Foodmart Sales report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 7.


Illustration 7: Performing a Quick Copy of the FoodMart Sales Report

6.  Within the Add Existing Item - RS022_MULTI_VAL_PARAM dialog, navigate to the RS022_MULTI_VAL_PARAM folder we created earlier, when we added the new RS022_MULTI_VAL_PARAM project.

7.  Right-click somewhere in the white space inside the RS022_MULTI_VAL_PARAM folder, within the dialog.

8.  Select Paste from the context menu that appears, as shown in Illustration 8.


Illustration 8: Select Paste within the New Folder ...

A copy of the Foodmart Sales report appears within the dialog.

9.  Right-click the new file.

10.  Select Rename from the context menu that appears.

11.  Type the following name in place of the highlighted existing name:

RS022_MULTI_VAL_PARAM.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Foodmart Sales sample report appears as depicted in Illustration 9.


Illustration 9: The New Report File, RS022_MULTI_VAL_PARAM.rdl

12.  Click the white space to the right of the file name, to accept the new name we have assigned.

13.  Re-select the new file by clicking it.

14.  Click Add on the dialog box to add the new report to report project RS022_MULTI_VAL_PARAM.

RS022_MULTI_VAL_PARAM.rdl appears in the Reports folder, within the RS022_MULTI_VAL_PARAM project tree in the Solution Explorer, as shown in Illustration 10.


Illustration 10: The New Report Appears in Solution Explorer – Report Folder

15.  From the main menu in the design environment, select File ---> Save All, as depicted in Illustration 11.


Illustration 11: Select File --> Save All to Save Our Work So Far ...

We now have a Reporting Services 2000 report file within our Reporting Services 2005 Project. Our next steps will upgrade the report, allowing us to leverage the new features available within Reporting Services 2005.

Upgrade the New Report to Reporting Services 2005 and Finalize Preparation

Upgrading a Reporting Services 2000 report to the new version is as simple as opening the reports, and then indicating our willingness to perform the upgrade, as we shall see. Let's open the new RS022_MULTI_VAL_PARAM report, to verify its operation, as well as to make a few observations on its existing Report Parameter. We will then prepare the report to take advantage of the new multiple value parameter capabilities offered within Reporting Services 2005.

1.  Right-click RS022_MULTI_VAL_PARAM.rdl in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 12.


Illustration 12: Opening the New Report ...

A message box appears, warning us that the report file must be converted to the current report definition format, and that, by making the conversion, we will sacrifice the file's backward compatibility with previous versions of Reporting Services. The message box appears as depicted in Illustration 13.

Click for larger image

Illustration 13: Notification of the Need For, and Consequences of, File Conversion

3.  Click Yes to convert the report file to Reporting Services 2005.

RS022_MULTI_VAL_PARAM opens in Layout view.

4.  Click the Preview tab to execute the report.

RS022_MULTI_VAL_PARAM executes, and appears on the Preview tab, where we can see the operational Report Parameter picklist when we click on the selector button. In this parameter, created within the more limited capabilities of Reporting Services 2000, we can select a single Product Family, as shown in Illustration 14.


Illustration 14: We Can Only Select a Single Picklist Value ...

As we have discussed, the information consumers with whom we are working have asked that we endow the report with a parameter that supports the selection of multiple values in the picklist. Because this is supported in the current version of Reporting Services, we have only to make adjustments in two places in the Layout view. To illustrate the procedure "from scratch," for the benefit of those who are seeking guidance in creating such a report parameter for the first time, we will eliminate the existing parameter and an associated filter reference, and create the parameter anew in our sample report.

5.  Click the Layout tab to return to the Layout view.

6.  Select Report --> Report Parameters from the main menu atop the Report Designer, as depicted in Illustration 15.


Illustration 15: Select Report --> Report Parameters from the Main Menu

The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 16.


Illustration 16: The Report Parameters Dialog

(For the sake of easy comparison, I include the dialog - for the same parameter - from the Reporting Services 2000 environment in Illustration 17.)


Illustration 17: The Report Parameters Dialog for the Previous Version

7.  In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.

8.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

9.  Click OK to accept our removal of the parameter.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report.

Having removed the sole Report Parameter, we now must delete a remaining reference to it, which we can access via the Properties dialog for the matrix.

10.  Click at some point within the label textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

11.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

12.  Select Properties from the context menu that appears, as shown in Illustration 18.


Illustration 18: Accessing the Matrix Properties

The Matrix Properties dialog opens, defaulted to the General tab.

13.  Click the Filters tab.

We note that, within the top row of the Filter list, we have the expression detailed in Table 1.

Expression

Operator

Value

=Fields!Product_Family.Value

=

=Parameters!ProductFamily.Value


Table 1: Filter Expression for Parameter with Single Value Input

14.  Click the Value field of the single occupied row to select it.

15.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 19.


Illustration 19: Select and Delete the Parameter Reference

The remaining reference to the now-deleted parameter is itself deleted.

16.  Click OK to accept our changes and to close the Matrix Properties dialog.

Having completed the preparation phase of our practice session, we are now ready to undertake creation of a Report Parameter that meets the needs specified by the information consumers for multiple value input.

Procedure: Add Support for Multiple Value Selection in a Report Parameter

The procedure for creating a Report Parameter in Reporting Services 2005 is similar to doing so in Reporting Services 2000. Many of the following steps will be familiar to most of us. Our specific focus in this section will be the addition of support for multiple value selection in the parameter picklist, which involves additional settings in the Report Parameter dialog, as well as a change in operators, within our sample report, at the matrix filter.

Create a New Report Parameter with Multiple Value Selection Support

1.  Select Report --> Report Parameters from the main menu atop the Report Designer, as we did earlier.

The empty Report Parameters dialog appears.

2.  Click Add.

3.  Type the following into the Name box atop the dialog:

pX_MultiVal_ProductFamily

4.  Type the following into the Prompt box :

Product Family(ies):

5.  Click (to insert a check mark) the checkbox to the immediate left of the Multi-value, one of five checkboxes appearing under the Prompt box.

This checkbox, a new option with Reporting Services 2005, enables the support of multiple selections in the picklist for our new Report Parameter.

6.  Ensure that the Allow null value and Allow blank value check boxes are cleared.

7.  Click the From query radio button in the Available values section of the dialog.

It is here that we align the ProductList Dataset, designed to populate the picklist that appears to the information consumers at runtime. Clicking the From query radio button enables the settings to its right, where we select the following:

  • Dataset: the report Dataset from which the values that appear in the drop-down selector are taken;
  • Value field: the Dataset field from which the value(s) used by the report (within a filter or other component) is taken;
  • Label field: the Dataset field from which the values that appear to the consumer in the picklist at runtime are taken.

8.  Select ProductList in the Dataset selector.

9.  Select ID_Product___Product_Family___MEMBER_CAPTION_ in the Value field selector.

10.  Select ID_Product___Product_Family___MEMBER_CAPTION_ in the Label field selector.

11.  Click the From query radio button in the Default values section of the dialog.

Once again, we note that selectors are enabled, this time a selector for Dataset and a selector for Value field, which serve similar functions with regard to the default parameter values.

12.  Select ProductList in the Dataset selector, as before.

13.  Select ID_Product___Product_Family___MEMBER_CAPTION_in the Value field selector, again as before.

The Report Parameters dialog appears, with our input, as depicted in Illustration 20.


Illustration 20: The Report Parameters Dialog

14.  Click OK to accept our input and to close the Report Parameters dialog.

The Report Parameters dialog closes, and we are returned to the Layout view of the report, once again.



Create a New Filter to Allow Passage of Multiple Values via the Report Parameter

We will now access the Properties dialog for the matrix once again, to create a filter based upon the new Report Parameter. The filter will be similar to the one we deleted earlier, but with one important difference, as we shall see.



1.  Click at some point within the label textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible, as we did earlier.

2.  Right-click the upper left corner of the matrix.

3.  Select Properties from the context menu that appears, as we did in the preparation section.

The Matrix Properties dialog opens, defaulted to the General tab.



4.  Click the Filters tab.

5.  Type / select the syntax shown in Table 2 into the indicated fields of the top row of the filter list.



Expression

Operator

Value

=Fields!Product_Family.Value

IN

=Parameters!ProductFamily.Value


Table 2: Filter Expression for Parameter with Multiple Value Input

NOTE: If you add the Value within the Edit Expression dialog by clicking on Parameters and selecting the single parameter offered (instead of simply typing in the value shown in Table 2 above), be sure to the remove the "(0)" that is appended to the Value expression.

The Filter tab of the Matrix Properties dialog appears, with our input, as shown in Illustration 21.

Click for larger image

Illustration 21: The Filter Tab – Matrix Properties Dialog

The difference in the above filter definition and that of its Reporting Services 2000 predecessor is the use of the "IN" operator (before it was "="). "IN" is new in Reporting Services 2005, and is required to enable the passage of multiple selections within a Report Parameter.

6.  Click OK to accept the new filter expression, and to close the Matrix Properties dialog.

We return to Layout view, from which we will accomplish a final "finesse" item. We will make the report display more consumer-friendly, in the next section, before previewing the results of our handiwork thus far.

Display Prompt Variables in the Report

Once we give information consumers the capability to select multiple values for parameter input, a somewhat concomitant need arises quite naturally: the need to display upon the face of the report the parameter value selection we have made prior to its execution. Such a "label" makes it apparent to the reader exactly what the report is intended to convey. The hypothetical consumer group in this article has had the foresight to ask for this, but oftentimes it is incumbent upon us as consultants (internal or external) to be preemptive in adding value, especially in cases where consumers are new to the applications within which we are designing / developing, and are not thus likely to be able to make informed requests.

Once-dominant reporting applications, such as Cognos Impromptu and a host of others, make a "prompt variable" object, along with other intuitive report objects (such as page number, report file name, and the like), available for easy inclusion in a report by the author at design time. Reporting Services allows us to use expressions to accommodate this, and virtually any other such need that we can imagine, as we shall see in the following steps.

1.  In Layout view, right-click the textbox containing the title FoodMart Sales (in the upper left corner of the report).

2.  Select Expression ... from the context menu that appears, as depicted in Illustration 22.


Illustration 22: Select Expression ...

The Edit Expression dialog appears.

3.  Within the upper expression box, replace the existing expression ("FoodMart Sales") with the following:

="FoodMart Sales for:  "&Join(Parameters!pX_MultiVal_ProductFamily.Value, ", ")

The upper input section of the Edit Expression dialog appears, with our replacement expression, as shown in Illustration 23.


Illustration 23: The New Expression in Place

The Join() function we have used works well for displaying the multiple parameter values, as we shall see, because it is designed to return a string created by joining (hence the function name) multiple strings contained within an array. The parameter values are returned in just such an array. If we wished to display any single member value within that array, we might do so by specifying the value under consideration in a fashion similar to the following, which would specify the "first of the values" within a group of one or more selections.

=Parameters!ProductFamily.Value(0)

The syntax for Join() is as follows:

Join(sourcearray[, delimiter])

Sourcearray would represent, in our immediate instance, the one-dimensional array of parameter values returned, based upon our selection in the picklist (we will see examples in the next section), which we seek to join via the function for display in the report. The delimiter, an optional part of the function, represents a string character we can use to separate the substrings in the returned string of values joined by the function. We have supplied a comma ("',") combined with a single space in the expression input above, simply to allow for the generation of a comma / space delimited string, to make for easy reading.

4.  Click OK to accept our new expression and to close the Matrix Properties dialog.

5.  With the same textbox still selected, select "12" in the Font Size selector within the Report Formatting toolbar beneath the main menu.

6.  Click the Bold ("B") button, to the right of the Font Size selector, to make the new report title bold.

The report appears in Layout view, with the formatting settings we have adjusted circled in red, as partially depicted in Illustration 24.


Illustration 24: Partial Layout View of the Report

We will see our handiwork in action in the next section.

Verify the Operation of the Report Parameter

Our final steps will consist of a couple of quick tests of the report, to ascertain that its parameter meets the specifications of the information consumers, as well as to give us the opportunity to gage the effectiveness of the displayed description we added in our last steps.

1.  Click the Preview tab to execute the RS022_MULTI_VAL_PARAM report.

The report executes, and appears as partially shown in Illustration 25.

Click for larger image

Illustration 25: Executed Report, Defaulted to "All" Parameter Selections (Partial View)

We see the report appear, executed by default with "all" parameter values selected. We can verify this, not only by our label, which lists the three Product Families as selected, but also by looking at what selections have been made within the parameter picklist.

2.  Click the dropdown selector button to the right of the Product Family(ies) parameter.

The picklist is exposed, as depicted in Illustration 26.

Click for larger image

Illustration 26: The New Parameter Picklist

3.  Uncheck the checkboxes for the Food and Non-Consumables families in the picklist, leaving only the Drink family selected, as shown in Illustration 27.

Click for larger image

Illustration 27: Specify Only the Drink Family ...

4.  Click the View Report button to refresh the report.

The report executes and returns data relating to the Drink Product Family only, as depicted in Illustration 28.


Illustration 28: The Report Filtered to the Drink Product Family ...

And so we see that our Report Parameter does, indeed, function correctly with multiple input values. Moreover, our dynamic title serves us well in displaying our parameter input, making it easy for any consumer to understand precisely what data is being displayed.

5.  Select File --> Save All from the main menu to save all work to the present.

6.  Select File --> Exit to exit MSSQL Server Business Intelligence Development Studio when ready.

We have corroborated the correct operation of the newly incorporated features. We present the results to the Operations consumers, who express satisfaction that the changes do, indeed, meet specifications.

Conclusion...

In this article, we extended previous examinations of report parameters to focus upon a commonly encountered business need: the capability to select multiple input values within a Report Parameter. We noted that this popular feature is supported in Reporting Services 2005. As a part of preparing the backdrop for a practice exercise surrounding the setup of multiple selection support within a Report Parameter, we got some hands on exposure to the migration of a Reporting Services 2000 report, with a simple Report Parameter in place, to Reporting Services 2005. The steps we took within the context of the sample Reporting Services 2000 report gave us a feel for what is involved in bringing the new capability to select multiple values to existing Reporting Services 2000 reports in our own environments. We emphasized that, with the introduction of multi-value parameter input in Reporting Services 2005, Reporting Services closes yet another of the few remaining gaps between itself and other, once dominant enterprise reporting solutions.

To set the stage for our discussion and practice session, we presented a business requirement based upon the needs of a hypothetical client, which we then addressed within a migration of the Reporting Services 2000 sample report to the current version of Reporting Services. We prepared for our practice session by creating a project within Reporting Services, and then created a "clone" report, within which we performed our exercises. We upgraded the report to Reporting Services 2005, and then removed the existing Report Parameter, together with a filter that referenced it, to allow for recreation of both with provision for the new multi-value input capabilities.

Once we had put in place the new Report Parameter with multiple value selection support, we added a textbox to the report containing an expression to display our parameter picklist selections on the face of the report. We introduced the Join() function and illustrated how to display the parameter value array in a comma-delimited string to meet the specifications of our hypothetical information consumers. Finally, we verified the operation of our enhancements in a couple of tests of report operation.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved