Lucky's (10/4/2012)
Hi,I have a multi value parameter in SSRS report which has a list of values around 600..When the user selects 'SELECT ALL',its passing the long list of values
to sql procedure and impacting performance of report..its getting timed out even..we
used 'ALL' option ( which passes a string 'ALL' instead of passing all values)
Firstly use SQL Server Profiler to get the exact query with the parameters values been send to database engine. For this you have to run the profiler on relational database engine and then execute the report.
Check this query. Then run this query from SSMS to check the performance.
but the user requirement is like..
sometimes they need to select 590 values out of 600 values..which will not work with 'ALL'
Can any one have a better way to resolve this with better performance which works with SSRS 'Select ALL'.
How are you using this multi-value parameter in your query ?
If you are using 'IN' operator, replace it with a inner join to a temp table containing all the parameter values.
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI