Home
IPhone SMS To Gmail
Consulting
Security Alerts
Blog
Software
Projects
Links
About
Contact
 Blog
 FriendFeed
 Facebook
 Flickr
 Twitter
 Toluu
 Last.fm
 Picasa
 LinkedIn
|
<< 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:
USE [MYDATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ParseMultiValueString] (@STRING nvarchar(max))
RETURNS @tempTable TABLE
(
MyValue nvarchar(255)
)
AS
BEGIN
WHILE (SELECT CHARINDEX(',', @STRING)) > 0
BEGIN
INSERT @tempTable SELECT LTRIM(RTRIM(SUBSTRING(@STRING, 1, CHARINDEX(',', @STRING)-1)))
SELECT @STRING = LTRIM(RTRIM(SUBSTRING(@STRING, CHARINDEX(',', @STRING)+1, LEN(@STRING))))
END
INSERT @tempTable SELECT LTRIM(RTRIM(@STRING))
RETURN
END
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.
Tags
SQL
Related Blogs
Attempted SQL Injection Attack
Restoring Specific Documents from a MOSS 2007 Content Database Backup
Deploying Reporting Services WebParts to SharePoint WSS and MOSS
Comments
Currently no comments.
Add A Comment
Name:
URL:
Email Address: (not public, used to send notifications on further comments)
Comments:
Please enter the text from the image:
|