Reporting Services and Multivalued Parameters

Watching a product grow over the years is kind of like watching a child grow to maturity, adding skills and capabilities to his or her repertoire. I've been working with SQL Server Reporting Services since its infancy as a bouncing baby beta born into SQL Server 2000. At the time, it could hold its own with peers such as Crystal Reports, and my company was impressed enough with its capabilities that we adopted it with dreams of making it our de facto Web-based reporting solution—and we have. However, like many new products, Reporting Services was missing some features that we and others hoped would be added in a service pack or in a future version of the product— features such as client-side printing, Microsoft SharePoint Web Parts, ad hoc reporting, interactive sorting, and multi-valued parameters. All these are available in Reporting Services for SQL Server 2005.

Adding multivalued parameters to existing reports that use embedded queries is as simple as selecting one option in the parameters' properties. However, making reports that use stored procedures use multivalued parameters presents a challenge.The difficulty essentially boils down to the fact that a Reporting Services report uses an array to store a multivalued parameter's values, but T-SQL doesn't directly support arrays. If, for example, a stored procedure uses a field with an integer (int) data type, passing in a string array, such as '1,2,3,4,5', causes the stored procedure to fail with a data type conversion error.

Let's explore the problem and one possible solution. I'll demonstrate adding a multivalued parameter to a Product Line Sales report from AdventureWorks, the sample database that's included with SQL Server 2005, but instead of using the query embedded within the report, I'll use a stored procedure that successfully parses the comma-delimited string that the report produces.

Demonstrating the Problem

To demonstrate the problem of using multivalued parameters with stored procedures, let's open up the sample Product Line Sales report, which includes a multivalued parameter called ProductSubCategory.To load the report into the Business Intelligence Development Studio (BIDS), navigate to the sample reports folder (C:ProgramFilesMicrosoftSQLServer90SamplesReportingServicesReportSamplesAdventureWorksSampleReports) and open the report project, AdventureWorks Sample Reports.rptproj.

Before we investigate the query that uses the multivalued parameter ProductSubCategory, let's take a look at the parameter option that tells the report to use a multivalued parameter. With the report open and the Layout tab selected, click Report on the menu bar, then select Report Parameters. Next, click the ProductSubCategory parameter.As you can see in Figure 1, the parameter's Multi-value option is selected. This step is all that's required on the report side.

Now, if we take a look at the TopEmployees query on the Data tab, we can see that the @Product-SubCategory parameter is evaluated in the WHERE clause's IN statement:

WHERE (PC.ProductCategoryID =  @ProductCategory)  AND (PS.ProductSubCategoryID IN (@ProductSubCategory)) AND (SOH.OrderDate > @StartDate) AND (SOH.OrderDate < @EndDate) 

To use multivalued parameters effectively with Reporting Services reports, you need the IN statement in the driving query to allow comparison of multiple values.When we run the report by clicking the Preview tab, we see the four parameters Category, Subcategory, Start Date, and End Date. We can drop down the Subcategory parameter list to see the available values—in this case, different types of bikes. Because the Subcategory parameter is set up as a multivalued parameter, we can click one or more of the available parameter values—or all the values, as Figure 2 shows.

The report's multivalued parameter works fine when previewed with the SQL query that's embedded directly in the report.The Reporting Services data extension prepares the TopEmployees query along with the selected ProductSubCategory values and passes them to SQL Server, which successfully executes the query against the AdventureWorks database and returns data to the report. Notice that even though the multivalued dropdown box for the Subcategory parameter uses the ProductSubCategory description, the values that are passed in the query are the Product-SubCategoryID values, which are of the integer data type.

Many report designers choose to use stored procedures instead of embedded queries for their reports. That way, when they need to alter a query, they can do so in one stored procedure rather than in multiple reports.With that in mind, let's make a copy of the Product Line Sales report, calling it Product Line Sales SP, and turn the embedded TopEmployees query into a stored procedure. We'll call the stored procedure Test_MVP, and it will contain the same parameter names that the base report uses, so the report won't have to change. Listing 1 shows the Test_MVP stored procedure.

Notice in Listing 1 that the @Product-SubCategory parameter is an integer data type so that it coincides with the Product-SubCategoryID field, which is also defined as int in the ProductSubCategory table. All that's required to use the new stored procedure in our Product Line Sales SP report is to go to the Data tab in the report, change the command type to StoredProcedure, and replace the existing query with the name of the stored procedure, as Figure 3 shows.

Before previewing the report, it's a good idea to test the stored procedure.We can do that in a query window in SQL Server Management Studio. Open Management Studio, connect to your SQL Server 2005 instance, and select New Query from the toolbar. Drop down the database list and select the AdventureWorks database. First, let's test the stored procedure with one value for the ProductSubCategory, which is the second parameter. In the query window, type:

EXEC Test_MVP 1,2,'01/01/04',   '12/31/04' 

The execution completes successfully with the four parameter values, and data is returned for the case in which ProductCategoryID = 1 and ProductSubCategory = 2 for the date range that we specified, 01/01/04 through 12/31/04. As Figure 4 shows, the query returns the five rows that we expected (because our query selects the top five employees).

Now let's try the same stored procedure with multiple values for ProductSubCategory. This time, we'll use '2,3,4,5' as the value for the second parameter:

EXEC Test_MVP 1,'2,3,4,5',   '01/01/04','12/31/04'

If we pass multiple ProductSubCategoryID values to the stored procedure, Reporting Services converts the multivalued parameter into a string of values and we get the error message Msg 8114, Level 16, State 1, Procedure Test_MVP, Line 0 Error converting data type varchar to int. This error indicates that the parameter in the stored procedure is expecting an integer value for ProductSub-Category. Because we passed in a string— '2,3,4,5'—the execution fails.

To allow a string value for a multivalued parameter list coming from a Reporting Services report—in this case, the original Product Line Sales report, we need to alter the stored procedure so that the data type of @ProductSubCategory is variable-length character, or varchar, because we don't know precisely how long the string will be. We'll set a limit of 100 total characters, varchar(100), which will be an ample length for testing the @ProductSubCategory parameter. To alter the stored procedure, we modify the first and third lines of the code in Listing 1 so that the first five lines of code appear as follows:

ALTER PROCEDURE [DBO].[Test_MVP] @ProductCategory Int , @ProductSubCategory varchar(100), @StartDate datetime, @EndDate datetime 

When I executed the altered stored procedure to test it, I received a new error message. Predictably, previewing the Product Line Sales SP report displayed the same error, which Figure 5 shows. It was about this time that I almost gave up on multivalued parameters, but I know that there's typically a way around a stubborn child. I did some research and learned that I could create a user-defined function (UDF) to parse the comma-delimited list of values returned from the original Product Line Sales report and introduce them to the Product Line Sales SP report.

A UDF Does the Trick

You can successfully introduce a multivalued parameter list into a stored procedure in several ways. For example, you can store the SQL statement that drives a report in a variable, concatenate the parameter list to the variable, then execute the constructed statement through the built-in procedure sp_executesql. Another possibility is to load the parsed values of the comma-delimited list of individual parameters into a temporary table or table variable, then join the original query to this temporary table by using a statement such as

INNER JOIN #mytempPSC ON  ProductSubCategoryID =  #mytempPSC.ProductSubCategoryID 

The temporary table would contain only the user-selected list of values from the report and would therefore return the desired results.

Because I tend to shy away from anything that resembles dynamic SQL (as the first example does) and because I'd most likely need to put temporary table logic to parse the delimited string directly in the stored procedure, I chose to incorporate a UDF to store and execute the parsing logic. Using a UDF also lets me call the parsing logic from any stored procedure that requires string parsing.The type of UDF I chose to create is a table-valued function, which like a temporary table, stores values as rows in a table, essentially turning the string into the required array. Listing 2 shows the code for the UDF that I used, called fn_MVParam.

The function uses several of its own parameters to store and parse multivalued strings coming from the original Product Line Sales report's parameter.The function accepts two input values, a string value and a delimiter character, which by default is a comma (,). The delimiter parameter lets us extend the versatility of the function by giving us the ability to pass in any delimiter that we need.

The fn_MVParam function uses the CHARINDEX function to search for the existence of the delimiter. If it finds the delimiter, fn_MVParam knows that the string has multiple values. It then uses the LEFT function to find and store the first parameter value from the string. The fn_MVParam function gets all the characters up to but not including the delimiter and inserts this value into a table that has one column called Param.

Next, fn_MVParam uses the RIGHT function to remove the already extracted parameter in the string. For example, if we have a string value of '1,2,3,4,5', the first iteration of the WHILE loop extracts the 1, then sets the value of the string to '2,3,4,5'. The fn_MVParam function procedes until the LEN function determines that the length of the string is 0.

The end result is a row list of multiple parameter values that we can use in the WHERE clause of our stored procedure. We can test the execution of the fn_MVParam function in Management Studio by passing in a comma-delimited list, as Figure 6 shows.

Finally, we must alter the stored procedure that drives the Product Line Sales SP report one more time so that it uses the new fn_MVParam function.We use the function in the WHERE clause as a subquery to be evaluated for the ProductSubCategory field, as follows:

WHERE (PC.ProductCategoryID =   @ProductCategory) AND (PS.ProductSubCategoryID IN (SELECT Param FROM fn_MVParam (@ProductSubCategory,','))) AND (SOH.OrderDate > @StartDate)   AND (SOH.OrderDate < @EndDate) 

Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 47788) shows the final version of theTest_MVP stored procedure with the two changes we've made.

Figure 7 shows the Product Line Sales SP report successfully run by using the Test_MVP stored procedure and its fn_MVParam UDF. It's worth mentioning that the Product Line Sales and Product Line Sales SP reports use two Visual Basic .NET functions—Join() and Split()—to work with the multivalued parameter as an array.You can see the Join() function at work in Figure 7. It creates the report header information for product categories and subcategories based on the parameter selection.

Adding multivalued parameters as a Reporting Services feature was absolutely a great idea. Many report designers—those who didn't want to write their own parameter selection front end—were formerly relegated to an all-or-nothing approach when setting criteria for a report by using parameters. Multivalued parameters let us set exactly the criteria we want. For those of us who used stored procedures to develop the bulk of reports in SQL Server 2000 Reporting Services, string-parsing workarounds are available that let us migrate our reports to make use of multivalued parameters. Thankfully, SQL Server has UDFs that we can use to create a solution, not just a workaround, to using arrays in T-SQL.

Rodney Landrum ([email protected]) is director of research and development for Pro Business Systems, a software development company in Pensacola, Florida that produces a SQL Server?based application for the healthcare industry. He is an MCSE and a Citrix Certified Administrator.