Eric Erikson FCC Consulting

View Original

Oracle EPM: Hierarchy Calculations

Most implementations have some statistical calculations, particularly with accounts. There are multiple levels of calculation complexity. Member formulas is usually the go-to place to write these; however, for simple calculations, there's another option. And it has a benefit that member formulas don't have!

Create an alternate hierarchy with the parent member representing the result and the children representing the inputs. Then use the consolidation operator to add/subtract/multiply/divide or calculate a percentage. In the below example, FCCS_Gross Profit is being divided by FCCS_Gross Sales to calculate a gross margin percentage. And with the percentage option there is no need to multiply the results by 100.

If the calculation needs parentheses, then create a mid level parent to represent that part of the formula.

Now, the benefit. A user can view the result on a report, data grid, etc. and then drill down into it to see the components. The result member is just a parent member, after all. So that's helpful.