Issue while expanding Hierarchy - EPM Formatting not reflecting in rows

[Former Member]

Hello Experts,

In my report, structure as below -

when I double click on Org hierarchy D000CT120202 with the intention of expanding it to see its immediate descendants, the hierarchy doesn't expand. Rather it expands to show the EPMOLAPMEMBERO formula.

This is the case for only this Member and not the others. We noticed that this issue started occurring after adding the Order % and Sales % Local members in the report.

The Order % and Sales % are Local members that have been enabled and added against the members O. Profit and S. Profit respectively.

If we disable these two Local members then the above mentioned issue doesn't occur. Because of this same problem, the EPMFormatting on the members also get affected.

Order % = O. Profit/Order - The Percentage calculation is done by EPMFormatting

Sales % = S. Profit/Sales - The Percentage calculation is done by EPMFormatting

Kindly advise how we can overcome this issue.

Regards,

Sachin

[Former Member · Accepted solution]

HI Sachin,

Please give the Formatting sheet screenshot were you have applied the formulas...

What is Keyfigure column?? Accounts??

For the Org hierarchy check whether "Expand on double click" option is enabled from the User options.

Regards,

JP

[Former Member · Accepted solution]

Hello JP,

The key figure column is Account type.

Please find the screenshots for EPM Formatting sheet.

The expand option is enabled from User Options.

To reiterate, the Order %, Sales % are Local members that have been added as shown below

Kindly advise.

Regards,

Sachin

[Former Member · Accepted solution]

Sachin,

You have mentioned that Local members calculation is done by EPMFormatting sheet....but from the screenshots I am seeing there is no formula there...?"?? Also the Local members formatting is applied properly as given in the formatting sheet....

Whats the error....?

For the expansion Go to Sheet Options and check Custom Expansion....

Regards,

JP

[Former Member · Accepted solution]

Hello JP,

For example, when I take Cost Center - D000CT120201 and on double clicking it, it expands and gives me the two Descendants. But when I double click on D000CT120202, nothing happens.

Screenshot attached -

When I said that the Percentage calculation is done by EPM Formatting, I might have said it wrong. Pardon me, what I meant is the formatting for Order% and Sales% cells are done in EPM Formatting sheet.

If you see in the second screenshot attached, when I double click on D000CT120202, the formula starts showing up.

The calculation is done in the Report Editor, Local Member Tab section as shown in the screenshot in the Original post.

Regards,

Sachin

[Former Member · Accepted solution]

Ok!....Does the cost center D000CT120202 have any children below it?? show the member selector for costcenter in the report definition.

If the problem is with this one cost center only.....have checked with a new report creation? does it give the same error on new report.

Regards,

JP

[Former Member · Accepted solution]

Yes, it gives the same error with the new report creation as well.

Please find the screenshot for the Cost Center D000CT120202

Surprising thing is that when I remove those two local members in the Row axis; then this shows no problems. Only with the enabling of those two local members, do I face such an issue.

Regards,

Sachin

[Former Member · Accepted solution]

Ok...maybe you report is getting corrupted or after adding the local members it is becoming static!

Best way is to select Member and Descendants/Children in the member selector of Cost center dim in Edit report and prepare this report.

Drill down maybe risky sometimes...

Regards,

JP

[Former Member · Accepted solution]

Can I ask this, Formula for Sales%  - S. Profit/Sales and Order% - O. Profit/Order;

Would I be able to get the result for Sales % and Order% through Logic Script by adding these two Members as part of the Key Figures.

Regards,

Sachin

[Former Member · Accepted solution]

Yes you can......

Regards,

JP

[Former Member · Accepted solution]

Would you be able to provide me a sample script to get me through this scenario.

Regards,

Sachin

[Former Member · Accepted solution]

Yes you can....but if you want the calculations only for viewing purpose then local members is fine....If you want to save the data in system then use script logic! For such simple calculation are you gonna ask the user to trigger the script logic??? default logic...not recommended for this.

Or simply you can create new members in the KeyFig dim as accounts and put the formula from the EpmFormatting sheet on those accounts!

Regards,

JP

[Former Member · Accepted solution]

"Or simply you can create new members in the KeyFig dim as accounts and put the formula from the EpmFormatting sheet on those accounts!"

I like the above option as suggested by you but I would like to know how you would go about this so that I can implement the same in my sheet as well taking my scenario for Order % as example.

Regards,

Sachin

[Former Member · Accepted solution]

Sachin,

Add member Order_Percent to your dimension. In the EPMFormattingSheet under the section Dimension member/properties in Row section Select the Member from the dimension as you have selected the local members.

Go to the Content option double clicking "ALL" on the right hand side. Paste your formula in the content and "Override". Apply....You will see the formula posted in the report..

Sorry I don't have system, to give screenshots...

Regards,

JP

[former_member186338 · Accepted solution]

Hi JP

It's a strange idea to store calculated ratio in the cube   Dimension member formula is a standard solution.

Vadim

[Former Member · Accepted solution]

Hello JP,

As you suggested, I tried doing the same.

The Content option is disabled as you see in the screenshot.

Is there some way we can get this enabled.

Regards,

Sachin

[Former Member · Accepted solution]

Oh yes.....I forgot about the dimension formulas! . But wont the dimension member formula be hard on performance and that it gets repeated in all reports/input forms??

Regards,

JP

[Former Member · Accepted solution]

Click on Content...then it is enabled.

[former_member186338 · Accepted solution]

Sorry, but dimension member formula is the only correct way to get meaningful results on nodes. With data stored in the cube you will have absolutely useless results on parents.

[Former Member · Accepted solution]

Hey Vadim...Got your point! But in this case there are no parent/nodes involved for calculations.

Regards,

JP

[former_member186338 · Accepted solution]

I see nodes for org hierarchy and may be some other dims not shown on the report.

[Former Member · Accepted solution]

His calculations are for the members of Keyfigure dimension which is Account type dimension. Org Hierrachy is cost center dim with which he has drill down problem! For a particular member he doesn't get the child members after double click.

Look for local members....he wants to convert those into normal members for calculations!

[former_member186338 · Accepted solution]

Sorry, but you have to spend some time to understand how dimension formulas work...

If he select parent in org hierarchy then percents will be absolutely wrong if stored in the cube for base members of org hierarchy.

[Former Member · Accepted solution]

Thank you JP, Vadim, as always.

If possible, could you shed some light or share a few documents on dimension formula.

Regards,

Sachin

[Former Member · Accepted solution]

Standard help...

SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP Library

Also search on SCN for more information....

Regards,

JP

[former_member186338 · Accepted solution]

The benefit of the dimension member formula is that you will get the same results on any report just selecting the member with member formula. And the calculations will be correct for both base members and parents. It's a traditional method of calculating ratio KPI's.

[former_member186338 · Accepted solution]

P.S. Please read also here