EPMLocalMember formula goes wrong in a dynamic expandable report

[Sougata]

Hi everyone,

I inserted a local member into an EPM report referencing 3 cells by typing in an Excel formula =E28-E14-E20. One of the cells (E28) refers to a parent node (Dimension ACCOUNT & Member PBT) and other two refers to base members.

When I collapse the report for a parent node and all the base members are hidden, the formula for the local member goes wrong - the formula now becomes =E26-E12-E18 because two rows were hidden after the collapse.

The localmember formula generated by the system is =EPMMEMBER([S_ACCOUNT].[PARENTH1].[PBT])-EPMMEMBER([S_ACCOUNT].[PARENTH1].[RT_111000])-EPMMEMBER([S_ACCOUNT].[PARENTH1].[ET_250000]) which is correct but the result is wrong as the Excel references have moved.

Can you please suggest what I can do to correct this?

Thanks

Sougata.

[Sougata · Accepted solution]

Hi Vadim and Rishi,

Do you mean this?

If yes, how do I construct a formula saying Account EBITDA = PBT - RT_1110000 - ET_230000 - (XA_SUPPLEMENTARY -> XA_MAT_ITEMS -> XA_MAT_BTAX)?

I have now moved the EBITDA account outside the hierarchy... Is this correct or it needs to be under PL node to be able to calculate it correctly?

Regards,

Sougata.

[former_member186338 · Accepted solution]

Just like here:

For account dimension you can omit dimension name!

"I have now moved the EBITDA account outside the hierarchy" - you can have it in the hierarchy, it will not affect hierarchy aggregation.

Vadim

[Sougata · Accepted solution]

Thanks for your help Vadim so far - it has almost solved the issue except that I can't get a local member to stay visible on the report when it is attached to a member with a nil value and the EPM report is set to "Remove Empty" for the rows.

With report setting "Keep all" for Rows:

With report setting "Remove Empty" for Rows:

The local member "Material Items - Profit/(loss)...." is now invisible but I do want it to stay because it itself is not zero i.e. it should calculate E224+E225 which is not "Empty".

EPM Report setting for this local member:

I guess this is happening because the local member is attached to a member which has a zero value? So is it possible to override the "Remove Empty" setting in an EPM report just for one account member? if not then is there any other option to make it always visible but at the same time attached to this particular member ("Material Items - Outside Equity Interest") irrespective of its value is zero or not?

Regards,

Sougata.

[former_member186338 · Accepted solution]

"So is it possible to override the "Remove Empty" setting in an EPM report just for one account member?" - not possible!

Vadim

[Sougata · Accepted solution]

Thanks Vadim.

[former_member186338 · Accepted solution]

With the member located in hierarchy and having dimension member formula it will be visible

[former_member186338]

Please show the report screenshot with clear description! And it's a bad idea to use automatic generation of local members, do it manually!

Vadim

[Sougata]

Hi Vadim,

I also tried to manually create the local member then typed in the EPM formula in the "Edit" dialog box but the result is same - the figure is incorrect after report collapse because the excel cell reference moves after collapse. I'm attaching a few screenshots.

After Expand

After Collapse

EPM Local Member Setting

Account Member Settings

Another thing I noticed that node TOTAL REVENUE is collapsed by default but other parent nodes are expanded by default i.e. when I launch the report.

Regards

Sougata.

[former_member186338]

Local member is not a good solution in case of expand/collapse. Create a new member in the account dimension and use dimension member formula for this member.

Vadim

[Sougata]

Hi Vadim,

FYI I'm quite new to the BPC world but I did think about creating it as a new member in the hierarchy but the problem seems to be that I want to include some of the existing accounts in that new node but those already exist elsewhere in the hierarchy....so I'm not sure how to achieve this.

The other way could be to use EPMRetrieveData for a local member - do you think it will work for a expand/collapse EPM report?

Regards,

Sougata.

[rishi4892]

Hi Sougata,

You can create a new member,

keep it outside the hierarchy,

Apply Member formula on it.

Now insert that Newly added member to first row in the report and Hide it.

Now In your existing local member refer to first row.

Thanks,

Rishi

[former_member186338]

"Now insert that Newly added member to first row in the report and Hide it.

Now In your existing local member refer to first row." - sorry, but why to use this strange method???

[former_member186338]

Hi Sougata,

Please read help about dimension member formulas:  SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP Library

and:

Simple Rule Formula Examples - SAP Business Planning and Consolidation, version for SAP NetWeaver - ...

Vadim

P.S. Add the new member with member formula to report instead of local member.

P.P.S. If you have many cases where the account doesn't fit into existing hierarchy - then you may think about creating additional hierarchy...

[rishi4892]

So that it won't impact existing hierarchy(they do not need to add member in the existing hierarchy), and the local member can be easily inserted after particular member as shown in the screenshot.