SQL Server Reporting Services (SSRS) – Determine if all values are selected in a multi-value parameter

=iif(countrows(“dsCarMake”) = Parameters!CarMake.Count, “DO SOMETHING”, “DO SOMETHING ELSE”)

Explanation:

I have a parameter called CarMake. I load this parameter from a dataset called dsCarMake. The parameter is set to allow multiple values.

Let’s say a total of fifty Car Make’s come back from my dataset. When I run the report and click the dropdown box of my parameter, it shows all fifty Car Makes.

After running the report, I want to display what the user selected in the dropdown and display it in a textbox for context purposes. The expression I can use to show all Car Make’s the user selected, comma delimited, would be:

=join(Parameters!CarMake.Value, “,”)

But what if the user selected all fifty? That is a bit much to show in the textbox. Instead, I wish to show the word ‘ALL’. The expression then would be this:

=iif(countrows(“dsCarMake”) = Parameters!CarMake.Count, “ALL”, join(Parameters!CarMake.Value, “,”))

What this expression states:

If the row count from my dsCarMake dataset (50) is equal to the number of Car Make’s the user selected (assumming they selected all 50)
then show the word ‘ALL’ in the textbox
else join all the values that were selected, comma delimited.

****************

Two ways to count rows retrieved from dataset:

1) countrows(“dsCarMake”)
2) Count(Fields!make.Value, “dsCarMake”)

Count values selected in multi-select parameter:

Parameters!CarMake.Count

Get value in single-select parameter that contains a count of values selected from a multi-select parameter.

Parameters!TotalCarMakeSelected.Value

Leave a Reply