Question Re: BPC Dimensions and Properties in EPM reports in Financial Management Q&A

BPC Dimensions and Properties in EPM reports

former_member246680 — Wed, 10 Jan 2018 19:56:10 GMT

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

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Wed, 10 Jan 2018 20:45:34 GMT

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).

Re: BPC Dimensions and Properties in EPM reports

former_member190109 — Wed, 10 Jan 2018 20:57:04 GMT

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

Re: BPC Dimensions and Properties in EPM reports

former_member246680 — Wed, 10 Jan 2018 21:25:03 GMT

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

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Wed, 10 Jan 2018 21:27:05 GMT

Strange idea to repeat my answer! But with mistakes in the function parameters - will not work!

Re: BPC Dimensions and Properties in EPM reports

former_member190109 — Wed, 10 Jan 2018 21:31:26 GMT

Nothing Strange it will take time to type the answer. Its all about helping Jordan. Thanks

Re: BPC Dimensions and Properties in EPM reports

former_member246680 — Wed, 10 Jan 2018 21:33:17 GMT

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

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Wed, 10 Jan 2018 21:35:57 GMT

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)

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Wed, 10 Jan 2018 21:38:17 GMT

It's better to test before posting if you are not 100% sure. Look on the correct function parameters in my comment!

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Wed, 10 Jan 2018 21:57:00 GMT

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"

Re: BPC Dimensions and Properties in EPM reports

former_member246680 — Wed, 10 Jan 2018 22:34:31 GMT

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

pi2.png

Many thanks

Jordan

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Wed, 10 Jan 2018 23:03:51 GMT

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!

Re: BPC Dimensions and Properties in EPM reports

former_member246680 — Thu, 11 Jan 2018 05:19:10 GMT

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

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Thu, 11 Jan 2018 10:03:49 GMT

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

Re: BPC Dimensions and Properties in EPM reports

former_member246680 — Thu, 11 Jan 2018 11:58:17 GMT

Hi Vadim,

Once I refresh after override the rows disappear, don't know why. See screenshots below

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Thu, 11 Jan 2018 12:08:00 GMT

Test with simply:

=EPMDimensionOverride("000";"PROJECT";"FUNCTION_CLASS=101011")

It has to work! And it's working in my system without issues!

Re: BPC Dimensions and Properties in EPM reports

former_member246680 — Thu, 11 Jan 2018 12:20:49 GMT

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

Re: BPC Dimensions and Properties in EPM reports

former_member186338 — Thu, 11 Jan 2018 13:13:05 GMT

"I dont want to repeat the same values of FUNCTION" - in edit report options you have "Repeat Row headers"

Re: BPC Dimensions and Properties in EPM reports

former_member246680 — Thu, 11 Jan 2018 13:30:21 GMT

You are awesome THANKS!