Dynamic Selection Of Keyfigures via Formula Variable
Base cube have 4 different keyfugres for Amount filed: Amount (LC), Amount (DC), Amount(GC) and Amount(4th) along with other fields. In other words all different currency types are appearing in the same row.
Design a BEx report, where user have the option to select currency type and accordingly report display selected currency type Amount field only. In other word dynamic selection of key figure is required.
Note: Cube do not have any Currency type field as there is no need to distinguish rows (as all currencies are appearing in same row).
- Include all necessary fields into the report, including all four Amount fields (in columns section). Hide all key figures.
- Add a formula in the column section and name it as Amount in (we will use replacement path text variable to display which currency type is selected)
- Go to edit of “Amount in” formula field. Right click on formula variable and create a new formula.
- Select Currency Type as reference characteristics in Replacement path option.
- Select Currency Type variable defined any other inforprovider.
- In Currency/Unit tab select numeric (as currency are defined as 10, 00, 30.. in currency type master data)
- Write a formula as below: (please refer currency type master data)
- To make report more readable, we are going to include a replacement path type test variable:
- Make it as replacement path type and select Currency Type as reference characteristics.
- Replacement path tab select the same variable we used in Formula variable
- Variable Screen
There are couple of other ways as well, like using calculated Key figure and a formula variable as below:
Although the sue of calculated keyfigure will have some impact on the report performance. But in scenarios where we need to use multiple keyfigures, using one global Calculated Keyfigurer along with a formula would be a better option.
Feel free to comment and share your idea(s).