A common need in an Essbase calc script is to map values from one dimension to another.

For example, let’s say you have accounts that you want to map to entities. You load values into those accounts at ‘Main Entity’, and then need to assign each account to its own unique entity.

Dimensions:

Account

  -Sales

  -COS

-SpecialEntitycharges1  {EntAttribute: “a_Entity001”}

-SpecialEntitycharges2  {EntAttribute: “a_Entity002”}

-SpecialEntitycharges3  {EntAttribute: “a_Entity003”}

-Software

Entity

  -MainEntity

-Entity001

-Entity002

-Entity003

EntAttribute

-SpecialEntities

  -a_Entity001

  -a_Entity002

  -a_Entity003

 

Calc wanted:

FIX(@WITHATTR(“EntAttribute”,”IN”,”SpecialEntities”))

   @MEMBER(@substring(@ATTRIBUTESVAL(“Account”),2))=MainEntity;

ENDFIX

 

The issue: You cannot variablize the left-hand side of the equation in an Essbase calc

The solution: @XWRITE allows you to deliver your database data and it will accept in whatever intersection you define for it.. including intersections derived from string/member functions.

The calc:

 

 

While this simple trick isn’t a total replacement for variablization of the left side of the equation, it can often effect the desired result, opening up a lot of functionality in your Essbase cubes. If you’re in a jam, this calc, in combination with the use of specific naming conventions, might be able to help out.

 

If you have any questions, please feel free to email me at jkeskitalo@ecapitaladvisors.com

Happy Coding!

-Jon Keskitalo