0

# Local member formula to sum only baselevel member

Apr 09 at 03:49 PM

62

Former Member

Hi Expert,

I have a template wherein rows are dynamic (i.e. it changes based on selection) and rows contains parents as well as base level members.

I need to have local member formula at the end which totals only values of base level members. I have gone through = SUM(EPMALLMEMBERS), however it includes parent member also in the calculation.

Vadim Kalinin Apr 11 at 04:59 PM
1

In order to sum base members create extra column with local member containing property CALC =EPMMemberProperty(;A2;"CALC")

Then in Total local member row use Excel SUMIF based on CALC property.

Show 5 Share
Former Member

I was expecting this one.. :)

Former Member

There are no other ways - first you need to get CALC property somewhere then SUMIF.

Alternative require VBA programming.

Former Member

I tried doing sumif way. However when I give sumif formula and refresh the report, sumif formula get updated and it makes Range selection same as SUMRange.

Below is the screen shot while creating EPMLOCALMEMBER formula =SUMIF(I25:I26,N,J25:J26)

After entering the local member formula, the formula updates to =SUMIF(J25:J26,,J25:J26)

lv8b1.png (37.2 kB)
dygqz.png (40.6 kB)
Former Member

Sorry, but formula for SUMIF has to be based on EPMALLMEMBERS and some complex Excel functions to have proper ranges!

Former Member

Vadim Kalinin Apr 09 at 05:35 PM
0

Use hierarchy parent instead of local member! Strange idea to talk about local member.

Show 9 Share
Former Member

In report selection has been provided wherein user can select "Member and children" or "Base level" or "Member and descendants". That's why I want to give local member formula at the bottom which totals the value of only Base level member.

Former Member

Sorry, but I do not understand the business logic of the mentioned total!

If the user will select 2 parent members with "member only" the result of total will be zero...

Former Member

I have two nodes Balance sheet and Profit and loss respectively in report. Also, I have excluded certain members from the Balance sheet node in report.

Further I want to show the calculation at the bottom showing the total of all the base member sums up to zero.

Former Member

Sorry, but business logic is not clear!

First you tell me that user can select some accounts himself.

Then "I have two nodes Balance sheet and Profit and loss respectively in report. Further I want to show the calculation at the bottom showing the total of all the base member sums up to zero."

Looks strange!

Former Member

No user cant select..

User can select expansion option i.e. Member and children or Member and descendants or Base level only.

based on selection Account dimension in row will appear.

Former Member

Not clear!

Please explain how: "User can select expansion option i.e. Member and children or Member and descendants or Base level only"

Do you use EPMDimensionOverride or...?

It's better to clearly explain your report (with screenshots, etc) if you want an answer!

Former Member

yes through dimension override. only thing I want is to have sum of base level member below row through local member formula.

Former Member

Former Member

Have to repeat - full details required!

Or it's a waste of my time!

Former Member Apr 11 at 11:15 AM
0

Below is the example of the data. I want to total all the base level member which are visible in report excluding the Base level Account 4, cause that member is excluded from the report.

Why I am not taking total of "Balance sheet" and "Profit & Loss" is because it will have the value of the member which I have excluded in report.

qhy3s.png (16.9 kB)
Show 10 Share

Sorry, but it's not a real report and without any EPMDimensionOverride formulas!

You are simply wasting my time!

Former Member

Below mentioned is the screen shot of the template. EpmdimensionOverride formula works fine, Only issue is that I want to show value 21,000 and not 18,000 (i.e. Sum of Base member).

resri.png (17.9 kB)
Former Member

"EpmdimensionOverride formula works fine" - I am not asking about how it works, I am asking about the text in the cell with EpmdimensionOverride!

Do you understand the meaning FULL INFO about report???

Former Member

Above is the dimension override formula

Former Member

And what do you have in A8 and A9???????????????????????????????????

It's not a paid service :)

Former Member

You asked about only text in cell with dimension override :) Blue cell was A8 and Orange one was A9 which is highlighted above.

A8 has

=IF(H13="Base","BAS(BALANCE_SHEET)",IF(H13="Member","BALANCE_SHEET",IF(H13="Member and Base","BALANCE_SHEET,"&"BAS(BALANCE_SHEET)",IF(H13="Member and Descendants","BALANCE_SHEET,"&"ALL(BALANCE_SHEET),PARENTBEFORE",IF(H13="Member and Children","BALANCE_SHEET,"&"DEP(BALANCE_SHEET)","ABC")))))

A9 has

=IF(H13="Base","BAS(INCOME_STATEMENT)",IF(H13="Member","INCOME_STATEMENT",IF(H13="Member and Base","INCOME_STATEMENT,"&"BAS(INCOME_STATEMENT)",IF(H13="Member and Descendants","INCOME_STATEMENT,"&"ALL(INCOME_STATEMENT),PARENTBEFORE",IF(H13="Member and Children","INCOME_STATEMENT,"&"DEP(INCOME_STATEMENT)","ABC")))))

I asked you to provide FULL info! Now it's more or less clear!

You have:

1. Base - only base members of BALANCE_SHEET & INCOME_STATEMENT

2. Member - parent members only: BALANCE_SHEET & INCOME_STATEMENT

3. Member and Base - parent members and base members for BALANCE_SHEET & INCOME_STATEMENT

4. Member and Descendants - parent members and descendant members for BALANCE_SHEET & INCOME_STATEMENT

5. Member and Children - parent members and children members for BALANCE_SHEET & INCOME_STATEMENT

What do you want to have in your local member in each case?

Former Member

I want the sum of base level member in all the cases. However for Point 2 and Point 5 there wont be any base member so data wont appear and that's fine...

Former Member

Sum of base members for P&L and Balance Sheet???? What is the business value???

Former Member

Cause total of Balance sheet and Profit loss forms Trial Balance :)

And in Trial balance we want total of Balance sheet and Profit loss (excluding Current year retained earning account from template).

And that's where I am facing issue with that exclusion of Current year retained earning account. I cannot simply take the total of top node Balance sheet and Profit and loss.

I have prepared the report and given selection along with exclusion of CYRE account and I want to make sure total of Balance sheet (Excluding CYRE Account ) and Profit and Loss account comes to zero.