How to Create a Dynamic Refencing Local Member
Local Members are very useful and easy to use when it comes to creating simple formulas. They work very well without breaking. But sometimes even simple calculations tend to break if we are using formulas involving specific members. For example, in a report where Rows/Columns are eliminated with zero or empty values, a Local Member with sum of few members will break if one of the Member is not present in the report after refresh. We can use a Position Local Member, but the formulas can go wrong referencing wrong rows/columns after eliminating. Or we end up with a big report if Rows/Columns are not eliminated.
In the below report there are 4 Nodes with lot of Company codes are with Zero values for a specific period. And company codes in NODE4 do not have any data. If a sum of 4 nodes has to be created, we can either use a simple calculation in excel can used. This will result in Formula in LocalMember as below.
and if you are using Positional Local Member the formula will look like this
The problem with the above 2 formulas is , if rows are eliminated with Zero values, and if one of the Node is removed, the Local Members will not work.
There are 2 ways in which we can resolve the issue.
One way is you can just hide the rows/columns instead of removing the rows/or columns. The Local Members work, even if one of the nodes is hidden.
Below Positional Local member, still works even if NODE4 is hidden.
But let’s say if one of Member (1002) is moved from NODE1 to NODE2 as below, the Positional Local Member will still work but with wrong formula. Here, the Local Member formula is still reflecting D42, which is wrong.
When there are multiple reports involved, Hiding Zero or Empty rows may not be available. In which case use below formula to locate specific Node ID and extract the value.
Below is a complex looking but simple formula to calculate Local Member for specific Member ID’s.
What the above formula does is to find specific Nodes in the report area using VLOOKUP and dynamically referencing the Rows and Columns based on the Cell used for calculating the Local Member. The IFERROR will give a “0” if the VLOOKUP returns an Error or #N/A.
There are few things you need to remember while using the above. Be sure to use only ID’s in report. There is not restriction is using descriptions but if Descriptions are used, and in case of change in descriptions, the formula might break. The report might get bigger and pose a maintenance problem if more member id’s are used in the calculation.
Otherwise, you can use this as one of the option in creating Local Members that has dynamic Rows/Columns and then create or use in other local members based on this.
Any comments and/or suggestions are welcome.
General recommendation is to avoid volatile Excel functions like INDIRECT and VLOOKUP for performance reasons.
I tend to use the 'Sorting & Grouping' options from the EPM dimension selector of a report where it's a bit easier to define subtotals. Or, the 'Calcualte Parents in Hierarchy' report option can also be helpful.
Agreed. But there are some cases, where we still need Local Members.
At times 'Sorting & Grouping' does make reports bit crowded with formulas.
Just wanted to give another way to create local members, though may not the best way. It might serve the purpose.