Dropdown assignment to another dropdown

[former_member244687]

Hello

I have a requirement , I have create a dropdown1 and assign a property of the costcentre dimension. Also I have another dropdown 2 which has items 'yes' and 'no' , if the user selects yes I want the dropdown 1 to list out the values, if its no then the dropdown 1 should not be active.

thanks !!

[former_member186338]

The only thing you can do is to replace values in the list column with empty strings:

Other things will require VBA coding.

[former_member244687]

Thanks ! , but I need to bring a dimension in the dropdown ? how do I link the dimension to dropdown ?

[former_member623]

you can use EPMDimensionPropertyValues() to get the dimension property values in a dropdown list.

EG. " =EPMDimensionPropertyValues("consolidation","entity","id",H4)"

[former_member186338]

If you need to get a list of property values you have to use:

=EPMDimensionPropertyValues(;"ENTITY";"INTCO";A2;A3:A50)

A3:A50 - range of property values - to be used in combobox range of my example (instead of A2:A5)

[former_member186338]

Using EPMDimensionPropertyValues without Range parameter it's not possible to get a list to use it later in a custom combobox!

[former_member244687]

Thanks Vadim and Rekha, I have couple of more questions

1. I am not able to get the Hierarchy from =EPMDimensionPropertyValues("consolidation","entity","id",H4)" , when I use H4 in the formula , the report is taking cell H4 and not PARENTH4.

2. I wanted to use a drop down filer using =EPMDimensionPropertyValues , but this should be based on another dropdown , how do I create the dependency, is there a sample VBA code ?

[former_member186338]

Not H4, but PARENTH4 in the formula!

"I wanted to use a drop down filer using =EPMDimensionPropertyValues , but this should be based on another dropdown , how do I create the dependency, is there a sample VBA code ?" - look on my sample, no VBA required!

[former_member244687]

I did try with 'ParentH4' and also 'PARENTAFTER' I am not able to get the expected result. What is the 'all value range' in the function ?

My requirement is I need to have a dropdown object and an EPMDimensionPropertyValues function. when I make the selection in the dropdown (Say A as in the pic attached ), the EPMDimensionPropertyValues should give a particular hierarchy say PARENTH2

[former_member186338]

You can't use EPMDimensionPropertyValues dropdown with customization! You can only use it to generate a list of values using range parameter:

Having this list of values you can use it in Excel combobox like in my original answer!

[former_member244687]

Oh ok, my issue is this hierarchy is very dynamic so how do I hard code these values ? every week i have to change

[former_member186338]

I don't understand you!

EPMDimensionPropertyValues will refresh list in the range. Whats the issue?

[former_member186338]

P.S. "dropdown 1 should not be active" you can't disable dropdown using Excel formulas, maximum you can do is the empty dropdown!

[former_member186338]

Screenshot with full details, please!

How do you created dropdown?

[former_member244687]

Attached is the screen shot, If I use Dropdown A it will give me 'Yes' and 'No' values and if the user chooses Yes then dropdown B will be active user can have the drilldown costcentres ( coming from Costcentre dimension ).