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
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
= 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!
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
Please provide a screenshot of the desired report including comments on the time members required.
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
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?
Please go through below blog...https://blogs.sap.com/2012/07/22/how-to-build-rolling-forecasts-in-bpc-100/
Regards,
JP
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.