Skip to Content
Author's profile photo Debjit Singha

Dynamic Selection Of Keyfigures via Formula Variable

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

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ethan Jewett
      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)

      Author's profile photo Benedict Venmani Felix
      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

      Author's profile photo Former Member
      Former Member

      its very nice

      Author's profile photo Former Member
      Former Member

      Very useful document

      Author's profile photo Former Member
      Former Member

      Good post

      Author's profile photo Sandeep a
      Sandeep a

      nice document .

      regards

      Sandeep

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Excellent one.. 🙂

      Author's profile photo Vijayeta Sharma
      Vijayeta Sharma

      Nice document

      Author's profile photo Former Member
      Former Member

      very good article ... 🙂

      Author's profile photo Former Member
      Former Member

      Good document..!