(New to EPM) Need to select multiple members of 1 dimension in EPM Retrieve report

[Former Member]

Hello,

I have taught myself how to create custom reports using a combination of EPM Retrieves and VBA but I am not very good at it yet.  Thanks in advance for your help. 

I want to create a report which sums multiple members in a dimension, similar to the "Allow Multi-Selection" functionality on the Page Axis within BPC.

See Screenshot (attached).  In Cell c10, I have only one member specified in the "Camp" dimension. I want to be able to specify multiple members, so that the report retrieves the sum of all the members' account activity.

My only alternative is to generate multiple worksheets, one with each member, then sum them together, but I want to avoid this if at all possible. It seems to me that if it can done within BPC's report editor, it should be possible with an EPM Formula or at least with VBA.

Thanks,

/Lisa

[Former Member]

Hello Vadim and Di~An,

Thank you very much. Yes, my ultimate goal is to have the new hierarchy created which groups the dimensions accordingly.  I am at the mercy of our company gurus on this point   And I do need to learn much more about how to create custom formulas etc. in BPC. My knowledge is still very basic.

My solution was to use VBA Code to Copy>PasteValues>Add for each member until I achieved the "group sum" I needed. It is not elegant and it takes a while to run, but, it works for now!

Thanks again!

/Lisa

[former_member186338]

Hi Lisa,

VBA is not a good solution for your requirements...

You are not using EPM report actually, just creating list of epmscaledata formulas. It will be always slow!

Vadim

[Former Member]

Hi Lisa,

It looks like your report is static rather than dynamic as far as row and column expansions are concerned.  If its always a specific number of added members, you can just add additional "+EPMScaleData(....)" to include your other member(s).  If its always just one other, you can reference this member by cell reference or even by member properties defined in its cohort member using EPMMemberProperty() if you have it setup. 

Other options you can consider if this is something that is regularly used across reports or on demand and want to take full advantage of EPM's dynamic expansion features:

1. create parent-child relationship between the members (as Vadim suggested).  Sometimes this is not possible in the existing hierarchy so you may need to create an alternate hierarchy that is specific to these secondary groupings.

2.  You can also create calculated members for each of your groupings and use MDX formulas to add them together automatically on the fly.  You would then reference the MDX member in your reports.  You will need to activate member formulas for the dimension and keep in mind there is no turning back once you do activate member formulas.  MDX is not typically recommended due to performance concerns but in small doses its fine or if you are on the HANA platform.

Good luck and keep us updated on your progress!

Regards,

Di~An

[former_member186338]

Sorry, but it's absolutely not possible. I can recommend to create a hierarchy parent for required members and use this parent.

Vadim

[former_member186338]

P.S. Even multiple member selection in page axis is not a recommended option due to significant performance degradation. Have to repeat - use hierarchy aggregation, if necessary- create additional hierarchy.

[former_member186338]

P.P.S. If you want to scale data by 1000 you can do it simply using custom Excel format without EPMScaleData...

[former_member186338]

The simplest solution for this particular case:

1. Use normal epm report without epmscaledata.

2. Use multiple selection in page axis.

3. Use format number to show 1000 as 1 ( like ,,)

Vadim