cancel
Showing results for 
Search instead for 
Did you mean: 

EPM Report - Deducting Value from Total where node not in report

Former Member
0 Kudos

Hi,

I need to create a report which provides a total at the bottom of the report - I have done this by putting the top node in the report. The problem is i also need to remove one node from the report and the total. To remove from the report i can used exclude members. This does not change the total.

See below the example table which contains two report (had to create two reports for specific reasons) and the hierarchy. The total should be the "top account" minus "Account D"

Table

Hierarchy

Options I have looked at:

1. Using a local member for the total but the node for Account D needs to be displayed. I also tried using EPMRetrieveData which did not work as expected as it didnt seem to take into account the column, i.e. JAN, FEB, MAR - it just returned the same value for each month

2. Another option is just to create another report elsewhere which contains details of the Account D and I just use that as a basis to deduce the value from the Top Account.

Any other options?

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Hi Leo,

Use option - Calculate parents in Hierarchy.

Vadim

former_member186338
Active Contributor
0 Kudos

Sample - Member 20202020201 is removed:

With Calculate Parents in Hierarchy option the value from cube is replaced with Excel formula SUM of members shown in the report

Vadim

former_member186338
Active Contributor
0 Kudos

Or exclude the member with filter in member selector like here:

Vadim

P.S. For sure with Calculate Parents in Hierarchy option...

Former Member
0 Kudos

Hi,

Thanks for the response and I didnt know about that option.

However, in my example since there are two reports in the worksheet it sums only C, E, F. When I want it to add A, B, C, E, F.

Cheers,

former_member186338
Active Contributor
0 Kudos

Sorry, but what for you are using 2 reports????

Former Member
0 Kudos

Hi,

i used this for ordering the nodes. Example below of hierarchy and table.

Effectively, I have in the first report included "account 3" and in the second report i have set the report to show "member and descendants" of "Top Account" at "level 2 but exclude "account 3". Also in the example, member "D" has been excluded in report 2.

This was the only way i could think of ensuring the report is dynamic and pick up all data under top account. Note, i am not able to re-order hierarchy in BPC. 

Hierarchy

Table

Cheers,

former_member186338
Active Contributor
0 Kudos

Bad idea with 2 reports! Use filters and order of lines in member selector like on the screenshot!

Vadim

Former Member
0 Kudos

Hi,

Thanks for that.

I just tried it but i need to able to filter out of second part of the report the report the member and children of "Account 3." See example below (I appreciate the example shows rep 1 and 2 but i am trying your example by only having one report. 

I dont want filter out actual members in the report because if new ones are generated I need them to be filtered automatically.

Is there a way to do this?

former_member186338
Active Contributor
0 Kudos

Sorry, Leo

I can't understand your explanation:

"I just tried it but i need to able to filter out of second part of the report the report the member and children of "Account 3." See example below (I appreciate the example shows rep 1 and 2 but i am trying your example by only having one report. 

I dont want filter out actual members in the report because if new ones are generated I need them to be filtered automatically."

Please, provide real screenshots with real account ID's and real hierarchy structure!

With the artificial pictures we are simply wasting time

Vadim

Former Member
0 Kudos

Hi,

The artificial pictures are a representation of the hierarchy and the report - pretty much exactly. If i was to show the actual report i would be showing in public my clients reports on the internet which i am sure you can understand i cant do. Let me try explain again - to be honest i dont think its i the pictures its probably my explanation.

The hierarchy is as below. Top account is the top node and it has the children account 1,2,3,4. Account 1 has the children A and B. Account 2 has children C and D, Account 3 has children E and F, Account 4 has children G, H, I

This is the final report I want to be able to display. Children of Account 3 at the top. Then the rest of the data below but needs to exclude member D

I have tried to use your example, but I guess in the ID I need enter the member IDs that I want to exclude rather than the node i.e. I need to enter E, F. The problem with this is if the node "Account 3" has new accounts added to it my report wont be dynamic. Is there a way around this?

former_member186338
Active Contributor
0 Kudos

Simple question - how do you currently exclude children of Accont 3 from the second report?

About real members - just show member ID's without description - no issues with your clients!

Vadim

P.S. You can exclude children by property PARENTH1<>ACCOUNT3

Former Member
0 Kudos

Hi,

I have now one report - performance improvement.

I am able to exclude data if needed by excluded members tab and the total still works with the "Calculate parents in Hierarchy" selected.

PS: I used excluded members tab exclude children from second report.

Thanks Vadim

Answers (1)

Answers (1)

Shrikant_Jadhav
Active Contributor
0 Kudos

Hi Leo,

provide more details about row & page  axis, nature of report i.e. Static or dynamic.

Close the old thread If your query is resolved.

Regards,

Shrikant

Former Member
0 Kudos

Hi report is dynamic - in the sense all items i have selected in the report are dynamic selections. The rows/axis etc.. the example should provide this info.