SSRS Multi Value Parameter. Check whether "Select All" is selected

Question

I have a multi value parameter in my SSRS Report. I want to find out whether (Select All) is checked in that parameter.

In other words, whether all the values in the parameter are checked or only some values are checked.

Is it possible?

I am able to find out number of selected values through Parameters!Parameter.Count. Is there a way to find out total of items in that parameter?

Ravenous

alternative approach: adding a specific "all" option https://mcmap.net/q/662222/-how-do-i-allow-a-drop-down-list-parameter-in-ssrs-to-have-a-default-value-of-quot-all-quot/10245 – Murdock

Answer 1

In case anyone is still having issues doing this, I just coded this easy fix.

=IIF(COUNTROWS("dataset").Equals(Parameters!parameter.Count),"it is equal","this is not equal")

Oriflamme

Answer 2

For the specific use-case of showing the selected filter on your report in a textbox, here's the expression that will show "All" if "(Select All)" is selected, otherwise it will show all the selected values as a comma-separated list:

=IIF(
     Parameters!YourMultivalueParam.Count = countrows("YourDataset"),
     "All",
     Join(Parameters!YourMultivalueParam.Label,", ")
 )

(split onto multiple lines for readability)

countrows reference: https://technet.microsoft.com/en-us/library/dd255215.aspx

---

Credit to other answers, just want to extend them for this common scenario.

Murdock

Answer 3

Your approach sounds good: I would make the options for the parameter come from a dataset.

Then you can use =COUNTROWS("DataSetName") to return the total number of options for your parameter and compare this with Parameters!*Parameter*.Count as you suggest.

Fricative

Where do you use that ? I tried as an hidden report parameter and as a dataset parameter : "Aggregate and lookup functions cannot be used in report parameter" – Androclinium

I use this in the body of the report, and change the heading text. It might change from saying "Selected Departments: Sales, R&D" to "Selected Departments: <All Departments>" or similar. – Fricative

Answer 4

I also faced this problem and I solved it this way.

I have one multivalued parameter named "Carrier". Then I have added one parameter "CarrierHidden" which is same as "Carrier" only thing is I made its Visibility as Hidden.

="Carrier=" & Switch(Parameters!CarrierHidden.Count = Parameters!Carrier.Count, "All",
 Parameters!Carrier.Count > 1 And Parameters!CarrierHidden.Count > Parameters!Carrier.Count, "Multi",
 Parameters!Carrier.Count = 1, Parameters!Carrier.Label(0))

Candlelight

I imagine that has a performance overhead, having to materialise the results, when compared to the other options. However it was the only way I could get a rowcount of the shared dataset which uses a parameter – Beau

Answer 5

The easy way will be to count the number of the selected parameters and compare them to the dataset

=IIF(Parameters!company_number.Count = CountRows("Dataset1"), True, False)

Allianora

Answer 6

The problem is if you're trying to pull something for another data set then cross referencing the row count in another dataset won't work. You will have to go with what the previous post states. Create an internal parameter of the exact type and assign the default value to the entire dataset. That way you have the max count of the rows since the hidden parameter.count = rowscount. That way you can use it within another dataset also provided that dataset is AFTER the first one is populated.

Weld

Welcome to Stackoverflow. Not sure if your answer is correct, but try to add a little formatting. Walls of text (I know this isn't really a wall) are generally discouraged. – Occiput

Answer 7

According to Microsoft's SSRS help search:

=Parameters!<ParameterName>.Count

Returns the integer value 1. For a single-value parameter, the count is always 1.

I verified this does indeed work, check the integer returned for the built-in parameter count field.

Allow multiple values on a parameter selection. Checking the value of the above field will let you know how many values the user actually chose.

In my situation, I allow multiple values on company number. This gives users the ability to choose one company to report on or several at once. Per client request, if they choose more than one, display data horizontally. If only one company is chosen in the parameter list, show the data vertically and hide the other tablix.

So my visibility show or hide expression looks like this in the one tablix:

=IIF(Parameters!company_number.Count > 1, True, False)

and like this in the other:

=IIF(Parameters!company_number.Count = 1,True,False)

Iolite

It is possible to detect if more than one parameter has been selected in case 'select all' is not used? – Progressionist

Answer 8

I used Count to display a single label or multiple selected parameters from a list

=iif(Parameters!parameter_list_name.Count=1,
Parameters!parameter_list_name.Label(0),
Join(Parameters!parameter_list_name.Label,", "))

Joung