Yeah, that too...

I recently hit a wall when I needed to display one of three fields in a textbox based on a parameter value.  Of course, I could write custom code and write a function to use the SELECT CASE function but it seemed over kill and unmanageable for my simple options.  What would seem trivial in the .net languages suddenly seemed difficult in reporting services.

Where the immediate IF function IIF allows a condition and two paths of execution, what if you needed the functionality of a SELECT CASE with n branches of execution?

The Choose() function allows multiple conditional statements.  In this scenario, I had a drop-down list of All, Labor and Non-labor.  When the user selected All, I wanted to use the AllExecution column in my table but if they selected Labor or Non-labor, then I wanted to use the LaborExecution and NonLaborExecution columns instead.

The syntax for these options was pretty straight forward:

=Choose(Index as Double, Choice as Object, ...)

So the expression for my textbox in my table was:

=Choose(Parameters!LaborType.Value, Fields!Amt.Value, Fields!laborAmt.Value, Fields!nonLaborAmt.Value)

A word of caution

The Choose() function is not zero-based (like every other index in the .net framework).  Therefore, my values for the drop-down list had to be 1, 2 and 3 instead of 0, 1 and 2.