cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Holding multiple member values in the BPC script logic variable

Go to solution
former_member488614
Participant

on ‎08-01-2017 7:42 PM

0 Kudos

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
Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
‎08-02-2017 7:01 AM
0 Kudos

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
Show replies
Show replies
former_member488614
Participant
‎08-03-2017 2:26 AM
0 Kudos

Hi Vadim-I used this code and I got the error message like Invalid when/endwhen-line #151 which is

*FOR %Y4%=%YEAR4% AND %Y5%=%YEAR5%.

I moved the bottom *NEXT statement to top like below, then it is validated successfully. while running DM package I selected BUD2018 and FCSTQ32017.

*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 
*NEXT 

*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

then package failed with error message RUN_LOGIC: Unknown Dimension Name in Keyword: "FCSTQ32017:

FYI-

for BUD2018 year4 is 2021 and year5 is 2022

for FCSTQ32017 year4 is 2020 and year5 is 2021

we are maintaining year4 and year5 data only for specific categories.

UJKT log<br>
LGX:
-------------------------------------------------------------------------------------------------------------------------------------
LOG:


FILE:\ROOT\WEBFOLDERS\ENABLE \ADMINAPP\PROJECTINPUT\TEST.LGF
USER:90001200
APPSET:ENABLE
APPLICATION:PROJECTINPUT
[INFO] GET_DIM_LIST(): I_APPL_ID="PROJECTINPUT", #dimensions=7 
CATEGORY,COSTELEMENT,DATASRC,MEASURES,PO,PROJECT,TIME


#dim_memberset=2 
CATEGORY:$DEST_CAT$,1 in total.
PROJECT:E000001,E00000101,E00000102,E00000103,E00000104,...37631 in total.


REC :%VALUE%


CALCULATION BEGIN:
QUERY PROCESSING DATA


UJO_READ:Members invalid On Dimension(CATEGORY)


I doubled checkd members in CATEGORY dimension, the selected members are available.
former_member186338
Active Contributor
‎08-03-2017 7:26 AM
0 Kudos

"I moved the bottom *NEXT statement to top like below" - absolutely incorrect idea!

Script has to be:

*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

To test try to execute in UJKT:

*SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=BUD2018,FCSTQ32017)
*SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=BUD2018,FCSTQ32017)
*FOR %Y4%=%YEAR4% AND %Y5%=%YEAR5% 
*XDIM_MEMBERSET TIME=%Y4%.01 
*XDIM_MEMBERSET TIME=%Y5%.01
*NEXT
former_member488614
Participant
‎08-03-2017 7:10 PM
0 Kudos

I tested your last piece of code in UJKT and it is working fine, as you suggested I moved *NEXT statement back to bottom, but when I validating our main logic it is still saying Invalid when/endwhen at line *FOR %Y4%=%YEAR4% AND %Y5%=%YEAR5% , looks like we are missing a small piece at *FOR *NEXT or *WHEN *ENDWHEN

0 RECORDS HAVE BEEN WRITTEN BACK. WRITING TIME :0.00 ms.

UJK_VALIDATION_EXCEPTION:Invalid when/endwhen - Line #84 : Invalid when/endwhen

former_member186338
Active Contributor
‎08-03-2017 7:15 PM
0 Kudos

Sorry, please post full code and full UJKT log!

You are doing something wrong!

P.S. Try to use 2 different variables %MONTH% and %MONTH1%:

*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 %MONTH1%=01,02,03,04,05,06,07,08,09,10,11,12
*REC(EXPRESSION=%VALUE%/12,CATEGORY=$DEST_CAT$,TIME=%Y5%.%MONTH1%) 
*NEXT
*ENDWHEN
*NEXT
former_member488614
Participant
‎08-03-2017 7:59 PM
0 Kudos

Here is my full code and UJKT Log

*START_BADI RUNLOGIC_PH
QUERY=OFF
WRITE=ON
APP=PROJECTREPORTING
LOGIC=CLEAR.LGF
DIMENSION ACCCLASS=<ALL>
DIMENSION ANALYST=<ALL>
DIMENSION COCODE=<ALL>
DIMENSION EXPTYPE=<ALL>
DIMENSION PROJTYPE=<ALL>
DIMENSION RESPCC=<ALL>
DIMENSION PROJMGR=<ALL>
DIMENSION COMMAREA=<ALL>
DIMENSION CATEORY=$DEST_CAT$

*END_BADI 
*SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=$DEST_CAT$)
*SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=$DEST_CAT$)

*SELECT(%WITHAC%,ID,PROJECT,"[ACCCLASS]<>''")
*SELECT(%WITHOUTAC%,ID,PROJECT,"[ACCCLASS] = ''")
*SELECT(%WITHPM%,ID,PROJECT,"[PROJMGR]<>''")
*SELECT(%WITHOUTPM%,ID,PROJECT,"[PROJMGR] = ''")
*SELECT(%WITHCC%,ID,PROJECT,"[COCODE]<>''")
*SELECT(%WITHOUTCC%,ID,PROJECT,"[COCODE] = ''")
*SELECT(%WITHCA%,ID,PROJECT,"[COMMAREA]<>''")
*SELECT(%WITHOUTCA%,ID,PROJECT,"[COMMAREA] = ''")
*SELECT(%WITHRC%,ID,PROJECT,"[RESPCC]<>''")
*SELECT(%WITHOUTRC%,ID,PROJECT,"[RESPCC] = ''")
*SELECT(%WITHPT%,ID,PROJECT,"[PROJTYPE]<>''")
*SELECT(%WITHOUTPT%,ID,PROJECT,"[RESPCC] = ''")
*SELECT(%WITHET%,ID,PROJECT,"[EXPTYPE]<>''")
*SELECT(%WITHOUTET%,ID,PROJECT,"[EXPTYPE] = ''")
*SELECT(%WITHAN%,ID,PROJECT,"[ANALYST]<>''")
*SELECT(%WITHOUTAN%,ID,PROJECT,"[ANALYST] = ''")

*XDIM_MEMBERSET CATEGORY = $DEST_CAT$
*XDIM_MEMBERSET PROJECT = %WITHAC%
*XDIM_MEMBERSET PROJECT = %WITHPM%
*XDIM_MEMBERSET PROJECT = %WITHCC%
*XDIM_MEMBERSET PROJECT = %WITHCA%
*XDIM_MEMBERSET PROJECT = %WITHRC%
*XDIM_MEMBERSET PROJECT = %WITHPT%
*XDIM_MEMBERSET PROJECT = %WITHET%
*XDIM_MEMBERSET PROJECT = %WITHAN%

*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
*XDIM_MEMBERSET PROJECT = %WITHOUTAC% 
*XDIM_MEMBERSET PROJECT = %WITHOUTPM%
*XDIM_MEMBERSET PROJECT = %WITHOUTCC%
*XDIM_MEMBERSET PROJECT = %WITHOUTCA%
*XDIM_MEMBERSET PROJECT = %WITHOUTRC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPT%
*XDIM_MEMBERSET PROJECT = %WITHOUTET%
*XDIM_MEMBERSET PROJECT = %WITHOUTAN%

*DESTINATION_APP = PROJECTREPORTING
//We have a scenario where all properties are balank so this code is working fine.no need to check combinations.
*ADD_DIM COCODE = COC_UNASSIGNED
*ADD_DIM COMMAREA = CA_UNASSIGNED
*ADD_DIM EXPTYPE = ET_UNASSIGNED
*ADD_DIM PROJTYPE = PT_UNASSIGNED
*ADD_DIM RESPCC = RCC_UNASSIGNED
*ADD_DIM PROJMGR = PM_UNASSIGNED
*ADD_DIM ACCCLASS = AC_UNASSIGNED
*ADD_DIM ANALYST = AN_UNASSIGNED

*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 %MONTH1%=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
----------------------
I put below code in UJKT with hardcoding categories.

*SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=BUD2018,FCSTQ32017)
*SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=BUD2018,FCSTQ32017)

*SELECT(%WITHAC%,ID,PROJECT,"[ACCCLASS]<>''")
*SELECT(%WITHOUTAC%,ID,PROJECT,"[ACCCLASS] = ''")
*SELECT(%WITHPM%,ID,PROJECT,"[PROJMGR]<>''")
*SELECT(%WITHOUTPM%,ID,PROJECT,"[PROJMGR] = ''")
*SELECT(%WITHCC%,ID,PROJECT,"[COCODE]<>''")
*SELECT(%WITHOUTCC%,ID,PROJECT,"[COCODE] = ''")
*SELECT(%WITHCA%,ID,PROJECT,"[COMMAREA]<>''")
*SELECT(%WITHOUTCA%,ID,PROJECT,"[COMMAREA] = ''")
*SELECT(%WITHRC%,ID,PROJECT,"[RESPCC]<>''")
*SELECT(%WITHOUTRC%,ID,PROJECT,"[RESPCC] = ''")
*SELECT(%WITHPT%,ID,PROJECT,"[PROJTYPE]<>''")
*SELECT(%WITHOUTPT%,ID,PROJECT,"[RESPCC] = ''")
*SELECT(%WITHET%,ID,PROJECT,"[EXPTYPE]<>''")
*SELECT(%WITHOUTET%,ID,PROJECT,"[EXPTYPE] = ''")
*SELECT(%WITHAN%,ID,PROJECT,"[ANALYST]<>''")
*SELECT(%WITHOUTAN%,ID,PROJECT,"[ANALYST] = ''")

*XDIM_MEMBERSET PROJECT = %WITHAC%
*XDIM_MEMBERSET PROJECT = %WITHPM%
*XDIM_MEMBERSET PROJECT = %WITHCC%
*XDIM_MEMBERSET PROJECT = %WITHCA%
*XDIM_MEMBERSET PROJECT = %WITHRC%
*XDIM_MEMBERSET PROJECT = %WITHPT%
*XDIM_MEMBERSET PROJECT = %WITHET%
*XDIM_MEMBERSET PROJECT = %WITHAN%

*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
*XDIM_MEMBERSET PROJECT = %WITHOUTAC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPM%
*XDIM_MEMBERSET PROJECT = %WITHOUTCC%
*XDIM_MEMBERSET PROJECT = %WITHOUTCA%
*XDIM_MEMBERSET PROJECT = %WITHOUTRC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPT%
*XDIM_MEMBERSET PROJECT = %WITHOUTET%
*XDIM_MEMBERSET PROJECT = %WITHOUTAN%

*DESTINATION_APP = PROJECTREPORTING

*ADD_DIM COCODE = COC_UNASSIGNED
*ADD_DIM COMMAREA = CA_UNASSIGNED
*ADD_DIM EXPTYPE = ET_UNASSIGNED
*ADD_DIM PROJTYPE = PT_UNASSIGNED
*ADD_DIM RESPCC = RCC_UNASSIGNED
*ADD_DIM PROJMGR = PM_UNASSIGNED
*ADD_DIM ACCCLASS = AC_UNASSIGNED
*ADD_DIM ANALYST = AN_UNASSIGNED

*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%.%MONTH%)
*NEXT
*ENDWHEN
*NEXT

Below is full UJKT log-
LGX:
-------------------------------------------------------------------------------------------------------------------------------------
LOG:
FILE:\ROOT\WEBFOLDERS\ENABLE \ADMINAPP\PROJECTINPUT\TEST.LGF
USER:90001200
APPSET:ENABLE
APPLICATION:PROJECTINPUT
[INFO] GET_DIM_LIST(): I_APPL_ID="PROJECTINPUT", #dimensions=7 
CATEGORY,COSTELEMENT,DATASRC,MEASURES,PO,PROJECT,TIME

#dim_memberset=1 
PROJECT:E000001,E00000101,E00000102,E00000103,E00000104,...37631 in total.

REC :%VALUE%

CALCULATION BEGIN:
QUERY PROCESSING DATA
QUERY TIME : 8.00 ms. 76701  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
CALCULATION TIME IN TOTAL :9.00 ms.
76701  RECORDS ARE GENERATED.
CALCULATION END.
[INFO] GET_DIM_LIST(): I_APPL_ID="PROJECTINPUT", #dimensions=7 
CATEGORY,COSTELEMENT,DATASRC,MEASURES,PO,PROJECT,TIME

#dim_memberset=1 
PROJECT:A000001,A002947,A003053,A003102,A003113,...13416 in total.
REC :%VALUE%
CALCULATION BEGIN:
QUERY PROCESSING DATA
QUERY TIME : 1.00 ms. 700  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
CALCULATION TIME IN TOTAL :0.00 ms.
700  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 
PROJECT:A000001,A002947,A003053,A003102,A003113,...13416 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 : 0.00 ms. 12  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
CALCULATION TIME IN TOTAL :2.00 ms.
144  RECORDS ARE GENERATED.
CALCULATION END.

UJK_VALIDATION_EXCEPTION:Invalid when/endwhen - Line #70 : Invalid when/endwhen
former_member186338
Active Contributor
‎08-03-2017 8:10 PM
0 Kudos

Sorry, but what is this:

*XDIM_MEMBERSET PROJECT = %WITHOUTAC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPM%
*XDIM_MEMBERSET PROJECT = %WITHOUTCC%
*XDIM_MEMBERSET PROJECT = %WITHOUTCA%
*XDIM_MEMBERSET PROJECT = %WITHOUTRC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPT%
*XDIM_MEMBERSET PROJECT = %WITHOUTET%
*XDIM_MEMBERSET PROJECT = %WITHOUTAN%

Do you understand what you are doing??????? I have already told you that it's a meaningless code!
former_member186338
Active Contributor
‎08-03-2017 8:22 PM
0 Kudos

P.S. AND in the second when/endwhen you forget to change variable in REC - still MONTH instead of MONTH1. Please be accurate!

former_member488614
Participant
‎08-03-2017 9:28 PM
0 Kudos

Sorry, but for BUD2018 and FCSTQ32017 all the properties in the input model are blank so I am updating as UNASSIGNED in reporting model to avoid error while writing to the target.Once this project go live, they will enter some values using Masterdata on the fly concept will develop some Badi to get rid of this code.till then I am just using for temperory solution.

former_member488614
Participant
‎08-03-2017 9:35 PM
0 Kudos

Sorry, I missed that, I just changed that code, still, I see the same issue, below is my UJKT code and Log I executed in Simulate mode.

BTW I commented blank properties code.

New code

*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
--------
UJKT LOG
LOG:
FILE:\ROOT\WEBFOLDERS\ENABLE \ADMINAPP\PROJECTINPUT\TEST.LGF
USER:90001200
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 : 1.00 ms. 498  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
CALCULATION TIME IN TOTAL :0.00 ms.
498  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 : 0.00 ms. 6  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
CALCULATION TIME IN TOTAL :0.00 ms.
72  RECORDS ARE GENERATED.
CALCULATION END.
UJK_VALIDATION_EXCEPTION:Invalid when/endwhen - Line #22 : Invalid when/endwhen
former_member488614
Participant
‎08-08-2017 8:20 PM
0 Kudos

Hello Vadim, did you get a chance to check this? thank you.

Answers (7)

Answers (7)

former_member488614
Participant
‎08-11-2017 8:47 PM
0 Kudos

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

validation.jpg

Show replies
Show replies
former_member186338
Active Contributor
‎08-11-2017 9:14 PM
0 Kudos

In my system this code is validating...

Try instead of MONTH AND MONTH1

M1 AND M2

What is your bpc version and SP?

former_member488614
Participant
‎08-11-2017 10:58 PM
0 Kudos

Changed variable names but same issue. bpc version is 10.0 and SP 8.

Do we need to change the code based on by bpc version and sp level?, if it is not going to work then I will ask the client to enter only one category at a time or upgrade bpc to latest version if it is related to version.Thanks for your time and patience on this.

validation.jpg

system-details.jpg

former_member186338
Active Contributor
‎08-12-2017 9:12 AM
0 Kudos

You have SP 10 of bpc 801 - very old. Upgrade required.

former_member488614
Participant
‎08-12-2017 6:52 PM
0 Kudos

Ok, I will check with client on this, I am closing this thread then accepting as helpful answer.

former_member186338
Active Contributor
‎08-11-2017 8:36 PM
0 Kudos

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

Show replies
Show replies
former_member186338
Active Contributor
‎08-11-2017 8:36 PM
0 Kudos

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

Show replies
Show replies
former_member488614
Participant
‎08-09-2017 6:52 PM
0 Kudos

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
Show replies
Show replies
former_member186338
Active Contributor
‎08-09-2017 7:55 PM
0 Kudos

And if you select validate do you have lgx text?

Something is wrong with your system. Lgx text has to be visible. Without it - nothing to discuss. Attach screenshots.

former_member488614
Participant
‎08-09-2017 8:35 PM
0 Kudos

I attached system details and UJKT screen shots.

former_member186338
Active Contributor
‎08-09-2017 8:49 PM
0 Kudos

Versions are very old!

And I asked you to show result of validate.

former_member488614
Participant
‎08-09-2017 9:02 PM
0 Kudos

Attached result of validate.

ujkt-validate.jpg

former_member186338
Active Contributor
‎08-09-2017 9:10 PM
0 Kudos

Then you have invalid script.

Try to validate simple script.

By the way, what do you mean by CATEGORY =CATEGORY in REC ? Strange...

former_member186338
Active Contributor
‎08-09-2017 9:25 PM
0 Kudos

Also destination_app will work only for the next when/endwhen. You have to repeat it before each when/endwhen.

former_member488614
Participant
‎08-10-2017 12:08 AM
0 Kudos

I did a test with 3 different scenarios.

1.Simple script-Worked fine simple-ujkt.jpg

2.Removed CATEGORY=CATEGORY and kept CATEGORY=BUD2018,FCSTQ32017, repeated destination_app before each when/endwhen category-ujkt.jpg

Invalid Dim member error

3.Removed CATEGORY=BUD2018,FCSTQ32017, and kept CATEGORY=$DEST_CAT$, repeated destination_app before each when/endwhen ujkt-dest-app.jpg

Invalid when/endwhen error

Am I doing any wrong in *FOR/*NEXT or *WHEN/*ENDWHEN loop?

former_member186338
Active Contributor
‎08-10-2017 10:14 AM
0 Kudos

Sorry but if you use in REC

CATEGORY=BUD2018,FCSTQ32017

You will never get a result. REC is writing to a single member!

You need to get some basic training in script logic, read some documents etc.

In this script you don't need to specify Category in REC at all!

former_member488614
Participant
‎08-10-2017 5:52 PM
0 Kudos

Even after removal of category from REC, still same invalid when/endwhen error, I believe the issue with *FOR/*NEXT or *WHEN/*ENDWHEN statements

*FOR %Y4%=%YEAR4% AND %Y5%=%YEAR5%
*XDIM_MEMBERSET TIME=%Y4%.01
*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 TIME
*IS *
*FOR %MONTH% =01,02,03,04,05,06,07,08,09,10,11,12
*REC(EXPRESSION=%VALUE%/12,TIME=%Y4%.%MONTH%)
*NEXT
*ENDWHEN

*XDIM_MEMBERSET TIME=%Y5%.01
*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 TIME
*IS *
*FOR %MONTH1%=01,02,03,04,05,06,07,08,09,10,11,12
*REC(EXPRESSION=%VALUE%/12,TIME=%Y5%.%MONTH1%)
*NEXT
*ENDWHEN
*NEXT
former_member186338
Active Contributor
‎08-10-2017 7:51 PM
0 Kudos

There are no issues with for/next or when/endwhen in general. May be some typo errors but I am unable to check from mobile phone. Try to simplify code to get a correct validation. Remove destination app. Just check loops.

former_member488614
Participant
‎08-11-2017 12:08 AM
0 Kudos

I checked with only loops as in screenshot and I don't see any typo errors.But still same error.

ujkt-loop.jpg

former_member186338
Active Contributor
‎08-11-2017 7:44 AM
0 Kudos

And where are selects?

former_member488614
Participant
‎08-11-2017 5:28 PM
0 Kudos

No change with selects as well.

ujkt-loop.jpg

former_member186338
Active Contributor
‎08-11-2017 6:04 PM
0 Kudos

Validate the following code:

*SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=BUD2018,FCSTQ32017)
*SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=BUD2018,FCSTQ32017)
*FOR %Y4%=%YEAR4% AND %Y5%=%YEAR5%
*FOR %MONTH% =01,02,03,04,05,06,07,08,09,10,11,12
*XDIM_MEMBERSET TIME=%Y4%.%MONTH%
*NEXT
*ENDWHEN

*FOR %MONTH1%=01,02,03,04,05,06,07,08,09,10,11,12
*XDIM_MEMBERSET TIME=%Y5%.%MONTH1%
*NEXT
*NEXT
former_member488614
Participant
‎08-11-2017 7:39 PM
0 Kudos

Looks like *WHEN is missing, below is UJKT validation text.

UJK_VALIDATION_EXCEPTION:Invalid when/endwhen - Line #3 : Invalid when/endwhen - redundant endwhen

former_member186338
Active Contributor
‎08-11-2017 7:54 PM
0 Kudos

There is no when endwhen in my code! I can't imaging what are you doing. Just validate it as is and show log.

former_member488614
Participant
‎08-11-2017 8:28 PM
0 Kudos

If you see your new code, there is a *ENDWHEN at line#7, I believe that is not required, attached validation.

validation.jpg

former_member186338
Active Contributor
‎08-09-2017 8:35 AM
0 Kudos

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.

Show replies
Show replies
former_member488614
Participant
‎08-01-2017 11:09 PM
0 Kudos

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

Show replies
Show replies
former_member186338
Active Contributor
‎08-01-2017 7:56 PM
0 Kudos

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

Show replies
Show replies
Ask a Question