Multi-Select Parameters for Reporting Services

  • Adriaan Davel

    Mr or Mrs. 500

    Points: 509

    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

    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

    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

    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

    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

    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: 335

    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

    Beautiful...it worked great!

  • vze47pt5

    Grasshopper

    Points: 19

    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?

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

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