Multi-Select Parameters for Reporting Services

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

  • 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?

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

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

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

  • 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

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

  • worked great!

  • 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 23 (of 23 total)

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