Hi experts,
I am creating an input schedule where I want to put several dimensions in the row axis. I have 2 dimensions, PROJECT and FUNCTION, which have a one to many relationship. 1 FUNCTION is linked to many PROJECTS. I have therefore created FUNCTION as a property of PROJECT.
My requirement is when I select FUNCTION, the input/report should show all the PROJECT members associated with that FUNCTION in the rows.
Please help me achieve this
Regards
Jordan
For multiple FUNCTIONS look on my example:
I have COMPANY_CODE and COST_CENTER with a property BPC_COMPANY_CODE
First I create report with both COMPANY_CODE and COST_CENTER in row axis.
Then in member selector for COMPANY code I use:
And with EPMDimensionOverride I have report for 2 COMPANY_CODE members:
You can use Excel formulas to create the override condition like: "BPC_COMPANY_CODE=CO_BR00 OR BPC_COMPANY_CODE=CO_AU00"
Hi Vadim,
Your example is very understandable and I have tried to copy it step by step but when I refresh the sheet, the rows go blank. Will keep analyzing it but I dont know where I am having it wrong
Many thanks
Jordan
Please test the report without override - do you see correct function for selected projects?
Please use insert image instead of attaching file - very hard to read!
Thanks Vadim,
It works for the selected PROJECT without Epmdimensionoverride. Thank you very much.
One more thing, how do I ensure that the same FUNCTION is not repeated in the rows. See highlighted FUNCTION in the image.
Regards
Jordan
Now try with Epmdimensionoverride
Your function parameters were incorrect, has to be:
=EPMDimensionOverride("000";"PROJECT";"FUNCTION_CLASS=101011 OR FUNCTION_CLASS=104010")
This statement will select only FUCTION: 101011 and 104010
Hi Vadim,
Once I refresh after override the rows disappear, don't know why. See screenshots below
Test with simply:
=EPMDimensionOverride("000";"PROJECT";"FUNCTION_CLASS=101011")
It has to work! And it's working in my system without issues!
Thanks Vadim
It was the spacing in the formula. It works perfect. For it to be neat I dont want to repeat the same values of FUNCTION. How do I do this?
Regards
Jordan
"I dont want to repeat the same values of FUNCTION" - in edit report options you have "Repeat Row headers"
Easy:
If you need to select only one function then you can use EPMDimensionOverride to override PROJECT dimension using FUNCTION property.
If you want to select multiple functions then you can establish relation between function and project in the function member selector (function = property function of project). Also use EPMDimensionOverride to override PROJECT dimension using FUNCTION property (multiple properties).
Hi Vadim,
Please give me more detail on this using my example for both single and multiple selections of FUNCTION. FUNCTION is selected through EPM Context and PROJECT is in the row axis. So the corresponding PROJECTS (PROJECT1,2 and 3) should then appear in the rows after selection of FUNCTION1. FUNCTION is a property of PROJECT.
Regards
Jordan
EPMContextMember will select only single FUNCTION :)
Let's assume you have in A1
=EPMContextMember(,"FUNCTION",,TRUE)
to select single FUNCTION member.
Then in A2 write (for default report 000):
=EPMDimensionOverride("000","PROJECT","FUNCTION="&$A$1)
Hi Jordan
Function can be given as selection in the context depending on the function selected Epmdimensionoverride for PROJECT will display many projects which are relavent to that function.
Example:
If Function is given as selection in c9 cell as =epmcontextmember(,Function)
if selection is Function1
Selected member in this case Function1 should be passed to dimension override function as below
=EPMDimensionOverride("000","PROJECT","'$C$9'")
Row axis will be filtered by Function1 relavent projects.
Other way:
This can also be done using linking Function property to PROJECT dimension.
Hope this helps for your requirement. Thank you
Regards,
Durgaprasadh
Strange idea to repeat my answer! But with mistakes in the function parameters - will not work!
Nothing Strange it will take time to type the answer. Its all about helping Jordan. Thanks
It's better to test before posting if you are not 100% sure. Look on the correct function parameters in my comment!
Hi Durgaprasadh,
Thanks for the response and example. When I use EPMDimensionOverride the result in the rows is "Expansion of PROJECT Overriden" instead of having the actual PROJECT members. As you have explained, I want to select FUNCTION1 in the context, then all the PROJECTS for that function appear in the rows. There is a one to many relationship so I maintained FUNCTION as a property of PROJECT.
Regards
Jordan