EPM10 SP24. EPMDimensionOverride ... drop down selection

[Former Member]

Hi

New to BPC so please be gentle

Designing an input form in EPM add-in and looking to use a drop down list in the form for our Profit Centre dimension. The drop down list is to contain a large number of members not linked in any way by hierarchical relationship or property.

This is what I have attempted so far:

-- Profit Centre in the rows with a single member selected and the column hidden.

-- On another sheet, list of 5 members from the Profit Centre dim and another cell with a drop down list containing these members (using the Excel Data Validation functionality)

-- The following formula in the Page Axis of my input form ... =EPMDimensionOverride("000","Profit Centre",Sheet1!C3)          (where Sheet1!C3 is the cell with the drop down list)

The cell with the formula in states 'Expansion of Profit Centre Overriden' so it seems to suggest it has worked? However changing the selection in the drop down then refreshing the report does not appear to change the member of the dimension selected (I had input a value against one of the Profit Centre members and this value still shows no matter which member I select in the drop down).

I have tried wrapping the members in double quotes on the second sheet but this doesnt appear to have made a difference.

It seems a pretty simple requirement so I must be missing something obvious, or maybe this isnt the correct approach to take for this. I have checked the forums but cant seem to find a solution ... in examples I found, the above formula appears to be getting used where member properties are involved so maybe it wont suit in my case.

Any advice appreciated.

Thanks

JB

[former_member225135 · Accepted solution]

Hi John,

Dimension override function is applicable only to row and column axis.

Please check below link;

Or you can use something like this:

=EPMOlapMemberO(Sheet1!C3,"[PROFIT_CENTER].[PARENTH1].[XXX]","XXX","","000")

Regards,

Rahul

[Former Member]

Just coming back to provide an update to this ... ashamed to say it but I couldnt quite implement the suggestions for one reason or another (mainly ineptitude!)

In the end, I settled with amending the report to run over a more focused BEX Query and used standard double click and select functionality on the dimension in the page axis.

[Former Member]

You can simply use EPMContextMember function on that cell where u want a drop down.

This function will bring a popup window to select the member. Its even better than a drop down.

Nidheesh Garg

[former_member186338]

Sorry Nidheesh,

The EPMContextMember is NOT a drop down and it was clearly stated by topic author that drop down is required!

"I am aware that the user could just click the member in the page axis to drill into the member selection"

Vadim

[Former Member]

Vadim,

I was just answering :

"I wouldnt want them inputting against in the report design"

By using EPMContextMember or EPMSelectMember the report design won't get affected.

This is an alternative approach otherwise i agree with the solution provided by you.

Nidheesh

[Former Member]

Thanks to all for your input. I will review, do some testing and come back with any further queries or to dish out a plethora of points!

JB

[former_member186338]

Hi John,

Can you clearly explain your report requirements?

What do you want to show in the report and what do you want user to select? Screenshot of the required report will be useful!

Vadim

[Former Member]

Thanks both. In simple terms, I am looking to have a drop down in the page axis for Profit Centre dimension as per below.

The idea being that a user can select one of these members, hit refresh then see the data / input data for the selection that has been made (there may be 50+ profit centre members that would need to be selectable in the list).

I am aware that the user could just click the member in the page axis to drill into the member selection and I could maybe exclude the members I wouldnt want them inputting against in the report design, but in an ideal world I would like this to be controlled as per the screenshot.

Thanks

JB

[former_member186338]

EPMDimensionOverride is not related to page axis - only row and column.

Please read

About page axis member selection using link in EPMOlapMemberFormulaO. Second proposal in Rahul post is correct - C3 has to contain result of drop down selection!

Vadim

[former_member186338]

Sample:

A2 contains: = EPMOlapMemberO(B2;"[TIME].[PARENTH1].[2016.03]";"2016.03";"";"000") - page axis member

B2 is a cell with data validation by list of values in C1-C3

Vadim

[former_member225135]

Even i have suggested the same thing

Regards,

Rahul

[former_member186338]

Yes, and I mentioned it

Just attached the screenshot for better understanding!