cancel
Showing results for 
Search instead for 
Did you mean: 

Question on local member formula

Former Member

Hi experts,

I have a simple EPM report which shows accounts in rows and datasources and entities in columns. I have selected one datasource and several entities. The datasource is a fixed selected member, the entities are select as all base level elements of a node which have a specific property value.

I now need a total column in my report. When I let EPM do it I get a formula like:

=SUM(EPMPOSITION(1), EPMPOSITION(2),...)

Such a formula is not dynamic which means I need to update the local member every time I add a new entity.

I tried this:

=SUM(EPMSELECTEDMEMBERS(ENTITY))

But that only summed the final column, not all of them.

Can anyone help me on making this formula fully dynamic?

Thanks,

Arnold

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Hi Arnold,

Please provide a report screenshot with explanation of formulas... Then I will be able to help you!

Vadim

Former Member
0 Kudos

Hi Vadim,

okay, let me explain the full Excel sheet in detail. It contains several reports all based on the same rows (accounts) and with the same dimensions in columns (datasource and entity). The difference is the members selected for datasource and entity. The whole report on one screenshot doesn't work, so here is the top left part:

The yellow columns are the first report, the grey column to the right is my local member. Then follows the next report with a few columns, then another and so on.

If we look at the local member in detail:

you can see the formula is a sum listing individual members. In my options, the local member recognition is on and the option to use the position in axis is off.

This is the definition of the local member

My first attempt was to replace the formula with the one given above.

the result is

the sum now has only one element.

I then tried to change the local member to this.

which made the column disappear completely.

Thanks,

Arnold

former_member186338
Active Contributor
0 Kudos

Hi Arnold,

First simple question: what was the reason to put datasource in the column axis??? It's fixed - then - put it in page axis!

Then, having only ENTITY in column axis you will be fine with:

=SUM(EPMSELECTEDMEMBERS(ENTITY))

Vadim

Former Member
0 Kudos

Hi Vadim,

the next reports to the right use different datasources.

The business ides behind the report is to have first all real subsidiaries of a group on a specific datasource, then all automatic adjustments done by BPC (several datasources but all on group level), then the company we use for manual group adjustments and finally the consolidated total.

Technically:

the first report shows S_REPORTED for all base elvel elements of GR1000 which have GROUP = SUB

the second report shows GR1000 and all datasources that are part of AUTO_ADJUST

the third report shows entity 9999 (child of GR1000) on datasource MAN_ADJUST

and the final report shows GR1000 on datasource TOTAL.

former_member186338
Active Contributor

Still looks like that in all reports you have some fixed member in column axis and another is a list of members...

Anyway, magic formula for local member to sum from the first report column to the last report column:

=SUM(EPMPOSITION(1):INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

Vadim

P.S. Assuming that local member is in the next column after the last data column: "COLUMN()-1"!

former_member210696
Active Contributor

Vadim,

This is awesome. Often we struggle on local members in dynamic reports and somehow manage but this would really be useful in many cases

Former Member
0 Kudos

Vadim,

thanks, works a treat.

BR,

Arnold

0 Kudos

Hello Vadim,

your post is a bit over a year old already. But maybe you read this.

Do you have an idea what formula I could use to it the other way around so i put the local member formula on EPMPOSITION(2) and want to have the sum until the end of Report?

Thanks in advance!
Max

former_member186338
Active Contributor
0 Kudos

Please open a new discussion and clearly explain your requirements:

"i put the local member formula on EPMPOSITION(2)" - what do you mean by this? Screenshot required!

Vadim

Answers (1)

Answers (1)

former_member210696
Active Contributor
0 Kudos

Arnold,

Did you try generating local member with Activate Local Member Recognition on but use Position in Axis off?

Regards,

Ashish

Former Member
0 Kudos

Hi Ashish,

yes, use Position is off.

BR,
Arnold

former_member210696
Active Contributor
0 Kudos

Hi Arnold,

I tested this and works fine for me. When the Use position is off, it doesn't generate something like =SUM(EPMPOSITION(1), EPMPOSITION(2),...). Can you share screenshots?

Regards,

Ashish

Former Member
0 Kudos

Hi Ashish,

see my above answer to Vadim.

Thanks,
Arnold