Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Multi-Select Parameters for Reporting Services

By Adriaan Davel,

After my colleague brought some performance issues to my attention, we thought further investigation was required. After discovering Multi-Select Parameters for SSRS reports, we set out to add this to as many reports as possible, as it really is a cool feature.

We always use stored procedures to extract data for reports as a development standard, which brings a complication to Multi Select Parameters. When more than one option is selected in the parameter drop down the values are concatenated into one string, delimited with a comma. If you are using "SELECT" statements in your report to extract data this works well, as you can simply use an "IN" statement in your "WHERE" clause as it will get concatenated into the correct format. If you use a stored procedure however the concatenated string will be considered a single value, and no records will be returned.

After some searching we found some sample code for a SQL function that takes the concatenated string and splits it into a table, allowing a "JOIN" statement onto the values in the concatenated string. This works very well from a coding perspective, but unfortunately has major performance penalties. We tested this method compared to using a direct "IN" statement and the difference was very big. The "IN" approach completed execution in just over 10 seconds, where the computed table executed over 6 minutes. A heads-up on the "IN" statement, string selections do not have the enclosing single quotation marks and you will need something like:

IN (''' + RTRIM(REPLACE(CONVERT(NVARCHAR(4000), @MultiSelectParameter), ',', ''',''')) + ''')...

I then tried inserting the computed table into a "#TEMP" table before joining it to the main table; this put us to about 3 minutes of execution. Much better but not good enough if compared to 10 seconds. This puts us back to where we started. (We worked off a single table, with a fairly large number of rows, if joins are already being used to other tables the impact of the computed table might be less prominent)

This leaves one more option: the controversial dynamic SQL code.

We tried to avoid using sp_executesql (or its various derivations) as far as possible, but in this situation it seems to be the best option. Two reasons why we feel that dynamics SQL statements are acceptable here are:

  • The interface to the stored procedure does not allow for SQL injection text as only selections from a defined list is allowed
  • Recompiling of the statement has a negligible impact on the system as the report is run infrequently

Apart from the technical reasons for allowing dynamic SQL statements, the overall duration improvement really makes this a good option.

One limitation on the dynamics SQL method to consider is the maximum length of your SQL string. In our case the user could choose from a small range of options, each two characters long. This allowed us to use NVARCHAR(4000) for the SQL string, if you could possibly have a large number of options, or possibly lengthy options you will have to revert to a NTEXT data type, making the dynamics SQL a lot more complex and impractical.

So what we have ended up with is a stored procedure that builds a dynamic SQL "SELECT" statement and executes it in the best performing way we can find!

Happiness!

Total article views: 11464 | Views in the last 30 days: 9
 
Related Articles
FORUM

sp_executesql dynamic columns in select statement

sp_executesql dynamic columns in select statement

FORUM

select statement

select statement

SCRIPT

Dynamic Select Generator

Query to generate a dynamic Select statement from any table for moving data between servers.

FORUM

Dynamic Connection string

Setting Dynamic Connection string Of Flat File

FORUM

CASE statement versus dynamic Query

CASE statement versus dynamic Query

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones