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:
=IIF(Parameters!IncludeUsers.Count>1,”MultiValue”,
Parameters!IncludeUsers.Value(0))
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.
