In my SSRS report, I can tell if a user selected all the choices in the drop down parameter by doing:
=IIF(COUNTROWS("SC_procRptLstBusinessUnit").Equals(Parameters!BusinessUnit.Count), "All ", (Join(Parameters!BusinessUnit.Label, ", ")))
Then in my report, I would show the word 'ALL' somewhere in the report instead of listing out all the individual choices.
Now, my question is:
How can I pass back to my Stored procedure, via a parameter, the letter 'A', so I know that the user chose all the choices from the drop down parameter ?
I tried passing back a parameter (@BusinessALL) to the SP but this code was not allowed in my parameter expression:
=IIF(COUNTROWS("SC_procRptLstBusinessUnit").Equals(Parameters!BusinessUnit.Count), "A", Nothing)
A aggregate or Lookup function are not allowed in a query parameter function.
- You have a report with a multiple select parameter where the user can select 1, more, or all values;
- If the user selects all values, you wish to display this somewhere on the report for the report reader's reference, instead of listing all the report values.
My standard workaround for this problem is to add an option "All" to the list of parameters, displayed at the top of the selection list. If I understand your information correctly your parameter list is populated by the procedure SC_proc_RptLstBusinessUnit
Once you have this available, you modify your main report procedure, starting it with an If statement:
Open in new window
This way you don't have to apply any logic in your report itself as to what parameters the user selected, it's all handled by the stored procedures. You can just display the selected choice.
I hope I understood your problem correctly. If not, please let me know so I can try and update my answer.