NerdyHearn
Home
Blog

Contact
Mailing List

Software

Active Directory Products
Object Compare
Permission Compare

IPhone Products
Calls To Calendar
SMS To CSV
SMS To Gmail
Voicemail To Gmail

Sites
DocuTerminal
How Long For Me
My Music To Me
SaveMySerials
TypeCount

Blog
Twitter

NerdyHearn - Blog


<< 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
Deploying Reporting Services WebParts to SharePoint WSS and MOSS
Restoring Specific Documents from a MOSS 2007 Content Database Backup

Comments

Currently no comments.

Add A Comment

Name:


URL:


Email Address: (not public, used to send notifications on further comments)


Comments:



Enter the text above, except for the 1st and last character:


NerdyHearn - Latest tech news relating to C#, ASP.NET, SharePoint, PHP, general development, and more. DocuTerminal - Online Filing Cabinet solution. Scan, search and archive your paper documents. SaveMySerials - Protect yourself from theft, fire, natural disasters and more by recording your serial numbers My Music To Me - Stream your subsonic music collection to your Sonos wireless system TypeCount - Count how often you type across different computers! ServrTrackr - Monitor your SSL certificates, website uptime, and DNS configurations