Skip to Content
avatar image
Former Member

Holding multiple member values in the BPC script logic variable

I have a requirement to split January month data in year4 and year5 and allocate equally to all the months in the year-This is for Budget and Forecast, users enter for year4 and year5 only in January month, this data will save in Input model, this amount I need to divide by 12 and allocate equally to all the months of the year while writing into reporting model.

Ex:-For Budget 2018 category user enters data in all the months from 2018 to 2020, for year4 which is 2021 they enter only in 01.2021, same for year5 01.2022. I maintained Year4 and Year5 as a property to Category dimension and maintained year info in that.

The below code is working fine while selecting only one category at a time in DM package, but if I select Budget2018 and Forecast2017 categories at a time it is not doing anything because year4 and year5 are different for these categories.

Is there a way to select different year4 and year5 in the variable for different categories?

*SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=$DEST_CAT$)
*SELECT(%4YEAR_MNTH%,ID,TIME,[ID]>=%YEAR4%.01 AND [ID]<=%YEAR4%.12)

*SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=$DEST_CAT$)
*SELECT(%5YEAR_MNTH%,ID,TIME,[ID]>=%YEAR5%.01 AND [ID]<=%YEAR5%.12)

*XDIM_MEMBERSET CATEGORY = $DEST_CAT$

*DESTINATION_APP =PROJECTREPORTING

*ADD_DIM COCODE = PROJECT:COCODE
*ADD_DIM COMMAREA = PROJECT:COMMAREA
*ADD_DIM EXPTYPE = PROJECT:EXPTYPE
*ADD_DIM PROJTYPE = PROJECT:PROJTYPE
*ADD_DIM RESPCC = PROJECT:RESPCC
*ADD_DIM PROJMGR = PROJECT:PROJMGR
*ADD_DIM ACCCLASS = PROJECT:ACCCLASS

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN

*WHEN TIME
*IS %4YEAR_MNTH%
*FOR %MONTH%=01,02,03,04,05,06,07,08,09,10,11,12
*REC(EXPRESSION=%VALUE%/12,CATEGORY=$DEST_CAT$,TIME="%YEAR4%.%MONTH%")
*NEXT
*ENDWHEN

*WHEN TIME
*IS %5YEAR_MNTH%
*FOR %MONTH%=01,02,03,04,05,06,07,08,09,10,11,12
*REC(EXPRESSION=%VALUE%/12,CATEGORY=$DEST_CAT$,TIME="%YEAR5%.%MONTH%")
*NEXT 
*ENDWHEN
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Best Answer
    Aug 02, 2017 at 06:01 AM

    Something like:

    *SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=$DEST_CAT$)
    *SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=$DEST_CAT$)
    
    *XDIM_MEMBERSET CATEGORY = $DEST_CAT$
    
    *DESTINATION_APP =PROJECTREPORTING
    
    *ADD_DIM COCODE = PROJECT:COCODE
    *ADD_DIM COMMAREA = PROJECT:COMMAREA
    *ADD_DIM EXPTYPE = PROJECT:EXPTYPE
    *ADD_DIM PROJTYPE = PROJECT:PROJTYPE
    *ADD_DIM RESPCC = PROJECT:RESPCC
    *ADD_DIM PROJMGR = PROJECT:PROJMGR
    *ADD_DIM ACCCLASS = PROJECT:ACCCLASS
    
    *WHEN CATEGORY
    *IS *
    *REC(EXPRESSION=%VALUE%)
    *ENDWHEN
    
    *FOR %Y4%=%YEAR4% AND %Y5%=%YEAR5%
    *XDIM_MEMBERSET TIME=%Y4%.01
    *WHEN TIME
    *IS *
    *FOR %MONTH%=01,02,03,04,05,06,07,08,09,10,11,12
    *REC(EXPRESSION=%VALUE%/12,CATEGORY=$DEST_CAT$,TIME=%Y4%.%MONTH%)
    *NEXT
    *ENDWHEN
    
    *XDIM_MEMBERSET TIME=%Y5%.01
    *WHEN TIME
    *IS *
    *FOR %MONTH%=01,02,03,04,05,06,07,08,09,10,11,12
    *REC(EXPRESSION=%VALUE%/12,CATEGORY=$DEST_CAT$,TIME=%Y5%.%MONTH%)
    *NEXT 
    *ENDWHEN
    *NEXT
    Add comment
    10|10000 characters needed characters exceeded

  • Aug 01, 2017 at 06:56 PM

    Not 100% clear, but if you select multiple categories then you have to use for/next loop for each category selected.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 01, 2017 at 10:09 PM

    Hello Vadim

    Do I need to hard code category in the script? for this year my budget category is BUD2018 and Forecast Category is FCSTQ32017, we are going to add new members when the year changes. In 2018 we will create categories as BUD2019 and FCSTQ12018, FCSTQ22018....

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 09, 2017 at 05:52 PM

    Hi Vadim-I doesn't see any text under LGX section, I executed as simulate mode in UJKT.Below is full log

    LGX:
    
    
    
    
    
    
    -------------------------------------------------------------------------------------------------------------------------------------
    LOG:
    
    
    FILE:\ROOT\WEBFOLDERS\ENABLE \ADMINAPP\PROJECTINPUT\TEST.LGF
    USER:90001789
    APPSET:ENABLE
    APPLICATION:PROJECTINPUT
    [INFO] GET_DIM_LIST(): I_APPL_ID="PROJECTINPUT", #dimensions=7 
    CATEGORY,COSTELEMENT,DATASRC,MEASURES,PO,PROJECT,TIME
    
    #dim_memberset=1 
    CATEGORY:BUD2018,FCSTQ32017,2 in total.
    REC :%VALUE%
    
    CALCULATION BEGIN:
    QUERY PROCESSING DATA
    QUERY TIME : 0.00 ms. 503  RECORDS QUERIED OUT.
    QUERY REFERENCE DATA
    CALCULATION TIME IN TOTAL :0.00 ms.
    503  RECORDS ARE GENERATED.
    CALCULATION END.
    [INFO] GET_DIM_LIST(): I_APPL_ID="PROJECTINPUT", #dimensions=7 
    CATEGORY,COSTELEMENT,DATASRC,MEASURES,PO,PROJECT,TIME
    
    #dim_memberset=2 
    CATEGORY:BUD2018,FCSTQ32017,2 in total.
    TIME:2021.01,1 in total.
    
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    REC :%VALUE%/12
    
    CALCULATION BEGIN:
    QUERY PROCESSING DATA
    QUERY TIME : 1.00 ms. 7  RECORDS QUERIED OUT.
    QUERY REFERENCE DATA
    CALCULATION TIME IN TOTAL :0.00 ms.
    84  RECORDS ARE GENERATED.
    CALCULATION END.
    
    UJK_VALIDATION_EXCEPTION:Invalid when/endwhen - Line #21 : Invalid when/endwhen
    ----------------------------------------------------------------------------------------------
    Script I used in UJKT--
    
    *SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=BUD2018,FCSTQ32017)
    *SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=BUD2018,FCSTQ32017)
    
    *XDIM_MEMBERSET CATEGORY = BUD2018,FCSTQ32017
    *DESTINATION_APP = PROJECTREPORTING
    
    *ADD_DIM COCODE = PROJECT:COCODE
    *ADD_DIM COMMAREA = PROJECT:COMMAREA
    *ADD_DIM EXPTYPE = PROJECT:EXPTYPE
    *ADD_DIM PROJTYPE = PROJECT:PROJTYPE
    *ADD_DIM RESPCC = PROJECT:RESPCC
    *ADD_DIM PROJMGR = PROJECT:PROJMGR
    *ADD_DIM ACCCLASS = PROJECT:ACCCLASS
    *ADD_DIM ANALYST = PROJECT:ANALYST
    
    *WHEN CATEGORY
    *IS *
    *REC(EXPRESSION=%VALUE%)
    *ENDWHEN
    
    *FOR %Y4%=%YEAR4% AND %Y5%=%YEAR5%
    *XDIM_MEMBERSET TIME=%Y4%.01
    *WHEN TIME
    *IS *
    *FOR %MONTH% =01,02,03,04,05,06,07,08,09,10,11,12
    *REC(EXPRESSION=%VALUE%/12,CATEGORY=CATEGORY,TIME=%Y4%.%MONTH%)
    *NEXT
    *ENDWHEN
    
    *XDIM_MEMBERSET TIME=%Y5%.01
    *WHEN TIME
    *IS *
    *FOR %MONTH1%=01,02,03,04,05,06,07,08,09,10,11,12
    *REC(EXPRESSION=%VALUE%/12,CATEGORY=CATEGORY,TIME=%Y5%.%MONTH1%)
    *NEXT
    *ENDWHEN
    *NEXT
    
    Add comment
    10|10000 characters needed characters exceeded

  • Aug 09, 2017 at 07:35 AM

    I am unable to reply to your last post, answering here!

    Please provide full UJKT log with LGX text - not shown in your previous posts.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 11, 2017 at 07:36 PM

    Have to repeat - there is no when/endwhen in my last test code. Please restart the ujkt transaction and validate again.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 11, 2017 at 07:36 PM

    Have to repeat - there is no when/endwhen in my last test code. Please restart the ujkt transaction and validate again.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 11, 2017 at 07:47 PM

    Restarted UJKT transaction and validated the code, attached validation screenshot.

    validation.jpg

    Add comment
    10|10000 characters needed characters exceeded