DROP DOWN LIST - REPORTS - SAP BPC

[Former Member]

Hi SAP Gurus,

I have been working on some reports on SAP BPC that should display members of a certain dimension in a dropdown list.

In particular, I would like to let users select a Country from a specific Cluster. Each Cluster corresponds to a certain geographic area.

First of all, I used EPMContextMember to come up with the Clusters.

Clusters have the following format:

GRS - GERMANICS

RUT - RUSSIA TOTAL

Then, I linked the first three letters (ID) to another cell in excel because they represent the named range of the hierarchy. The latter is in a hidden sheet.

What I would like to do is to have the countries related to that cluster but I would like to do it with a dropdown list instead of using an EPM formula.

No Data Validation but Combobox.

Thanks in advance.

[former_member186338 · Accepted solution]

Sorry but not clear!

What do you have on the hidden sheet?

Can you show some screenshots?

Can you explain the parameter selection process step by step?

P.S. Excel Forms combobox is static by nature... (without VBA)

[Former Member · Accepted solution]

Hi Vadim,

thanks for replying. I will try to explain better.

On the hidden sheet I have the whole hierarchy, like the screen below

In a cell of the Report Sheet (let's say B1), instead, I have:

=EPMContextMember("Finance";"country";"calc=y";TRUE)

The user by double clicking in cell B1 can see the clusters (that are the same from the headers of screenshot above)

Finally, in cell F1 I would like to have the base levels of each country.

Please let me know if now is more clear.

[Former Member]

Hi Vadim,

thanks a lot for the answer you inspired me.

Now it works

[former_member186338]

Something can be done (not perfect, but...)

[Former Member]

Hi Vadim,

thanks for replying. I will try to explain better.

1. In the hidden sheet I have the country's hierarchy, like the following screen:

2. In a cell of the Report Sheet (let's say B1) I have the EPM formula to retrieve the Clusters.

=EPMContextMember("Finance";"country";"calc=y";TRUE)

The result is the ID + Description (e.g. UKI - UK & Ireland).

I do a transformation (that i put in a hidden cell) for the first 3 letters of cell B1 to come up with UKI and link it with the hierarchy.

3. I would like to have in another cell the dropdown list (combobox) in order to let users select the base level country under the clusters. (e.g. UK and Ireland if the selection is UKI)

I used a Data Validation and it works with an Indirect formula. However, I would like to know if anyone has tried to do the same with a combobox.

I hope it's more clear now.