DOWNLOAD THE CODE:
Download the Code 48596.zip

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:\ProgramFiles\MicrosoftSQLServer\90\Samples\ReportingServices\ReportSamples\AdventureWorksSampleReports) 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'
   Prev. page   [1] 2     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

We have definitely run into this issue on our current project. However, I don't believe the UDF presented is particularly efficient as it basically uses a WHILE loop to do cursor-like row-based processing of the array. Here is an alternative I borrowed from Erland Sommarskog's excellent article 'Arrays and Lists in SQL Server' (http://www.sommarskog.se/arrays-in-sql.html#overview). It uses a prepopulated Numbers table which is simply an integer column with values from 1-8000. In order to get a key for ordering the list, I originally added an IDENTITY column, which I replaced with the SQL Server 2005 ROW_NUMBER() OVER syntax.

CREATE FUNCTION dbo.fnParseDelimitedList (@list varchar(7998), @delimiter char(1)) RETURNS @ParsedValues TABLE (pk int, Value varchar(7997)) AS BEGIN INSERT INTO @ParsedValues (pk, Value) SELECT ROW_NUMBER() OVER(ORDER BY Number), SUBSTRING(@delimiter + @list + @delimiter, Number + 1, CHARINDEX(@delimiter, @delimiter + @list + @delimiter, Number + 1) - Number - 1) AS Value FROM dbo.Numbers WHERE Number <= LEN(@delimiter + @list + @delimiter) - 1 AND SUBSTRING(@delimiter + @list + @delimiter, Number, 1) = @delimiter RETURN END

Tim Overlund, MCDBA, MCSD.NET Database Practice Avanade, Inc

timoverlund@hotmail.com- December 23, 2005

Article Rating 3 out of 5

Another scenario I forgot to mention is when the parameter being passed is a GUID. Since guids are by definition a fixed length when part of a string consisting of an array of guids, there is an even more efficient parsing method credited by Sommarskog to Ken Henderson. Here is my implementation of the guid parser using nvarchar(MAX) to allow a basically unlimited number of GUID's:

CREATE FUNCTION dbo.fnParseGUIDs(@str nvarchar(MAX)) RETURNS @ParsedValues TABLE (pk int, GUID uniqueidentifier) AS BEGIN INSERT INTO @ParsedValues (pk, GUID) SELECT Number , CAST(LEFT(SUBSTRING(@str, 37 * (Number - 1) + 1, 37),36) AS uniqueidentifier) FROM Numbers WHERE Number <= DATALENGTH(@str) / (37 * 2) + CASE DATALENGTH(@str) % 37 WHEN 0 THEN 0 ELSE 1 END RETURN END

Happy Holidays,

Tim

timoverlund@hotmail.com- December 23, 2005

Article Rating 3 out of 5

 
 

ADS BY GOOGLE