Dynamically Referencing Members in Business Rules
Often times with a Hyperion Essbase or Planning application, an allocation of data will be required. Many times, the allocation is simply moving data from one member to another. When the number of members involved is large, developing the script can be time consuming. When the members frequently change, the maintenance of the calculation can be a nuisance.
When the members involved in the allocation are similar on both sides (the from and the to), the following method can be employed to speed the development and limit, or eliminate, any maintenance required.
Requirement
The application has 50 members in which the data needs to be moved. The data originates from an account coming from the general ledger. The data needs to be moved to a new member that doesn’t exist in the chart of accounts. The new member will exist in a different part of the hierarchy.
Solution
The first step is to create a corresponding member for each of the 50 accounts that need allocated. These accounts will be identical to the original 50, except they will be prefixed with a “D” identifying them as a dummy, or made up, account. Each of these new accounts will have a UDA of “allocation.” The prefix of the member and the UDA are not critical. They will likely be something more meaningful to the requirements.
| GL Acct | Dummy Account |
| 500345 | D500345 |
| 500578 | D500578 |
| 607878 | D607878 |
Once the hierarchy is ready to handle the allocation, the following function can be used. In layman’s terms, this only executes on the new members added (identified by the unique UDA) and makes them equal to the corresponding member without the added prefix. We will assume that this is being executed on a scenario that equals “Actuals.”
FIX(@UDA(“allocation”))
/* Make the new member equal to the old member */
“Actuals” = @MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1));
/* Clear the old member */
@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1)) = #Missing;
ENDFIX
Let’s assume that the UDA is NOT added to the new, or dummy, member. If the UDA is on the originating member, the calculation would look like this.
FIX(@UDA(“allocation”))
/* Make the new member equal to the old member */
@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”)))) = “Actuals”;
/* Clear the old member */
Actuals = #Missing;
ENDFIX
Now we can break down these functions. Remember, the calculations loop through all members in all dimensions. In this example, setting the result equal to “Actuals” is simply making the account that the calculation is looking at, at that particular point in the loop, equal to whatever is on the other side of the equation.
@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1))
There are four functions used in this string.
- @MEMBER will convert a string to a member name
- @SUBSTRING requires 2 parameters (3 optional). The first is the larger string from which you want to take a smaller string. The second is where to start, with 0 being the first character. The third is how many characters to include. If this is left blank, it will take all the characters to the right of the second parameter.
- @NAME will convert a member to a string.
- @CURRMBR gets the current member of a specified dimension.
Putting this all together, this calculation (from inside out) is getting the current member of the Accounts dimension (“d345678”). It converts that member to a string. It takes all the characters to the right of the first character (“345678”). Then it converts the string back to a member. At this point, we can set that member equal to something.
@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”))))
The functions here are the same as above, except we are not removing the “d.” We are adding it.
- @CONCATENATE accepts two parameters and will combine those two in to one string
Putting this all together, this calculation (from inside out) is concatenating two strings, a “D” and the current member of the Accounts dimension (“d345678”). It then converts the string to a member. At this point, we can set that member equal to something.
Benefits
By using these functions, the calculations can be much smaller, quicker to develop, and completely maintained by the outline. This effectively gives the user community ownership on the maintenance.
I have used this tip for made a scrip, but in this section “@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”)))) = “Actuals”;” the code doesnt work.
I just currently used these functions without an issue. My guess is there is an issue with where you are using it. Keep trying and hopefully you figure out the issue. When you resolve, please post here so others can benefit from your experience.
@CONCATENATE(“D”,@NAME(@CURRMBR(“Account”)))) = “Actuals”;” the code doesnt work.
I have similar requirement, anything else here ?
I would need to understand more. If this is all the code you have it wouldn’t work. You can’t set a member to text. You can’t reverence a member without making it a member. Just too many missing things here to give you some help.
Sure, include that calculation line inside a calculation block.
Once the syntax is OK, make sure you:
1. Include the @contatenate inside an @member
2. If you are using it on the left side of an expression, it must be included inside a “calculation block” parentheses block like:
MemberName (Conditionals
CalculationCommands;
EndConditionals)
In the example you are showing I would rewrite more or less as follows:
“January”
(If (@ismbr(“January”))
@member(@CONCATENATE(“D”,@NAME(@CURRMBR(“Account”))))) = “Actuals”;”
EndIf)
Hi Kyle,
Has the approach of copying value from a Dynamic Calc parent member to a stored member worked for you? Below does not work. Goal is to copy value from dynamic calc accounts that end with _T, to corresponding stored account without _T. This works fine only when the Dynamic Calc account is directly referenced by its member name.
Thanks,
Ashutosh
FIX(“bREN”,”No GBLICP”,”No Intercompany”,”CONT_ADJS”, “OpeningBalance”)
“Periodic”(
@CALCMODE(BOTTOMUP);
@CREATEBLOCK(@Relative(“D”,0));
)
ENDFIX
FIX(@Relative(“D”,0),”bREN”,”No Intercompany”,”CONT_ADJS”, “No Intercompany”)
FIX(@RELATIVE(“TargetAccounts”, 0))
“Periodic”(@CALCMODE(BOTTOMUP);
/*Generating data at destination */
/* “No GBLICP” = “OpeningBalance”->”FKT022000_T”; FKT022000_T is a dynamic calc account */
“No GBLICP” = “OpeningBalance”->@MEMBER(@CONCATENATE(@NAME(@CURRMBR(“Account”)),”_T”));
)
ENDFIX
ENDFIX
If you are trying to create the block based on a dynamic member and the block doesn’t already exist, then this would not work. If your parent is sparse, the block won’t exist. If the parent is in period or account, the block MAY exist.