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.
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.
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.
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
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.
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.
confirm our understanding of the expressed needs of the Operations
group, and consider the steps we will need to undertake to provide the
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.
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.
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.
a Report Server Project
begin, we will launch the new SQL Server Business Intelligence Development Studio.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
equivalent on my PC appears as depicted in Illustration 1.
Illustration 1: Launching
SQL Server Business Intelligence Development Studio
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
--> New from the main menu.
from the cascading menu, as shown in Illustration 2.
Selecting a New Project
Project dialog appears. Business Intelligence Projects appears in
the Project Types tree, indicating an installation of Reporting
Click Business Intelligence Projects in the Project Types tree, if necessary.
Server Project in the Templates list.
following into the Name box, leaving other settings at default:
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
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
Close the Start
page within the Visual Studio .NET 2005 interface to clear space in
which to work.
created a Reporting Services 2005 Report Project, we are
ready to proceed with creating the clone of a Reporting Services 2000 report.
a Copy of the Foodmart Sales Report
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.
the Reports folder underneath the Shared Data Sources folder, in
the Solution Explorer.
--> Existing Item ... from the cascading context menus
that appear, as depicted in Illustration 5.
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\
Using the Add Existing Item - RS022_MULTI_VAL_PARAM dialog, navigate to the location
of the sample reports in your own environment.
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.
the Foodmart Sales report inside the dialog.
from the context menu that appears, as depicted in Illustration 7.
Performing a Quick Copy of the FoodMart Sales Report
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
somewhere in the white space inside the RS022_MULTI_VAL_PARAM folder,
within the dialog.
from the context menu that appears, as shown in Illustration 8.
Illustration 8: Select
Paste within the New Folder ...
of the Foodmart Sales report appears within the dialog.
the new file.
from the context menu that appears.
following name in place of the highlighted existing name:
NOTE: Be sure to include the .rdl extension in the file name.
renamed copy of the Foodmart Sales sample report appears as depicted in
Illustration 9: The New
Report File, RS022_MULTI_VAL_PARAM.rdl
white space to the right of the file name, to accept the new name we have
new file by clicking it.
on the dialog box to add the new report to report project RS022_MULTI_VAL_PARAM.
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
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 ...
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.
the New Report to Reporting Services 2005 and Finalize Preparation
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.
in the Solution Explorer.
from the context menu that appears, as shown in Illustration 12.
Illustration 12: Opening
the New Report ...
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 Yes to
convert the report file to Reporting Services 2005.
RS022_MULTI_VAL_PARAM opens in Layout view.
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 ...
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
Click the Layout
tab to return to the Layout view.
--> 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
Parameters dialog, where we define parameters for the report, appears, as
shown in Illustration 16.
Illustration 16: The
Report Parameters Dialog
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
In the Parameters
list, on the left side of the dialog, click the single entry, ProductFamily,
to select it.
Click the Remove
button underneath the Parameters list.
parameter is removed from the list.
Click OK to
accept our removal of the parameter.
now empty Report Parameters dialog closes, and we are returned to the Layout
view of the report.
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.
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
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.)
from the context menu that appears, as shown in Illustration 18.
Accessing the Matrix Properties
Properties dialog opens, defaulted to the General tab.
Click the Filters
note that, within the top row of the Filter list, we have the expression
detailed in Table 1.
Table 1: Filter
Expression for Parameter with Single Value Input
Click the Value
field of the single occupied row to select it.
Click the Delete
button to delete the reference to the parameter, as indicated in Illustration
Illustration 19: Select
and Delete the Parameter Reference
reference to the now-deleted parameter is itself deleted.
Click OK to
accept our changes and to close the Matrix Properties dialog.
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
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
Create a New Report
Parameter with Multiple Value Selection Support
--> Report Parameters from the main menu atop the Report
Designer, as we did earlier.
Report Parameters dialog appears.
following into the Name box atop the dialog:
following into the Prompt box :
insert a check mark) the checkbox to the immediate left of the Multi-value,
one of five checkboxes appearing under the Prompt box.
checkbox, a new option with Reporting Services 2005, enables the support
of multiple selections in the picklist for our new Report Parameter.
the Allow null value and Allow blank value check boxes are
Click the From
query radio button in the Available values section of the dialog.
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
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
Label field: the Dataset field from
which the values that appear to the consumer in the picklist at runtime are
in the Dataset selector.
in the Value field selector.
in the Label field selector.
Click the From
query radio button in the Default values section of the dialog.
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.
in the Dataset selector, as before.
the Value field selector, again as before.
Parameters dialog appears, with our input, as depicted in Illustration 20.
Illustration 20: The
Report Parameters Dialog
to accept our input and to close the Report Parameters dialog.
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.
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.
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.
The syntax for Join() is as follows:
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.
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.
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.
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