After changing a report Select statement into dynamic-sql I found “Select All” no longer worked for my “Customers” parameter.
To fix this, in Dataset/Properties/Parameters I changed the Parameter Value to
To translate … after the “.Value” there is … comma, double-quote, single-quote, comma, single-quote, double-quote, close-bracket. So that in the query each value would be surrounded by its own single quotes
A customer had many copies of the same report – one for each site. And the application database had multiple copies of each table – one for each site. The table names were all appended with the sites name EG: “WP London_SalesHeader”, “WP Barcelona_SalesHeader” etc.
In Report Manager there was a folder for each site, containing sub-folders for different categories of report (EG: /London/Sales, OR /London/Production).
This is a simplified account of how I created a Report that returned only information specific to its location.
In Report Builder I created a hidden parameter called @site of type Text with no “Available Values” and its “Default Values” using the global variable ReportFolder.
As the output from this built-in variable would be like “\Paris\Sales” I had to create an expression for the “Default Value” of @site searching through each site name in turn …
IIf(Globals!ReportFolder.Contains("Paris"),"WP Paris", "WP London"))
Finally, in the report query I derived the table name using the @site parameter.
declare @cmd varchar(max) =
from [Production].[dbo].[' + @site + '_SalesHeader]'
(NB: As a best-practice I displayed the value of @site, along with the other parameter choices, in the report sub-title.)