Multi-Select Parameters for Reporting Services

Question

Multi-Select Parameters for Reporting Services

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Adriaan Davel Mr or Mrs. 500 Points: 509 More actions · Answer 1

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...

Adriaan Davel Mr or Mrs. 500 Points: 509 More actions · Answer 2

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?

Adriaan Davel Mr or Mrs. 500 Points: 509 More actions · Answer 3

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.

Adriaan Davel Mr or Mrs. 500 Points: 509 More actions · Answer 4

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.

Adriaan Davel Mr or Mrs. 500 Points: 509 More actions · Answer 5

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.

Jim Parzych SSC Journeyman Points: 86 More actions · Answer 6

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

Jaishree Old Hand Points: 365 More actions · Answer 7

how can we write a query and pass multi value parameters to stored procedure in the report? To exec a procedure we simply write

Exec 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.

cerayg Grasshopper Points: 15 More actions · Answer 8

cerayg

Grasshopper

Points: 15

March 19, 2010 at 4:34 pm

#1137078

Beautiful...it worked great!

vze47pt5 Grasshopper Points: 19 More actions · Answer 9

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?