Skip to Content

Scenario:

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.

Requirement:

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).

Steps:

  •    Include all necessary fields into the report, including all four Amount fields (in columns section). Hide all key figures.

Dynamic Selection Of Keyfigures Via Formula Variable (1).JPG

  • 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)

Dynamic Selection Of Keyfigures Via Formula Variable (2).JPG

  • Go to edit of “Amount in” formula field. Right click on formula variable and create a new formula.

Dynamic Selection Of Keyfigures Via Formula Variable (3).JPG

  • Select Currency Type as reference characteristics in Replacement path option.

Dynamic Selection Of Keyfigures Via Formula Variable (4).JPG

  • Select Currency Type variable defined any other inforprovider.

Dynamic Selection Of Keyfigures Via Formula Variable (5).JPG

  • In Currency/Unit tab select  numeric (as currency are defined as 10, 00, 30.. in currency type master data)

Dynamic Selection Of Keyfigures Via Formula Variable (6).JPG

  • Write a formula as below: (please refer currency type master data)

Dynamic Selection Of Keyfigures Via Formula Variable (7).JPG

  • To make report more readable, we are going to include a replacement path type test variable:

Dynamic Selection Of Keyfigures Via Formula Variable (8).JPG

  • Make it as replacement path type and select Currency Type as reference characteristics.

Dynamic Selection Of Keyfigures Via Formula Variable (9).JPG

  • Replacement path tab select the same variable we used in Formula variable

Dynamic Selection Of Keyfigures Via Formula Variable (10).JPG

  • Variable Screen

Dynamic Selection Of Keyfigures Via Formula Variable (11).JPG

There are couple of other ways as well, like using calculated Key figure and a formula variable as below:

Dynamic Selection Of Keyfigures Via Formula Variable (12).JPG

Note:

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).

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Ethan Jewett

    I’ve moved this to the BEx space from the Data Warehousing space. Please post documents and discussions pertaining to BEx in the BEx space.

    Thanks,

    Ethan

    (moderator)

    (0) 
  2. Benedict Venmani Felix

    Nice idea Debajit!!!

    I was wondering if this was even possible and wanted to try this out in our sandbox 🙂 .

    Now, you have helped me gain the knowledge without any effort.

    Thanks,

    Benedict

    (0) 

Leave a Reply