SQLPam's Blog

April 27, 2011

Using Multi-Value Parameters in SSRS Datasets

Filed under: SSRS — sqlpam @ 1:14 pm

I was given a simple assignment to generate a Usage Summary report for the reports in SSRS.  To achieve the required results took very little time.  However, I decided to allow the user the ability to select which report user(s) could be displayed on the report.  Thanks to a recent presentation by Mike Davis, I was able to implement this with minimal effort.  My one gotcha was that I neglected to place parenthesis around the parameter – so it was a minor blonde moment.

As I played with the report a couple of days later, it struck me that instead of having to wade thru all the users of a report, it would be helpful to see all users grouped.  Yes – a grouping on the report would have achieved this – but the individual users would still be there.  I had the idea to present the option of All Users in the user selection list.  If this option was selected – by itself – it meant the user wants to see the summary of all users per report. 

My dataset is embedded in the report – not best practice but is the easiest under current circumstances.  This meant that to determine is a user was in the selected list all I had to code was:

UserName IN (@UserList)

The issue came in when I test in my query if the only selected user was All Users.  If there was only one selection from the list a simple test of @UserList = ‘  All Users  ‘ would work.  If more than one value was selected, I started getting errors.

I got around this issue by creating a new internal parameter.  I set this parameter to ‘MultiValue’ if more than one selection was made.  If only one selection was made, I set the new parameter to the selected item.  The default function for the new parameter looks like:


I now had a parameter I could test against to render the results I wanted.  If the new parameter was the All Users option, I set the User Name to All Users instead of the actual user name.  For readability, I did the initial query as a CTE and then grouped on the information in the CTE.  I had not thought to use characteristics of parameters as internal parameters until this need arose.  I am now considering where this might be helpful in other situations.


Blog at WordPress.com.