Skip to Content
0

Based on the current month, Need to fetch values from Category Dimension

Jan 20, 2017 at 01:31 AM

60

avatar image

How I can fetch "budget" values instead of "ACTUAL" , if the month is October in my EPM Report. Category is in Page Axis.

Dynamically I need to fetch "BM" or "ACTUAL". its monthly based forecasting report.

Regards,

DN

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
DN Kumar Jan 25, 2017 at 08:55 AM
0

Hello,

I have Used cell AF1 to get the 2016.09. in the cell AF8 I took the value.

(In the TIME dimension having property CURRENT containing 1,2,3 values. user is maintaining value 2 as under CURRENT month).

= EPMOlapMemberO(IF(AF1=2,EPMSelectMember(,"BM",,"CATEGORY"), EPMSelectMember(,"ACTUAL",,"CATEGORY")),"[CATEGORY].[PARENTH1].[ACTUAL]","Actual (ManAcc)","","001")

Its works... I am able to get BM / ACTUAL based on TIME dim CURRENT Property using the above formula.

Thanks for trying to help me.

Regards,

DN

Show 1 Share
10 |10000 characters needed characters left characters exceeded

= EPMOlapMemberO(IF(AF1=2,EPMSelectMember(,"BM",,"CATEGORY"), EPMSelectMember(,"ACTUAL",,"CATEGORY")),"[CATEGORY].[PARENTH1].[ACTUAL]","Actual (ManAcc)","","001")

Absolutely strange idea to place EPMSelectMember(,"BM",,"CATEGORY") inside EPMOlapMemberO!

Why not simply:

= EPMOlapMemberO(IF(AF1=2,"BM","ACTUAL"),"[CATEGORY].[PARENTH1].[ACTUAL]","Actual (ManAcc)","","001")

EPMSelectMember is used to provide user selection of the member, not to be used inside other formulas!

0
Priyanka Jagtap Jan 20, 2017 at 05:48 AM
0

Hi DN,

Can you please explain your requirement with an example in form of a report??? small representation! How do you need to fetch the categories based on month? the logic???

Have you tried working with Excel logics???

Regards,

JP

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Jan 20, 2017 at 09:15 AM
0

Please provide a screenshot of the desired report including comments on the time members required.

Share
10 |10000 characters needed characters left characters exceeded
DN Kumar Jan 23, 2017 at 03:42 AM
0

If we are in the Oct month close, the Input schedule should fetch the category as "BUDGET" values other wise it should be "actual" values. How can we keep the condition at category level dynamically based on running month. Category at page axis. Oct month close may happen in Nov also.

At present I keep a static formula,

IF(MONTH(TODAY())=9 <-- For oct close they may enter in Nov also

= EPMOlapMemberO(IF(MONTH(TODAY())=9,EPMSelectMember(,"BM",,"CATEGORY"), EPMSelectMember(,"ACTUAL",,"CATEGORY")),"[CATEGORY].[PARENTH1].[ACTUAL]","Actual (ManAcc)","","001")

second question is that (arrow mark in the fig), How can we lock/display mode in input schedule based on the month close. if we are in the Dec close, previous months should go into display/grayedout mode. how can we keep the condition based on the current month close.

Regards,

DN


w7sfk.png (30.7 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

The screenshot is not very helpfull....but to summarise...based on your current month the category should be "BUDGET" otherwise all the other months should be "ACTUAL". Is this correct?

0
Priyanka Jagtap Jan 23, 2017 at 07:00 AM
0
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Jan 23, 2017 at 08:05 AM
0

In general looks like you have static report for column axis. Please read my blog: https://blogs.sap.com/2013/03/01/static-report-in-bpc-nw-10/

You can use Excel formulas inside EPMOlapMemberO functions...

For gray formatting you can use Excel conditional formatting based on current month. You can also use Excel data validation to dynamically prevent data entry.

Share
10 |10000 characters needed characters left characters exceeded