I recently found myself going back to Old School techniques for SQL Server Reporting Services. A colleague of mine was working with a report using a user entered multi-select option as a filter for the report. I was surprised to find how reporting services translated the input into a query to retrieve the data from SQL Server.
The report had a data source based on a SQL Server query. It created a comma separated value list for the user choices. Then added an expression to the where clause stating something like the following:
Where ID IN (1,3,4,8,23)
Then the dynamic SQL was executed using sp_executesql. This was really wrong in so many ways. It was bad enough that it created a whole new query plan for every unique selection of options. However, the query also had another multi-select input exacerbating the number of unique queries that could be generated.
To get through the problem I had the client turn the query into a stored procedure with two string inputs. Then using old school conversion techniques, had him turn the csv inputs into tables which could be joined to the rest of the tables in the stored procedure, resulting in a single query plan regardless of the selections made by the user. There are any number of techniques you may find for creating an SQL function that translate a list of values in an comma-separated-values list into a table.
I am not sure what version of SSRS was being used for the report. It was at least SQL Server 2008 R2. Perhaps a newer version has a better solution.
I hope I am missing something. How about some input from our SSRS wizards? Is there a way to take advantage of table data types as input parameters? Certainly that would be a preferred method. Share your implementation in comments here, or by email to btaylor@sswug.org.
Cheers,
Ben