The CHOOSE() Function in SSRS
BOL’s description of the CHOOSE() Function: “Selects and returns a value from a list or arguments”. The syntax of the function is as follows:
=CHOOSE(index, expression_1 [, expression_2] [, expression_3] [, expression_4] …[, expression_n])
So, the Choose() function uses an Index (of type Double) to ‘choose’ one of a list of supplied values/expressions. For example;
=CHOOSE(Index, “Red”, “Yellow”, “Green”, “Grey”, “Black”, “White”)
When Index = 3, then the result is “Green”. When Index = 5, then the result is “Black”
Let’s look at a practical example:
I have a report (a simple chart at this stage) that relies on any one of a number of values to decide which metric to display – these values are provided to the user via a parameterized drop down list in the report, they simply choose which metric they want and the appropriate data is displayed. In the chart, I use an expression to decide on which metric is to be used based on the value returned by the parameter. Previously, I would have used an IIF() statement to do this, although I now have 7 values and this makes the IIF() statement far too ‘involved’.
Figure 1. shows the Metrics parameter and some of the values used:
Fig 1. Setting the Metrics Parameter Values
The expression that is used in the chart (to get the data) is as follows:
=CHOOSE(Parameters!Metrics.Value, Fields!TotalItems.Value, Fields!OpenItems.Value, Fields!ClosedItems.Value, Fields!AveragePrice.Value, Fields!GrossProfit.Value)
Fig 2. Setting the Expression for the Data Values
When the user selects Total Items from the Parameter drop-down, a value of ‘1’ is passed to the CHOOSE() function which in turn selects Fields!TotalItems.Value as the data to use in the Chart Object. I have also used this expression to set the appropriate Series Label value.
.
Is there a way that I can use a parameter value for the array of choices? I am working with a report that needs to have either 3 or 4 groups. There are 4 different combinations for the order of the groups. I would like to report user to be able to select the order of the groups from a dropdown box. Because only one choice requires 4 groups, I decided to repeat the 3rd group as the 4th group and hide it in the display if only 3 groups are needed. Here are the group choices. I have indicated the group that I will hide with ().
Select Groups:
Salesman, Customer, Order, (Order)
Salesman, Plant, State, (State)
Salesman, Plant, Customer, Order
Plant, Customer, Order, (Order)
I haven't been able to figure out the correct syntax to use in the parameter value.
Of course I could just use 4 parameters, one for each group, but I am hoping I can use this method.
Thanks for you help!
Sarah
Hi Sarah,
Firstly, excuse the delay. For some reason your comment got picked up as spam…
I have similar reports that use dynamic groupings and have found that (although bulky) using cascading parameters works best. Create a list [Salesman | Customer | Order | Plant]. Now create 4 parameters that each call the list (Group 1, Group 2, etc). However, for each Group after Group 1 you will need to exclude the option that was selected in the previous groups.
This will give you users total control over how the groupings will be displayed, but may need a little training to explain how to use them to the best effect.
Here’s a nice article from Chris Hays on Dynamic grouping that should help you.
Let me know if you come up with any other ways to do this!
Hi
I have a quote form where under the part no, description etc they have notes for the customer, I want to be able to only have the notes line show if there is a note but it also needs to be able to show more than one note at a time e.g. if the part needs engineering work and it needs a colour check I need to let the customer know this. Is the choose expression the right one and do you have any clues as to the correct format for this?
Thanks
Hi Emma,
Assuming that you are using a nested table for your ‘notes’ lines, I would go with setting the visibility property using an expression to evaluate whether or not you have any data to show.
You can find more about the visibility property here.