Skip to Content
avatar image
Former Member

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

Jan 20, 2017 at 01:31 AM | 61 Views

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

EPM Add-In for Excel
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 25, 2017 at 08:55 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Vadim Kalinin
      Jan 25, 2017 at 09:05 AM

      = 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!

  • avatar image
    Former Member
    Jan 20, 2017 at 05:48 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Vadim Kalinin
    Jan 20, 2017 at 09:15 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 23, 2017 at 03:42 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Jan 23, 2017 at 06:59 AM

      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?

  • avatar image
    Former Member
    Jan 23, 2017 at 07:00 AM

    Please go through below blog...https://blogs.sap.com/2012/07/22/how-to-build-rolling-forecasts-in-bpc-100/

    Regards,

    JP

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Vadim Kalinin
    Jan 23, 2017 at 08:05 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded