Skip to Content
avatar image
Former Member

Nesting select queries in BPC 10 NW logic scripts

Hi,

I am working on a BPC 10.0 NW trying to dynamically updated a piece of script logic to seed data for forecasting, hardcoding the values in the logic script works, but I have got two issues, firstly I am writing a select query that I want to select records based on not having a null/blank property but I can not find the comand for NOT Null, I tried as follows,

*SELECT(%PACCT%,"[PLAN_PARENT]",ACCOUNT,"[PLAN_PARENT] NOT NULL")

But it does not like the syntax NOT NUll

This select statement drives a FOR statement that I want to use a varible in another select statement that drives a REC

The following is the syntax I curently have  that works

*FOR %FC% = %FCVERSION%

*FOR %ACCT% = %PACCT%

  *XDIM_MEMBERSET ACCOUNT = BAS(%ACCT%)

  *SELECT(%FCACCT%,"[ID]",ACCOUNT,"[PLAN_PARENT] = LI_FYPDA")

  *FOR %INPUTACCT% = %FCACCT%

       *REC(FACTOR = 1, ENTRYTYPE = SYS_CALC, VERSION = %FC%, ACCOUNT = %INPUTACCT%)

   *COMMIT

  *NEXT

*NEXT

Below is what I have tried that does not work

*FOR %FC% = %FCVERSION%

*FOR %ACCT% = %PACCT%

  *XDIM_MEMBERSET ACCOUNT = BAS(%ACCT%)

  *SELECT(%FCACCT%,"[ID]",ACCOUNT,"[PLAN_PARENT] = " %ACCT%)

  *FOR %INPUTACCT% = %FCACCT%

       *REC(FACTOR = 1, ENTRYTYPE = SYS_CALC, VERSION = %FC%, ACCOUNT = %INPUTACCT%)

   *COMMIT

  *NEXT

*NEXT

I would like the query restriction to be the variable from the for statement not a hardcoded value

I would appreciate and help in using a variable in an select statement

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Dec 23, 2014 at 04:54 PM

    Thank you for the tip on how to as script logic questions. The find blank worked.

    BPC NW 10.0 Engine I think it is JAVASCRIPT

    This is a data manager script that will run after an upload from another system to summarize some data into a forecast version

    The following key dimensions are

    Version that has a property called CURACTMTH that contains the ID for the current time period, and STARTMNTH that is the month number for the different forecast versions

    Time that has the property called MONTHNUM that contains the month number that corresponds to the forecast version

    Account that has a property called PLAN_PARENT on certain base levels, PLAN_PARENT is the ID for the parent that will be summarized into the account with the flag.

    The end game is a dynamic script that follows the following logic,

    For the current month based on the CURACTMNT ID from the A01 version, select the current forecast version that has the same MONTHNUM as the time period, copy the plan parent total into the corresponding base account from Actuals to Forecast.

    Each plan parent ID only appears once as a property and each MONTHNUM only appears once

    The Script,

    *SELECT(%CURMNTH%,"[CURACTMTH]",VERSION,"[ID] = A01")

    // find the CURRENT month ID

    //*SELECT(%PACCT%,"[PLAN_PARENT]",ACCOUNT,"[PLAN_PARENT] = LI_FYPDA")

    *SELECT(%PACCT%,"[PLAN_PARENT]",ACCOUNT,"[PLAN_PARENT] <>''")

    // find the plan PARENT accounts

    *SELECT(%VMONTH%,"[MONTHNUM]",TIME,"[ID] = "%CURMTH%)

    // find the CURRENT month number from the month ID

    *SELECT(%FCVERSION%,"[ID]",VERSION,"STARTMNTH ="%VMONTH%)//does not work works if I hardcode %VMONTH%

    // find the forecast version ID

    //Limit data set for faster processing

    *XDIM_MEMBERSET TIME = %CURMNTH%

    *XDIM_MEMBERSET VERSION = A01

    *XDIM_MEMBERSET ENTRYTYPE = BAS(G_TOTAL)

    //Set Version

    *FOR %FC% = %FCVERSION% //cycles through forecast versions selected through select statement

    *FOR %ACCT% = %PACCT% //cycles through accounts selected through select statement

    *XDIM_MEMBERSET ACCOUNT = BAS(%ACCT%)

    *SELECT(%FCACCT%,"[ID]",ACCOUNT,"[PLAN_PARENT] = LI_FYPDA")// works need to make dynamic based on current accounts as this feeds the rec function

    *FOR %INPUTACCT% = %FCACCT%

    *WHEN TIME

    *IS %CURMNTH%

    *WHEN ENTRYTYPE

    *IS BAS(G_TOTAL)

    *WHEN VERSION

    *IS A01

    *WHEN ACCOUNT

    *IS BAS(%ACCT%)

    *REC(FACTOR = 1, ENTRYTYPE = SYS_CALC, VERSION = %FC%, ACCOUNT = %INPUTACCT%)

    *ENDWHEN

    *ENDWHEN

    *ENDWHEN

    *ENDWHEN

    *COMMIT

    *NEXT

    *NEXT

    *NEXT

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 23, 2014 at 04:22 AM

    Hi Nathan,

    First, you can't use SELECT with loop variable in WHERE part inside FOR/NEXT loop - it's processed before FOR/NEXT.

    Second: to test for empty property:

    *SELECT(%PACCT%,"[PLAN_PARENT]",ACCOUNT,"[PLAN_PARENT]<>''")

    Vadim

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 23, 2014 at 01:12 PM

    I kinda doubt that your first script "worked" because *REC works only inside WHEN/ENWHEN brackets.

    Add comment
    10|10000 characters needed characters exceeded