Skip to Content
0

Holding multiple member values in the BPC script logic variable

Aug 01, 2017 at 06:42 PM

437

avatar image
Former Member

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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Best Answer
Vadim Kalinin Aug 02, 2017 at 06:01 AM
0

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 10 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

"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
0
Former Member
Vadim Kalinin

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

0

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
0
Former Member
Vadim Kalinin

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





0

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!
0
Show more comments

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

0
Show more comments
Vadim Kalinin Aug 01, 2017 at 06:56 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Aug 01, 2017 at 10:09 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Aug 09, 2017 at 05:52 PM
0

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 17 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Former Member
Vadim Kalinin

I attached system details and UJKT screen shots.

ujkt.jpg (121.6 kB)
system-details.jpg (17.2 kB)
0

Versions are very old!

And I asked you to show result of validate.

0
Former Member
Vadim Kalinin

Attached result of validate.

ujkt-validate.jpg

ujkt-validate.jpg (84.6 kB)
0

Then you have invalid script.

Try to validate simple script.

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

0

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

0
Former Member
Vadim Kalinin

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?

simple-ujkt.jpg (62.6 kB)
category-ujkt.jpg (102.9 kB)
ujkt-dest-app.jpg (97.0 kB)
0
Show more comments
Former Member

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



0

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.

0
Former Member
Vadim Kalinin

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

ujkt-loop.jpg

ujkt-loop.jpg (50.2 kB)
0

And where are selects?

0
Former Member
Vadim Kalinin

No change with selects as well.

ujkt-loop.jpg

ujkt-loop.jpg (65.2 kB)
0

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
0
Show more comments

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

0
Show more comments
Vadim Kalinin Aug 09, 2017 at 07:35 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Aug 11, 2017 at 07:36 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Aug 11, 2017 at 07:36 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Aug 11, 2017 at 07:47 PM
0

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

validation.jpg


validation.jpg (55.5 kB)
Show 4 Share
10 |10000 characters needed characters left characters exceeded

In my system this code is validating...

Try instead of MONTH AND MONTH1

M1 AND M2

What is your bpc version and SP?

0
Former Member
Vadim Kalinin

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

validation.jpg (54.3 kB)
system-details.jpg (17.2 kB)
0

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

0
Former Member
Vadim Kalinin

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

0