Yeah, that too...

Almost two years ago today, I posted a journal entry, Listing All Values from a Multi-value Parameter in a Header or Footer, which detailed how to use the JOIN function to list out all of the items that a user had selected so that when the report was printed or exported, they would know which values had been selected.

I recently had a situation where the users of my application were selecting all of the items in the list, filling up and overflowing the footer section of the report, producing an on-screen error.

You can prevent this by creating a custom expression in the textbox that contains the list of values that the user has selected.

=IIF(Parameters!EE.Count = Count(Fields!expEleCd.Value, "All Expense Elements"), "All", IIF(Join(Parameters!EE.Value, ", ").Length > 60, LEFT(Join(Parameters!EE.Value, ", "), 60) & "..." , Join(Parameters!EE.Value, ", ")))

The expression uses nested immediate IF functions (IIF) and the COUNT function to compare the number of available values to the number of values actually selected.

If the user selected all of the available values of the parameter, then "All Expense Elements" is returned.

If not, then the second immediate IF checks to see if the string of values created by the JOIN function is longer than sixty characters.  If true, then the list is shortened and an ellipsis (…) is appended to the end of the string.  If it isn't longer than sixty characters, it returns the values that the user selected separated by commas.