Skip to Content
avatar image
Former Member

DROP DOWN LIST - REPORTS - SAP BPC

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jul 11, 2017 at 04:17 PM

    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)

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

      hier.jpg (17.3 kB)
  • avatar image
    Former Member
    Jul 11, 2017 at 04:58 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 12, 2017 at 09:21 AM

    Hi Vadim,

    thanks a lot for the answer you inspired me.

    Now it works

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 11, 2017 at 05:10 PM

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

    Add comment
    10|10000 characters needed characters exceeded