How to create flexible parameter-driven queries
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 Here button.)
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.
As callout A in Listing 1 shows, 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
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.