EPM Function to Display all Base Members in MS Excel

[Former Member]

Hi everyone,

I need to display all base members of a dimension in Microsoft Excel.

Tried =EPMContextMember(,"DIMENSION1","CALC=N") but don't think it is working as it reads my context, which is a parent node.

What EPM function should I use to do so?

Thanks!

[Former Member · Accepted solution]

Hi Mohammad,

I don't think that you can display the results of an EPM report in once cell.

You could however concatenate the results as a second step.

Regards,

Mark

[Robert_Jan_Bron]

Hello,

I think what you want is the EPMDIMENSIONPropertyValues formula. This formula generates a comma separates list (or Validation Dropdownbox) of all values in a property of a certain dimension. You can choose to set it on ID. Only drawback is that you cannot filter out the baselevels.

However it is possible to create a property that replicates the id. If you fill tthat property only for baselevel members you can call that property in the EPMDIMENSIONPropertyValues function.

[Former Member]

Dear all,

Allow me to rephrase my question.

I have DIMENSION1 with five base members namely:

MEMBER1

MEMBER2

MEMBER3

MEMBER4

MEMBER5

I need to display these five base members in Cell A1 in Microsoft Excel.

Cell: A1

Cell Value: MEMBER1, MEMBER2, MEMBER3, MEMBER4, MEMBER5

What EPM function should I use to do so?

Thanks!

[former_member599120]

Hi Mohammad,

I think you can do it using Edit Report button and click a particular axis member after that choose Base Member or Children. And If you would like to use EPM keyword, please use EPMAxisOverRide().

For example: DIMENSION1 is used for column axis and would like to retrieve base members from PARENT01 parent.

Solution: EPMAxisOverRide("000", FALSE, DIMENSION1, BAS(PARENT01)).

Please try to implement above example for your case.

Thanks,

Wandi Sutandi

[Former Member]

Hi Wandi,

Thank you for your reply.

Were you trying to display all base members in a BPC report?

Actually, I don't need it to be displayed in the report. I need all base members of DIMENSION1 to be displayed in one single cell - in above case Cell A1 and is separated by comma for each member.

[Former Member]

Hi Mohammad,

You can probable create a dummy report in a hidden area. Use dimension override with the filter as BAS(MEMBER). Then in cell A1, use a function to concatenate the values in the row axis of the hidden report, separated by comma.

Hope this helps.

[Former Member]

Hi Mohamad,

You can use the EPMDimensionPropertyValues function with ID as the property in the function parameter. It will display the IDs as comma separated list. However, the problem is that it will show all the IDs, and you cannot do any kind of filtering.

Hope this helps.

[Former Member]

Hi Mohamad,

In addition to the EPMContextMember function, please also use the EPMAxisOverride function.  Point the override function to the EPMContextMember function.

Hope this helps.

Vijay.