SQLServerCentral / Discuss Content Posted by Adriaan Davel / Article Discussions / Article Discussions by Author / Multi-Select Parameters for Reporting Services / Latest Posts

RE: Multi-Select Parameters for Reporting Services

vze47pt5 — Fri, 17 Sep 2010 11:54:17 GMT

I'm running against Oracle. My query works fine with these small chages to Oracle syntax:WHERE (:CommaDelimtedList || ',' LIKE '%' || TableColumnToCompareTo || ',%' OR @CommaDelimtedList = '')However when I run the query in Reporting Services 2005 Sp2, if I choos more than one item in the multiselect, I get the error"ORA-00920 Invalid relational operator" Any idea what is happening?

RE: Multi-Select Parameters for Reporting Services

cerayg — Fri, 19 Mar 2010 16:34:11 GMT

Beautiful...it worked great!

RE: Multi-Select Parameters for Reporting Services

Jaishree — Mon, 22 Oct 2007 11:44:45 GMT

how can we write a query and pass multi value parameters to stored procedure in the report? To exec a procedure we simply writeExec MyStoredProcedure to exec a stored procedure we need to pass parameters. I can do Exec MyStoredProcedure IN (@ParamList)I have gone through this thread conversations however, I am not able to make out when to use Split function and how to use.In dataset, which query I need to write to exec a stored procedure with multi value parameters.

RE: Multi-Select Parameters for Reporting Services

Jim Parzych — Mon, 20 Aug 2007 05:51:00 GMT

This is only for reports that run occasionally. most users select 1 to 3 clients, so it is not high on our list at this time to optimize this

RE: Multi-Select Parameters for Reporting Services

Adriaan Davel — Sun, 19 Aug 2007 23:44:00 GMT

Hi Philippe,

Thanks for the post. This was the one option we considered but did not test as we were running on a SQL2000 database and I had never worked with CLR on SQL2000. I would imagine that this would be a good solution as C\C++\C# is normally better as string manipulation than SQL.

RE: Multi-Select Parameters for Reporting Services

Adriaan Davel — Sun, 19 Aug 2007 23:40:00 GMT

Hi sebastian,

I don't normally use indexes on temp tables as building the index is often more costly than not using it. In this case we did test it with, and without indexes and the overall performance was better without the index.

My rule of thumb for indexes is to only create then if they will be used more than once, then the cost of building the index will be less than scanning the table. With temp tables this is mostly true.

RE: Multi-Select Parameters for Reporting Services

Adriaan Davel — Sun, 19 Aug 2007 23:37:00 GMT

Hi dph,

Have you done performance testing on this with large tables? I try to avoid "LIKE" statements as much as possible as they have a tendancy to cause table scans. I would be interested to see your performance testing results.

RE: Multi-Select Parameters for Reporting Services

Adriaan Davel — Sun, 19 Aug 2007 23:33:00 GMT

Hi Jim,

Your approach is the same as what we did, and found it to be highly inefficient, have you done some perfonace testing on big tables and wide range of parameter selections?

RE: Multi-Select Parameters for Reporting Services

Adriaan Davel — Sun, 19 Aug 2007 23:31:00 GMT

Hi Jon,

Interesting idea. Have you tested this against large tables? This would result in a table scan which might have a big impact on performance...

RE: Multi-Select Parameters for Reporting Services

Adriaan Davel — Sun, 19 Aug 2007 23:27:00 GMT

Hi SDM,

It could well be that my results were affected by our table size and parameter length as it was a fairly large table and many parameter options (strings) selected. I'm sure a smaller table and less parameters would have different results. We decided to stick with the dynamics SQL as to advantages out weigh the disadvantages.

In the query plan computations stood out most (as expected) and the join was very in-efficient, which is what I expected as well.

RE: Multi-Select Parameters for Reporting Services

Adriaan Davel — Sun, 19 Aug 2007 23:14:00 GMT

Hi Jose,

We tried to split the string into records, and insert it into a temp table yes, and it had some perfomance boost but the cost of splitting the string and inserting into a temp table cause performance to be less than using dynamic sql...

RE: Multi-Select Parameters for Reporting Services

Wapper — Wed, 15 Aug 2007 06:33:00 GMT

If you want to avoid dynamic SQL, you could use an UDF that will convert comma-delimited string to a table-type variable, and then use IN as usual.

SELECT * FROM customers

WHERE ISNULL(@cust_id_multiple, '') = '' OR customers.cust_id IN (SELECT value FROM dbo.LIST_TO_TABLE(@cust_id_multiple))

Below is a sample UDF that does the trick.

--converts list of values to a temporary table. Used to avoid dynamic SQL statements when IN clause is used as filter expression--sList: list of numeric values in comma-separated form, to be converted to table --return value: table-type variable containing one column. The table is filled with numeric values from @sListCREATE FUNCTION LIST_TO_TABLE (@sList AS VARCHAR(255))RETURNS @retTable TABLE(value VARCHAR(50)) AS

BEGIN

IF @sList IS NULL RETURN

--preparing the input list, removing IN keyword and parenthesis SET @sList = REPLACE(@sList, 'IN(','') SET @sList = REPLACE(@sList, 'IN (','') SET @sList = REPLACE(@sList, '(','') SET @sList = REPLACE(@sList, ')','') SET @sList = LTRIM(RTRIM(@sList))

--scrolling thru values in @sList and populating temporary table DECLARE @Index INT, @Delimiter CHAR(1) DECLARE @Result VARCHAR(255)

SET @Delimiter = ','

WHILE @sList <> '' BEGIN SET @Index = CHARINDEX(@Delimiter, @sList) IF @Index <> 0 BEGIN SET @Result = LEFT(@sList, @Index - 1) SET @sList = SUBSTRING(@sList, @Index + 1, LEN(@sList)) END ELSE BEGIN SET @Result = @sList SET @sList = '' END

INSERT @retTable SELECT @Result END

RETURN

END

RE: Multi-Select Parameters for Reporting Services

Luke-458024 — Tue, 14 Aug 2007 07:18:00 GMT

If you want to use an IN try this?

WHERE RealTableColumn IN (SELECT TempTableColumn FROM TempTable)

EXISTS can be fast too but not sure in this scenario.

WHERE EXISTS (SELECT TempTableColumn FROM TempTable WHERE TempTableColumn = RealTableColumn)

However a JOIN on an indexed TempTableField should be as fast as anything in my experience.

I would normally use the IN option as it is clearer.

RE: Multi-Select Parameters for Reporting Services

Philippe Cand — Mon, 13 Aug 2007 11:12:00 GMT

Hello,I have been using all these solutions and finally settled for a CLR function that I found on the Internet.It works great for me.Regards,Philippeusing System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, TableDefinition = "SQLStr nvarchar(4000)", FillRowMethodName = "FillSplitCommaDelimitedStringToStr")] public static System.Collections.IEnumerable clrfn_SplitCommaDelimitedString(SqlString str) { string x = str.Value; if (!string.IsNullOrEmpty(x)) { return x.Split(','); } else { return null; } } private static void FillSplitCommaDelimitedStringToStr(object obj, out SqlString str) { if (obj != null) str = (String)(obj); else str = String.Empty; }};''I call it like thatWhere (@PTI2 = '##' or b.PTI2_Cd in (select ltrim(SQLstr) from ONGlobals.dbo.clrfn_SplitCommaDelimitedString(@Pti2) ) )''The ## is used for cases where we want all values.

RE: Multi-Select Parameters for Reporting Services

sebastian rodriguez — Mon, 13 Aug 2007 08:18:00 GMT

Adriaan,

When you create the temp table, was included a index for the join column ?, the performance between a table without index vs an indexes table in this case can be very big.

Example: The customer table have the next structure:

create table customer ( id int primary key, state int, LName varchar(100), rname varchar(100))

and supose that table have an index on the state column create index IDX_Customer_State on Customer (state)

so, we temp table have the next code

declare @TempJoinTable (IdState int primary key) //note the primary key, we can index a @temptable like anyone

the next step is to populate the temp table with this code

-- the @FilterValue contain the filter values with comma

DECLARE @posicion INTDECLATE @Value VARCHAR(100)

WHILE (Len(@FilterValue))BEGIN

SET @Posicion = PATINDEX('%,%',@FilterValue) IF (@Posicion = 0) BEGIN SET @Value = @FilterValue SET @FilterValue = '' END ELSE BEGIN SET @Value = SUBSTRING(@FilterValue,1,@Posicion-1) SET @FilterValue = SUBSTRING(@FilerValue,@Posicion+1,LEN(@FilterValue) END

INSERT INTO @TempJoinTable VALUES (@Value)

END

finally, just join the tables and compare the execution time

SELECT *FROM customer CUS INNER JOIN @TempJoinTable TT ON CUS.State = TT.IdState

RE: Multi-Select Parameters for Reporting Services

dph-446223 — Mon, 13 Aug 2007 07:55:00 GMT

We faced this problem a while back at our company as well. And one of our DBAs came up with the following solution:WHERE (@CommaDelimtedList + ',' LIKE '%'+ TableColumnToCompareTo + ',%' OR @CommaDelimtedList = '')Just thought I'd share with everyone how we handle this situation.

RE: Multi-Select Parameters for Reporting Services

Jim Parzych — Mon, 13 Aug 2007 06:46:00 GMT

i approach it a little differently. I use a function to split a parameter value into its different piecesI also provide an All option with -1 being the value passed to the stored procedure.my stored procedure has this line:AND (s.ORDERCODE IN (SELECT Item FROM dbo.Split(@OrderCode,',')) OR '-1' IN(@OrderCode))and function Split looks like this:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[Split]( @ItemList NVARCHAR(4000), @delimiter CHAR(1))RETURNS @IDTable TABLE (Item VARCHAR(50)) AS BEGIN DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ''' ''', '') SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) INSERT INTO @IDTable(Item) VALUES(@Item) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END RETURNEND

RE: Multi-Select Parameters for Reporting Services

Justin D Loranger — Mon, 13 Aug 2007 06:27:00 GMT

I would love to see a code example from Adriaan's article as well.

RE: Multi-Select Parameters for Reporting Services

Ronald Green — Mon, 13 Aug 2007 03:53:00 GMT

If you use parameterized dynamic sql in the stored procedure, it shouldn't differ from the query within the report. What I'd try is dump the SSRS parameters, do it with my own code, and pass an xml full of parameters (sql 2005).

RE: Multi-Select Parameters for Reporting Services

Jon Spink — Mon, 13 Aug 2007 03:52:00 GMT

no need for dynamic SQL, just use something like this:

WHERE charindex(',' + field1 + ',',',' + @param1 + ',') > 0

... where @param1 is a string of comma-delimited values.

RE: Multi-Select Parameters for Reporting Services

Nick Thompson-356919 — Mon, 13 Aug 2007 03:12:00 GMT

Thanks for the post Adriaan. It would be great if you could paste some sample code here!

Nick

RE: Multi-Select Parameters for Reporting Services

SDM — Mon, 13 Aug 2007 02:00:00 GMT

Adriaan,

I'm rather surprised at the blow-out to ten minutes for that basic shred-the-tokenised-string-to-a-table approach. I've done this several times as a way of dealing with SSRS multi-select, and found it fine for performance (although really it's a bit crude and hack-y).

Was there anything obvious in the query plan (of the porly performing query) to indicate why this was such a bad thing to do?

RE: Multi-Select Parameters for Reporting Services

jose antonio segurado — Mon, 13 Aug 2007 01:36:00 GMT

Have you tried to have it stored in a table with that function of yours and do a select statement but instead of join using @parameter in (select parameter from @table) to see if that boost the performance?And could you publish de code of said function?Thanks in advance

Multi-Select Parameters for Reporting Services

Adriaan Davel — Tue, 31 Jul 2007 12:01:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/ADavel/3138.asp