Skip to Content
1
Former Member
Sep 21, 2012 at 07:58 AM

BPC 10 NW Script Logic - Issue with dynamic variable in *Select and *REC statement

254 Views

Hello experts, I am working on a BPC 10 NW implementation and we have a requirement to take the average of the first 8 months of actuals for the current year and seed that into each of the 12 periods of the next year. The data might look something like this:

Current Year

APR 11

MAY 12

JUN 10

JUL 11

AUG 10

SEP 12

OCT 10

NOV 12

DEC

JAN

FEB

MAR

This would mean that 11 is seeded into the next year from APR to MAR. To accomodate we have used a generic script tweaked to suit our requirement. When the package is executed through the Data Manager users are prompted to enter the current year eg 2011, which is then stored in %BUDYEAR% and passed dynamically to the script. The script is as follows -

*SELECT(%BASE%,ID,TIME,"[BASELINE]='%BUDYEAR%'") //The time dimension has a property called BASELINE and the first 8 months of each year are flagged.

*SELECT(%UPLIFT%,"ID","ACCOUNT","[UPLIFT]=Y") //Only certain accounts need to be used.

*XDIM_MEMBERSET TIME=%BASE%

*XDIM_MEMBERSET ACCOUNT=%UPLIFT%

*XDIM_MEMBERSET CATEGORY=ACTUAL

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P01)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P02)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P03)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P04)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P05)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P06)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P07)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P08)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P09)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P10)

*REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P11)

*REC(EXPRESSION=%VALUE%/8,TIME=2012.P12)

*ENDWHEN

There are two issues with the script -

The validation errors on *SELECT(%BASE%,ID,TIME,"[BASELINE]='%BUDYEAR%'") in particular the %BUDYEAR%.

It also errors on *REC(EXPRESSION=%VALUE%/8,TIME=%BUDYEAR%(+1).P01).

Any help would be much appreciated.