dynamic column axis based on property of page axis dimension

[former_member199935]

Hi,

i've a request to filter the column axis based on "property of a dim member" selected in page axis.

for example, we've following scenario.

Mat_A (Material) is manufactured only in PLT_A (plant).

Mat_B (Material) is manufactured only in PLT_B (plant).

Mat_C (Material) is manufactured only in PLT_C (plant).

Plant dimension members are as follows

PLT_A

PLT_B

PLT_C

Material dimension members are as follows. I added as many properties as that of plants we've to Material dimension.

Mat        PLT_A      PLT_B      PLT_C
MAT_A      X
MAT_B                     X
MAT_C                                    X

Now, my EPM report is

Page Axis      : Plant selection

Row Axis        : Time

Column Axis      : Material

If user selects PLT_A in page axis for plant, i'm looking for a following report.

Plant : PLT_A

               MATERIAL
TIME       MAT_A
Q3           10
Q4            20

If user selects PLT_B in page axis for plant, i'm looking for a following report.

Plant : PLT_B

               MATERIAL
TIME       MAT_B
Q3           15
Q4           25

How could i achieve this. Any inputs greatly appreciated.

[former_member186338 · Accepted solution]

If "manufactured only" in single plant then you don't need multiple properties.

Use property PLANT in material dimension filled with Plant ID (PLT_A...)

Then use EPMDimensionOverride for material based on property - equal to Plant ID.

Vadim

[former_member199935 · Accepted solution]

thanks Vadim for the reply. But, same material could be manufactured in multiple plants hence i created multiple properties. in that case, how could it be achieved?

[former_member186338 · Accepted solution]

It's another question

Sorry, multiple properties is not a good solution - only for very limited number of plants... etc...

For a small number of plants use:

=EPMDimensionOverride("000","Material",$B$4&"=X")

where $B$4 is a cell on the page axis containing ID of Plant

The universal solution for many to many relation can be:

Vadim

[Former Member]

Hi,

I have a solution for you, but the solution will work for below scenario only :

•      In the Page Axis, You have to select only one Plant member at a time. The below scenario will not work for multiple plant selection.

Solution:

Step 1: Under Plant Dimension create a Property to capture the list of all Materials. Let's the name of             property is "MAT_LIST".

Step 2: Go to your report in Excel. Use below formula to capture the list of Materials relevant to the           selected Plant.

          Syntax :   =EPMMemberProperty("000", <Plant Name>, "MAT_LIST")

               <Plant Name> : Choss the cell that refers to the Page Axis Selection for Plant

Step 3: Use "=EPMDimensionOverride("000","Material",<Cell with 'EPMMemberProperty' formula>)" to update the list of Materials.

NOTE: If you have not populated the value of "MAT_LIST" property for all Plant members, then you can use below formula in place of the formula mentioned in the step 2.

          =IF(EPMMemberProperty("000", <Plant Name>, "MAT_LIST")="",<Dummy Material>,

               EPMMemberProperty("000", <Plant Name>, "MAT_LIST"))

Hope this will help to achieve your scenario.

Shub

[former_member186338]

Hi Shub,

In the real life we are talking about the list with 10000... materials for each plant... Do you think you proposal is realistic?

Vadim

[Former Member]

Hi Valdim,

At some cases, instead of listing out all 10000..base members, we can mention BAS(<parent>). In this was we can at least decrease the list up to some extent.

But in this way :

1. Technically we will be able to achieve the current scenario

2. In this approach, we have to create only one property.

Shub

[former_member199935]

Shub,

your answer seems to fit my needs. Just so you know, for our specific project, we are dealing with 8 plants and only 90 materials at the moment (not the entire material master).

But, I'm still not clear on your Step 3.

I got Step 1 and Step 2. But, am still dubious on how i can achieve Step 3. Hopefully you can explain in a more detail.

What should I populate for MAT_LIST property for each plant? as 

We've 90 materials manufactured in Plant A.

We've 56 materials manufactured in Plant B.

We've 35 materials manufactured in Plant C.

How does Materials get restricted (using epmaxisoverride) to those only manufactured in a given plant (page axis) without actually populating/creating a link between Material and Plant in "MATERIAL" dimension?

[former_member186338]

Even 90 materials as a comma separated list of ID's in a single cell (property) can be an issue... but you can try

Vadim

[Former Member]

Hi,

As per your requirement, I tried to design an example. Please check below for more explanation.

How to maintain "MAT_LIST" property:

Lets say I have a material dimension as below :

                        

I have a Plant dimension and Plants are related to Materials as mentioned in the comment column in the below screen

         

NOTE : Here you can see, sometimes I used BAS(<parent member>) instead of listing out all base member names to reduce length of my string.

How does Materials get restricted to those only manufactured in a given plant (page axis) ?:

- 'MAT_LIST' property is used to establish the link between Plant and Material, where we listed out all relevant Material IDs (Use BAS(..) to reduce the list)

- At report level, use EPMMemberProperty() command to list out the name of relevant Materials to the plant selected from Page Axis. as explained in Step 2 in my 1st post.

     Example :

          Let's say I have written the EPMMemberProperty() formula in cell "C3" and in the Page Axis,           I selected PLANT_A as my plant.

          Then automatically value in C3 cell will be  "BAS(MAT_1), BAS(MAT_2)"

          Now lets say I have written EPMDimensionOverride() command in cell 'C4' to overrides the member selection for Material Dimension based on the values in 'C3' cell.

                         =EPMDimensionOverride("000","Material",C3)

    Conclusion:

               As C3  cell contains 'BAS(MAT_1), BAS(MAT_2)' and in C4 cell we have mentioned EPMDimensionOverride() command, So if we refresh our report after selecting PLANT_A as our plant, then the report will automatically update the material dimension as per the list of C3 Cell.

Hope now you are able to understand how step 3 works and relation between material and Plant gets established.

Let me know, if you need any further help.

Whatever  Vadim mentioned in his last mail, that's true. but to overcome that we can use BAS() command. Otherwise it's a very tougher task to maintain a such huge list.

Thanks

Shub

[former_member199935]

thanks Shub for your detailed explanation. it did the trick. (didn't know dim property can hold 3000+ characters). Finally, i got what am looking for. I see following minor downsides with this but i can live with it.

1. every time i refresh report, EPM log is recording following message

"Unexpected  '=' at position 1 in the expression [=BAS(MAT_1), BAS(MAT_2) etc

2. in case a new material is added, it may not be easy for business administrator (non-IT) updating these property values.

Once again, thanks for the solution.

[former_member186338]

1. May be you have =BAS(... instead of simply BAS(..

2. The hierarchy usage can work only in very specific case and the update will be terrible!

Have you look on the link I have provided at the beginning?

Vadim

[former_member199935]

i came across an issue with the solution that was discussed in this thread. As i'm building input Schedule, EPMDIMENSIONOVERRIDE is not working to my needs.

it works fine for reports when it has data but looks like not for input schedules working with blank forms.

dimoverride is working only if i select "remove empty and zero values" for columns when input schedule already has data.

if i select "Keep All" columns, Input Schedule (with blank data) is keeping all materials not the ones only needed from dimoverride.

if i select "remove empty and zero values" columns, Input Schedule (with blank data) is not showing any columns.

Vadim,

to your post, if i remove "=" from dim property, epmdimensionoverride is not working even if it has data.

yes, i looked at thread on BOM and i may need some time to fully understand how i can use that solution for my current needs

[Former Member]

Hi BPC tech,

Whatever Valdim said that is correct, we should keep "=" in the dimesion Property. Because the syntax of epmdimensionoverride is,

        Syntax     :      =EPMDimensionOverride(<reportID>,<dimension Name>,<members>)

          Example   :     Update Selection Material to 'CH_MAT_1_1' & 'CH_MAT_1_2' members

                    =EPMDimensionOverride("000","Material","CH_MAT_1_1, CH_MAT_1_2")

In our case, we are referring to cell(C3) for <members>. So as per the rule

• If C3 cell has some value, then after refresh, Material will be updated automatically after mentioning the above EPMDimensionOverride() function.
• if C3 cell is blank, then after refresh there will be no change in Material

So i think in your case, In the input form you are selecting a Plant for which MAT_LIST is blank. So the above function is not working.

So in such Scenario I will suggest you to update the formula of C3 cell as below :

          =IF(EPMMemberProperty("000", <Plant Name>, "MAT_LIST")="",<Dummy Material>,

               EPMMemberProperty("000", <Plant Name>, "MAT_LIST"))

That means, if MAT_LIST is blank for any Plant, then in C3 cell the value will be a <Dummy Material> for that Plant. So C3 Cell will be never blank.

If after following all above steps, still you are facing any issue, then please share the details of steps that you are following now, along with the screen shots. So that I can help you further.

Thanks,

Shub

[former_member199935]

Shub,

i got it working but i've a UI issue.

i'm selecting the plant let's say in cell A5. if i double click on this cell and change to a different plant that has values for MAT_LIST, EPM says "executing/processing/writing/restoring inputs" and refreshing the input template but the columns are not getting restricted with MAT_LIST.

if i click on a macro button that calls "EPMExecuteAPI", "RefreshActiveSheet", it is working fine. is there a way i can get this working immediately from double click (change Plant) of a cell value?

[former_member186338]

No, use macro button!

[anil_agrawal]

Hi ,

To create filter in the column axis based on  "property of a dim member" selected in page axis,You need to create a property of Plant ID in Material Dimension .

Then you have to use the EPM function "EPMDIMENSIONPROPERTYVALUES" and define the parameters for the property Plant_ID .

Then as suggested by Vadim use "EPMDIMENSIONOVERRIDE" Function and concatenate it with the dropdown list created by the dimension property value function. Don't forget to read the member of page axis from the list created by "EPMDIMENSIONPROPERTYVALUES".

Anil

[former_member186338]

Hi Anil,

Please read once again the topic author statement:

He want to have many to many relationship and a single property will not work! Sorry!

And I do not understand what for you need EPMDIMENSIONPROPERTYVALUES?

Vadim