I'm looking for a way to create a conditional expression in a chart based on a calculated field name.
I have a datamodel containing three fields that can be summarized; Forecast MPC, Forecast TPC, Forecast KG.
The datamodel also has an island table called 'Unit' with three values; MPC, TPC and KG.
I want the expression in my chart to use a caIculated field name, where the field name is calculated based ont the selected unit.
For example:
- The user selects unit 'MPC' in a list box
- The chart expression should calculate the expression sum([Forecast MPC])
The field name in the chart expression should be calculated by combining the text 'Forecast' with the selected value from the Unit field.
I know that I could solve this by creating the following expression;
if( Unit = 'MPC', sum([Forecast MPC]), if( Unit = 'TPC', sum([Forecast MPC]), sum([Forecast KG])))
The drawback of the above expression is that three aggregated values in the if-then-else are calculated and only one is shown in the chart, so this is not the best solution if performance is an issue. I'm also aware of the Conditional feature on the Expression tab in the chart wizard. but I need a more dynamic solution.
Any help would be appreciated.
