Filter EPM Property values based on another property in same dimension

[Former Member]

Hi,

I need to restrict property values of a Dimension based on selected value of another property of the same dimension.

For example, in Material Dimension, I have properties Planner and LOB

Material	Planner	LOB
100	ABC	LOB1
200	ABC	LOB1
300	XYZ	LOB2

The Planner will select a Planner Name, based on which the corresponding list of materials should be shown along with LOB.

I have tried EPMDimensionPropertyValues but it doesnt allow to restrict the IDs based on another filter condition.

Please help.

[Astha_M]

Hi Brinto,

As per above explanation, you want to give 2 dropdowns one for selection of Planner (eg. Planner 1, Planner 2, Planner 3 etc.) and the 2nd dropdown for selection of Materials. The Materials dimension has a Property Planner so based on the selection of planner, only relevant materials selection is required.

The Dependant selection for materials can be created using EPMSELECTMEMBER Function. In the function, You can add filter based on Planner Property.

Hope this helps.

Regards,

Astha

[Former Member]

Thanks everyone for the response.

As mentioned in the post, the requirement is a bit twisted.

Let me rephrase the requirement:

I need to have two dropdowns ( which should act as filters to the template).

MATERIAL diemnsion has a property PLANNER.

I need to have the first drop down as list of all PLANNERS ( I could do this easily by EPMDimensionPropertyValues).

For the next dropdown, I need to show only those MATERIALS which has PLANNER1 ( lets say, PLANNER1 was selected in the first dropdown).

I am facing issues for the second dropdown, as I cannot have a condition to show the Property (ID) values in EPMDimensionPropertyValues

Also note that MATERIAL is not present in the report ( this is because we need a different aggreagation for material in the report) so we cannot use EPMDiemnsionOverride.

Please help.

[former_member186338]

Sorry, but absolutely not clear... Can you provide some screenshots?

Vadim

[former_member186338]

Hi Brinto,

Both Mario and Bishwajit are correct, the issue was the wrong wording in your question!

The correct question will be:

I want to select materials in rows, having property Planner equal to some value (ex. "ABC"). I also want to have the column on the report with the values of the property LOB.

First is done using =EPMDimensionOverride("000","MATERIAL","Planner="&B7), where B7 contains ABC

Second is done by creating Local member with the formula:

=EPMMemberProperty(, EPMMemberID(EPMDIM_CURRENT_MEMBER(MATERIAL)), "LOB")

This formula will be automatically generated if you activate local member recognition and type =EPMMemberProperty(... in the fist cell of the column for LOB property.

B.R. Vadim

[bishwajit_das]

I have done a work-out with the ACCOUNT dimension and its two properties - ACCTYPE and RATETYPE.

Here I am selecting the ACCTYPE property values from a dropdown - cell  A2 (yellow colour)

Accordingly all the members of ACCOUNT dimension is populated in the cell B2 (yellow colour)

I can selected any account member among the list...and similarly the ACCOUNT will be overrided using the EPMDIMENSIONOVERIDE function int the cell C3. and the Report will show the respective account and the other property(RATETYPE) value.

Please following the screen shoots...

[former_member192509]

Good Day Brinto.

In this case you can use the function EPMCONTEXTMEMBER in one cell, i.e:

=EPMCONTEXTMEMBER("Material") and this function is in the cell "B6", in other cell you put the function =EPMMEMBERPROPERTY("";B6;"LOB") (and this in the cell B7), this function return the value of the property that the planner select.

Finally you use the function =EPMDIMESIONOVERRIDE("000";"MATERIAL";"LOB="&B7).

With this functions you can accomplish your request.

Kind Regards

Alejandro