Calculate Parents in Hierarchies

[former_member544285]

Dear Experts,

I'm trying to create a report with "Calculate Parents in Hierarchies" options checked in EPM Options:

Dimension in Row Axis:

• ACCOUNT (A)

Dimension in Column Axis:

• TIME (T)
• BUSINESS_AREA (U)
• AUDITTRAIL (D)

Both ACCOUNT and AUDITTRAIL are setup in hierarchy. The report look like this (numeric values have been removed):

Column J contains ACCOUNT dimension members in hierarchical order. Formula are automatically generated on column K, L, M based on ACCOUNT dimension (For example formula for K54 is =SUM(K55;K140)-SUM(K52;K141). This indicates the formula is calculating based on ACCOUNT members).

However, for Column M, it automatically calculates based on Column K, L, and M because BUSINESS_AREA dimension member "BA_S1" is parent of member 110, 120, and 130.

My questions is : is there any workaround to force Column N to retrieve value without having to calculate based on the child member of BUSINESS_AREA dimension?

This has come into my attention because if I remove column K, L, and M, then member "BA_S1" will retrieve the data directly as expected. But if I put just 1 of the children, the column will turn into formula which calculate based on the child.

Any help would be appreciated.

Regards,

Hendry

[former_member186338]

Hi Hendry,

"My questions is : is there any workaround to force Column N to retrieve value without having to calculate based on the child member of BUSINESS_AREA dimension?" - looks like no way... In general there is no warranty that "Calculate Parents in Hierarchies" will work correctly (like in your sample with 1 children...).

Vadim

[former_member186338]

P.S. What business logic are you trying to implement with "Calculate Parents in Hierarchies"?

[former_member544285]

Hi Vadim,

So background story:

We have 2 members in the ACCOUNT dimension:

• Gain on Asset Sell (account type INC)
• Loss on Asset Sell (account type EXP)

These 2 accounts need to be alternately shown based on summation of several other expense accounts. If the summation is less than 0, it will need to show as Gain on Asset Sell. Otherwise it will need to show as Loss on Asset Sell.

Because I can't group those expense accounts into 2 different nodes in a hierarchy, to achieve this requirement, I set those 2 members as member formula:

• Gain on Asset Sell : IIF( [24401000] + [24402000] + [24403000] + [24404000] + [24405000] + [24406000] + [24407000] + [24499000] < 0 , [24401000] + [24402000] + [24403000] + [24404000] + [24405000] + [24406000] + [24407000] + [24499000], 0)
• Loss on Asset Sell : IIF( [24401000] + [24402000] + [24403000] + [24404000] + [24405000] + [24406000] + [24407000] + [24499000] > 0 , [24401000] + [24402000] + [24403000] + [24404000] + [24405000] + [24406000] + [24407000] + [24499000], 0)

Then I place those 2 accounts under the net income hierarchy. The parent, however, will not sum the value of member formula when I show without "Calculate Parents in Hierarchies". For that reason, I needed to activate the "Calculate Parents in Hierarchies" option so the value will show correctly.

However, enabling that option draws another problem, as ACCOUNT is not the only dimension with hierarchy in the report. The example is in my initial post. This demands the report to show all children of all parent members in the column so the value will be correct. So far it is working fine, but I'm wondering if there's a better way to do this as the current solution is not so efficient because I need to pull the data into the report, yet I need to hide them in the report. Also it is not good in maintenance perspective : i.e. when the parent have another children, then the report will need to be updated as well.

Any suggestion is welcome

Regards,

Hendry

[former_member186338]

Sorry, but "Then I place those 2 accounts under the net income hierarchy. The parent, however, will not sum the value of member formula when I show without "Calculate Parents in Hierarchies"." - what do you mean by "under the net income hierarchy"?

What do you want to achieve? What is the required result for parent?

Vadim

[former_member186338]

P.S. I mean that you Net Income parent has to be calculated properly without any dimension member formulas!

[former_member544285]

Hi Vadim,

I put all the said expense accounts under net income directly. Illustration:

• NET INCOME
  • INCOME
    • Gain on Asset Sell << Member Formula
  • EXPENSE
    • Loss on Asset Sell << Member Formula
  • 24401000 << will not be shown in the report
  • 24402000 << will not be shown in the report
  • 24403000 << will not be shown in the report
  • ...

[former_member186338]

Incorrect idea!

You hierarchy has to contain real members, not members with member formulas (members with member formulas are not used in aggregation).

If some accounts are not shown on report like 24401000, 24402000, 24403000, 24404000, 24405000, 24406000, 24407000, 24499000 but all mentioned accounts have NET INCOME as parent - then you will have correct NET INCOME without "Calculate Parents in Hierarchies"

Vadim

P.S. INCOME and EXPENSE in you case has to be dimension member formulas also...

[former_member544285]

What you said is correct - the NET INCOME value will be correct in 2 cases:

1. With "Calculate Parents in Hierarchies", but without showing Accounts 24401000, 24402000, 24403000, 24404000, 24405000, 24406000, 24407000, 24499000
2. Without "Calculate Parents in Hierarchies" in any case. This is because member formula Gain on Asset Sell and Loss on Asset Sell will not be aggregated into the INCOME, EXPENSE nor NET INCOME. But in that case, the value will look odd because the INCOME and EXPENSE value won't be showing value from the member formulas

As I said, I'm open to any suggestion to tackle this requirement. Do you have an idea?

Regards,
Hendry

[former_member186338]

"But in that case, the value will look odd because the INCOME and EXPENSE value won't be showing value from the member formulas" - Look on P.S. from my prev. answer!

[former_member544285]

The requirement is to have 2 separate rows in the hierarchy to show with the condition I explained above. I'm looking for another possible solution if possible. Local member is out of question because they want the line to show automatically in adhoc report when they pull the hierarchy

[former_member186338]

"The requirement is to have 2 separate rows in the hierarchy to show with the condition" - not possible to achieve using BPC hierarchy, can be only simulated by members with dimension member formulas (artificial parents).

Vadim