3

I have a multi-valued parameter called Faculty on a report. I want to display the selection in a textbox at the top of the report. The obvious solution would be:

JOIN(Parameters!Faculty.Label,", ")

The problem that I am having is that the first option in the list (the default) is "All Faculty". This is distinct from the "Select All" checkbox that SSRS provides. We have created this hardcoded "All" option because it looks cleaner on the parameters screen (the user sees "All Faculty" instead of "Adam, Arbor, Altman..." etc.). If the user leaves this checked and makes any other selections, we assume that they meant to only select the other items and the stored procedure ignores the "All" selection.

So I would like to display something like this:

IIF(Parameters!Faculty.Value(0) = "0000000", [*display all selections except value 0*], (JOIN(Parameters!Faculty.Label,", "))

The section in square brackets is what I am having trouble with. The 0000000 value represents "All Faculty".

ಠ_ಠ
  • 3,060
  • 28
  • 43
user1596554
  • 31
  • 3
  • looks like it can go three ways 1) "All" only 2) "All" and any others (All is ignored) 3) others except all. for 2) you can join everything first, then cut "All, " from the result string using Substr or Remove – user1578107 Aug 14 '12 at 05:31
  • 1
    Thank you! That solved it. I ended up using substring. In case anyone else reading this needs the final code, here it is: IIF(Parameters!Faculty.Count < 6, IIF(Parameters!Faculty.Value(0) = "0000000", IIF(Parameters!Faculty.Count = 1, (JOIN(Parameters!Faculty.Label,"; ")), (JOIN(Parameters!Faculty.Label,"; ").Substring(13))), (JOIN(Parameters!Faculty.Label,"; "))), "Multiple") I added a check so that it will only display up to 5 at a time, otherwise it says "Multiple". I did Substring(13) because "All Faculty; " is 13 characters including spaces. – user1596554 Aug 14 '12 at 14:38
  • 1
    The solution above worked initially, but failed when the string was less than 14 characters (due to the IIF). You set me on the right track and I came up with a fairly elegant solution: Switch(Parameters!Faculty.Count > 5, "Multiple Selected", Parameters!Faculty.Count <= 5, Replace((JOIN(Parameters!Faculty.Label,"; ")),"All Faculty; ", ""))'code' – user1596554 Aug 14 '12 at 15:43
  • Hiya! Good to see you solved it. Don't forget to answer your own question and accept the answer, so others may benefit if they stumble upon your question! – Jeroen Aug 17 '12 at 20:53

1 Answers1

0

Answered by OP in comments to original question:

I ended up using substring. In case anyone else reading this needs the final code, here it is: 

IIF(Parameters!Faculty.Count < 6,
IIF(Parameters!Faculty.Value(0) = "0000000",
IIF(Parameters!Faculty.Count = 1, (JOIN(Parameters!Faculty.Label,";
")), (JOIN(Parameters!Faculty.Label,"; ").Substring(13))),
(JOIN(Parameters!Faculty.Label,"; "))), "Multiple")

I added a check so that it will only display up to 5 at a time, otherwise it says "Multiple". I did Substring(13) because "All Faculty; " is 13 characters including spaces.

The solution above worked initially, but failed when the string was less than 14 characters (due to the IIF). You set me on the right track and I came up with a fairly elegant solution:

Switch(Parameters!Faculty.Count > 5, "Multiple Selected",
Parameters!Faculty.Count <= 5,
Replace((JOIN(Parameters!Faculty.Label,"; ")),"All Faculty; ",
""))'code'
Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51