on 09-19-2014 8:37 AM
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
Hi Arnold,
Please provide a report screenshot with explanation of formulas... Then I will be able to help you!
Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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"!
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
Arnold,
Did you try generating local member with Activate Local Member Recognition on but use Position in Axis off?
Regards,
Ashish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.