Local member formula to sum only baselevel member

[bhagyesh_ravange]

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.

[former_member186338]

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.

[bhagyesh_ravange]

I was expecting this one.. 🙂

[former_member186338]

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

Alternative require VBA programming.

[former_member186338]

Please accept the correct answer...

[bhagyesh_ravange]

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)

[former_member186338]

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

[bhagyesh_ravange]

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.

[former_member186338]

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

You are simply wasting my time!

[bhagyesh_ravange]

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).

[former_member186338]

"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???

[bhagyesh_ravange]

Above is the dimension override formula

[former_member186338]

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

Do I need to ask questions about everything? One after one?

It's not a paid service 🙂

[bhagyesh_ravange]

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")))))

[former_member186338]

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?

[bhagyesh_ravange]

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_member186338]

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

[bhagyesh_ravange]

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.

[former_member186338]

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

[bhagyesh_ravange]

Hi Vadim,

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_member186338]

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...

[bhagyesh_ravange]

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_member186338]

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!

[bhagyesh_ravange]

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_member186338]

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!

[bhagyesh_ravange]

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

[former_member186338]

Please provide EPMDimensionOverride formula!

[former_member186338]

Have to repeat - full details required!

Or it's a waste of my time!