DBAs and administrators who use SQL Server Reporting Services (SSRS) can sometimes run into a difficult situation: Users want to be able to choose more than one option in a report's pick list, but the report uses a parameter-driven query. In Hitchhiker's Guide to SQL Server 2000 Reporting Services (Addison-Wesley Professional, 2004), Peter Blackburn and I discuss how to cobble together a solution, but there have been a number of improvements to address this problem since that book was published. For example, SSRS's Report Processor has added support for this scenario. So, it's time to walk through an example of how to manage multi-select parameters using this new support.
For this demonstration, I created a new business intelligence (BI) project with Visual Studio 2008 SP1. (Without SP1, this example doesn't work because SP1 re-enables the BI functionality that was last supported in Visual Studio 2005.) You can also use the SSRS 2008 R2 upgrade to Visual Studio 2008. I won't bore you with step-by-step instructions on how to set up the BI project so that we can concentrate on how to build the report project.
To begin, you need to create a new BI Report Project, create a shared data source named dsAdventureWorks against the dsAdventureWorks2008 sample database, and add a report to the project. You can add this report with or without the Report Wizard. If you add this report with the Report Wizard, be aware that it contains a bug that prevents you from naming the dataset it creates. The problem presents itself if you have the temerity to change the dataset name using the Report Data window. If you encounter a problem like this, you'll have to manually edit the .rdl file to rename the dataset it references.
After you add the report, you need to:
- Build a parameter-driven query that creates the initial dataset.
- Build a query to populate the pick list.
- Configure the parameter for the parameter-driven query.
- Test the report.
(You can download the completed project files by going to the top of this page and click the Download the Code icon.)
Building the Parameter-Driven Query
To create the initial dataset, you can use the query shown in Listing 1. This query returns a set of rows from the AdventureWorks2008 Production.Products table along with the associated product photos in related tables. It returns rows based on a single parameter: the product's color, which will be presented to users in a drop-down list of acceptable (i.e., known) colors, including NULL.
SELECT Production.Product.Name, Production.Product.ProductNumber,
Production.Product.Color, Production.Product.ListPrice,
Production.Product.Size, Production.Product.Weight,
Production.Product.ProductLine, Production.Product.Class,
Production.Product.Style, Production.ProductPhoto.ThumbNailPhoto,
Production.ProductPhoto.LargePhoto
FROM Production.Product INNER JOIN Production.ProductProductPhoto
ON Production.Product.ProductID =
Production.ProductProductPhoto.ProductID
INNER JOIN Production.ProductPhoto
ON Production.ProductProductPhoto.ProductPhotoID =
Production.ProductPhoto.ProductPhotoID
--BEGIN CALLOUT A
WHERE (Production.Product.Color IN (@ColorWanted))
--END CALLOUT A
As callout A in Listing 1 the Products table query uses an IN clause that's coded to accept a parameter. This isn't going to be kosher as far as the T-SQL compiler is concerned because an IN clause can't contain a parameter. But don't worry—the Report Processor deals with this issue when the report is interpreted and rendered. How is this done? Well, the Report Processor cheats. It substitutes a generated IN expression (which contains a delimited list) into the query on each execution instead of passing the parameter as such. Unfortunately, it means that if you try to reference the parameter elsewhere in the query (e.g., when checking to see if a specific value is chosen), the delimited string is inserted. This severely limits the use of the parameter for other purposes.
The multi-select parameter is actually handled behind the scenes as a Value array. Each selected parameter value is added to this array. The first parameter in the ColorWanted parameters collection array is referenced by
=Parameters!ColorWanted.Value(0))
This permits you to reference each of the selected values individually. Thankfully, you don't have to leverage the parameter to provide a Select All option, as the Report Processor does this for you.
After the parameter-driven query executes, the results are exposed as a dataset (dsProductsByColor) in the Report Data window. As Figure 1 shows, each column is visible and ready to be dragged into a report.
Building the Pick-List Query
To populate the pick list, you need to build another query that adds a second dataset (dsPickLists) to the report. The query needs to populate the pick list in such a way that the user can choose one or more colors from the list. It boggles the mind to think of the options here. One of the more interesting ways to deal with lookup tables is to use shared datasets, as implemented in SSRS 2008 R2. In this example, though, I'll keep it simple and create an additional dataset that queries the host database using dsAdventureWorks with a DISTINCT query to return the available colors. To add this dataset, follow these steps:
- Right-click the data source name (in this case, dsAdventureWorks) in the Report Data window. Choose Add Dataset to bring up the Dataset Properties dialog box.
- Name the new dataset, choose its data source (or create a new one), and set the query type, as Figure 2 shows. You specify the type of query in case you need to use a stored procedure to run it.
- Enter the query. If you want to test it, click Query Designer, which takes you to its Edit As Text dialog box, as Figure 3 shows. Click the red exclamation mark to test the code. You can also use the Edit As Text dialog box to enter the query.
- Click OK. You'll be taken back to the Report Data window. Your new dataset will be appended to the list of datasets.
This configuration is a simple one. You can use the Dataset Properties dialog box for more complex configurations by defining parameters, fields, and options for the query as well as setting filters for its result set.
Configuring the Parameter for the Products Table
At this point, you need to configure the parameter for the Products table query you built earlier. Click the Parameters folder in the Report Data window, then right-click the @ColorWanted parameter to expose the Report Parameter Properties dialog box shown in Figure 4. Here you can set the text that prompts the user for a value as well as configure how the parameter is to be managed. In this case, you can accept multiple values but not a blank value (i.e., an empty string). Note that a multi-select parameter can't be configured to accept a NULL value, which is why the pick-list query filters out NULL values. The parameter should be visible.
All of the parameter configurations you just made are recorded in the report's .rdl file. Now you need to modify the .rdl file to display a list of valid color choices. In this case, you want the pick-list query to provide the valid values. To do so, click Acceptable Values in the Report Parameter Properties dialog box to bring up the dialog box shown in Figure 5. Select the Get values from a query option, then select dsPickLists from the drop-down list of available datasets. Finally, choose Color from the drop-down lists for the Value and Label fields.
If desired, you can set default values. If you don't provide a default value, users must specify a value, which is good. You don't necessarily want the system to run an open-ended query if it doesn't make sense to specify a default value (e.g., a default value might hinder rather than help the user).
To set a default value, click Default Values in the Report Parameter Properties dialog box to get to the dialog box shown in Figure 6. Specify the value that's to be used by default. In this case, I chose "Black" to illustrate how to set a default value.
Testing the Report
After you're done configuring the report, it's time for testing. Don't click the green arrow key or press F5 to test the report—you do that only when you're ready to deploy the report. Instead, click the Preview tab in the Report Designer. This starts the Report Processor, which interprets the .rdl file and renders the report in Visual Studio.
Since I set a default parameter value, the Report Processor uses that value when it runs the query and generates the report. If no default is set, the report processor prompts you for a parameter before rendering the report. If you set a default parameter value but want to test the report using different values, you can click the Enter Product Color wanted drop-down list, which will provide the pick list of possible colors as well as a Select All option, as Figure 7 shows.
Creating Flexible Yet Well-Performing Queries
Although it might seem like a lot of steps, it's not that hard to get a multi-select parameter-based query to work. The method I just described works for stored procedures as well. Most users appreciate the flexibility of multi-select parameters.
When creating multi-select parameter-based queries, keep in mind that overall report performance is a function of generating reports that properly focus the report on needed information—not necessarily on information brought up by an improperly configured default parameter. More data means slower performance. Also keep in mind that, in some cases, using parameters to feed filter expressions can dramatically improve performance, as it might mean that the query need not be re-executed.
