<< Back To All Blogs
Using Multi-Value Parameters in SQL Server Reporting Services
Monday, August 10th, 2009
SQL Server Reporting Services are very powerful for presenting users with the data necessary for their reporting needs. The situation often arises in which a user wants their data in a list, checkable by item as a parameter as opposed to simply a substring filter for the report. This feature is very similar to how Excel provides a drop-down of value filters, in which you can check "Select All" or the specific item values you want to have present for each row.
This raises a more difficult situation because you need to then parse a string passed to the report, and find the items that apply to an array of values. The easiest way I have found to do exactly this is to create a table-valued function, and pass the comma-separated string to the function, and use a SQL IN statement to pull table rows from all contained values.
Here is the function that I have been using thus far to separate the values of the comma-separated string:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER FUNCTION [dbo].[ParseMultiValueString] (@STRING nvarchar(max))
RETURNS @tempTable TABLE
WHILE (SELECT CHARINDEX(',', @STRING)) > 0
INSERT @tempTable SELECT LTRIM(RTRIM(SUBSTRING(@STRING, 1, CHARINDEX(',', @STRING)-1)))
SELECT @STRING = LTRIM(RTRIM(SUBSTRING(@STRING, CHARINDEX(',', @STRING)+1, LEN(@STRING))))
INSERT @tempTable SELECT LTRIM(RTRIM(@STRING))
To use this function while checking in your table, you would do something like the following:
SELECT * FROM MyTable WHERE MyTable.MyField IN (SELECT MyValue FROM dbo.ParseMultiValueString(@MyParameter))
This is, of course, assuming that this were a Stored Procedure with a Parameter named @MyParameter.
Hopefully that will help some of you out along the way.
Reportin' Tom Out.
Attempted SQL Injection Attack
Restoring Specific Documents from a MOSS 2007 Content Database Backup
Deploying Reporting Services WebParts to SharePoint WSS and MOSS
Currently no comments.
Add A Comment
Email Address: (not public, used to send notifications on further comments)
Enter the text above, except for the 1st and last character: