Reporting Services – Using a Query Parameter for the IN Operator

Many times we use the IN operator with a list of values in the Where clause of a query. Of course, the problem is not with a static list; but how can we provide a dynamic list of values, specifically as a report parameter? A simple solution, where we would have the complete list in a variable, does not work. For example, this query that should retrieve rows for year 2003 and month numbers provided as a parameter from the vProductProfitability view in the AdventureWorksDW demo database would fail:

SELECT v.*
FROM vProductProfitability v
WHERE Year=2003 AND MonthNumberOfYear IN (@InList)

A possible solution is a dynamic query, i.e. query concatenated from T-SQL statements and report parameters as a string expression:

=”SELECT * FROM vProductProfitability

   WHERE Year=2003 AND

         MonthNumber IN (” + Parameters!Month.Value + “)”

More details on using dynamic queries in a report can be read in the Reporting Services Books OnLine, topic “Walkthrough – Using a Dynamic Query in a Report”.

Dynamic queries have their own problems, especially with security. You can read more in an excellent article “The Curse and Blessings of Dynamic SQL” by Erland Sommarskog. So I prefer the second solution – using a User-Defined Function (UDF) to split the provided string and to create a rowset of split values. Here is code for such a function:

— UDF to split the list
— Expects comma as delimiter
USE AdventureWorksDW
GO

IF OBJECT_ID(‘dbo.TsqlSplit’) IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO

CREATE FUNCTION dbo.TsqlSplit
(@List As varchar(8000))
RETURNS @Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @Item As varchar(8000), @Pos As int
WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos=CHARINDEX(‘,’,@List)
IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
SET @Item =  LTRIM(RTRIM(LEFT(@List,@Pos-1)))
IF @Item<>” INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(‘,’),8000)
END
RETURN
END
GO

/* Usage example */
SELECT t1.*
FROM TsqlSplit(‘11,2,333,4,5,66666’) AS t1

There are many different solutions for the task of splitting a string, from VB Split function to set-oriented T-SQL solutions. Check, for example, Itzik’s article “Separating Elements”, or attend some of his courses and seminars. Now, when we have the function, the next step is to write a query to populate a dataset in a report (I suppose a new report with the AdventireWorksDW data source). The query could simply have list for the IN clause replaced by a Select from the function, as shown in the Usage example for the function. But it turns out that we get much more efficient execution plan if we join the view to the rowset provided by the function:

SELECT v.*
FROM dbo.vProductProfitability v INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@InList)) As t
ON v.MonthNumberOfYear = t.Item
WHERE Year=2003

Note the @InList parameter in the query, or better said the @InList function parameter. Now we are more or less done. If you check report parameters, there should be already created a new parameter called InList, bound to the query parameter. Just change the prompt to something meaningful for end users, for example to “Comma delimited list of months:”. You might want to include a default value as well, to give an example to end users how to update the parameter, for example “1,2,3”. Place a table in the report body on the Layout screen, and insert couple of fields, including Year and Month in the detail row. Preview the report, and play with different input lists.