SSRS sorting based on parameters in a expression within a group

Question

I am trying to sort a group based on a parameter that the user can select; there a probably better ways of doing what I want, but just need to get it working for now.

The following gives me the error

Overload resolution failed because no accessible 'IIF' accepts this number of arguments

Expression I am trying to run:

 =IIF(Parameters!Sort.Value = "Parameter1", Fields!Field1.Value,
     IIF(Parameters!Sort.Value = "Parameter1", Fields!Field2.Value,
     IIF(Parameters!Sort.Value = "Parameter1", Fields!Field3.Value,
     IIF(Parameters!Sort.Value = "Parameter1", Fields!Field4.Value,
     IIF(Parameters!Sort.Value = "Parameter1", Fields!Field5.Value,
     IIF(Parameters!Sort.Value = "Parameter1", Fields!Field7.Value,
     IIF(Parameters!Sort.Value = "Parameter1", Fields!Field6.Value,
     IIF(Parameters!Sort.Value = "Parameter1", Fields!Field8.Value,
     IIF(Parameters!Sort.Value = "Parameter2", Fields!Field8.Value,
     IIF(Parameters!Sort.Value = "Parameter3", Fields!Field9.Value,
     IIF(Parameters!Sort.Value = "Parameter3", Fields!Field10.Value,
     IIF(Parameters!Sort.Value = "Parameter3", Fields!Field9.Value,
     IIF(Parameters!Sort.Value = "Parameter3", Fields!Field11.Value,
     IIF(Parameters!Sort.Value = "Parameter3", Fields!Field6.Value,
     IIF(Parameters!Sort.Value = "Parameter3", Fields!Field8.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field12.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field13.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field14.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field15.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field16.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field17.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field18.Value,
     IIF(Parameters!Sort.Value = "Parameter4", Fields!Field8.Value,
     IIF(Parameters!Sort.Value = "Parameter5", Fields!Field1.Value,
     IIF(Parameters!Sort.Value = "Parameter5", Fields!Field3.Value,
     IIF(Parameters!Sort.Value = "Parameter5", Fields!Field4.Value,
     IIF(Parameters!Sort.Value = "Parameter5", Fields!Field5.Value,
     IIF(Parameters!Sort.Value = "Parameter5", Fields!Field7.Value,
     IIF(Parameters!Sort.Value = "Parameter5", Fields!Field6.Value,
     IIF(Parameters!Sort.Value = "Parameter5", Fields!Field8.Value,
     ))))))))))))))))))))))))))))))

Does this mean that it just cannot be done in this way with a a lot of IFF statements or am I just missing something?

Better ways of doing this would also be greatly appreciated.

Instead of using loads of nested IIF's, The SWITCH function is much simpler and easier to read. — Alan Schofield
As @AlanSchofield, said, this would be much better written as a SWITCH statement. Intregral IF (IIF) statements evaluate every condition in the forumla, even if (as in this case) they don't get used. So every IIF in your statement gets evaluated every time. So just because the first condition returns TRUE, every other condition still gets evaluated. This is where a SWITCH statement shows its strength as it stops evaluating as soon as it finds a positive result. The SWITCH statement is also easier to read, write and debug. — Frank Ball

NickNick NickNick · Accepted Answer · 2018-10-19T11:48:27

Weird message.. but it looks like you forgot to put the "else" argument in the last IIF.

You have the ',' but then, no argument

SSRS sorting based on parameters in a expression within a group

1 Answers