Nesting select queries in BPC 10 NW logic scripts

[Former Member]

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

[Former Member · Accepted solution]

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

[former_member186338 · Accepted solution]

Hi Nathan,

If you have target account value in the PLANT_PARENT ACCOUNT property of base members, then you simply:

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

Please, remove the USELESS COMMIT that will reset scope!!!!!!!!

B.R. Vadim

[former_member186338 · Accepted solution]

In general if you want to copy data from number of base accounts to some target base accounts you have 2 options:

1. Universal - for each source base account fill some property (TARGETACCT) with the required target base account. Then use simple *REC(...,ACCOUNT=ACCOUNT.TARGETACCT)

2. For the simple 2 level account hierarchy:

PARENT1

  BASE1

  BASE2

...

PARENT2

  BASE3

  BASE4

...

BASE_PARENT1_INPUT

BASE_PARENT2_INPUT

You can fill the property for target account only for parents.

Then:

*SELECT(%AC%,"[ID]",ACCOUNT,"[TARGETACCT]<>''") // parents will be selected

*SELECT(%TAC%,"[TARGETACCT]",ACCOUNT,"[TARGETACCT]<>''") // targets will be selected

*FOR %A%=%AC% AND %TA%=%TAC%

*XDIM_MEMBERSET ACCOUNT=BAS(%A%)

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%,ACCOUNT=%TA%

*ENDWHEN

*NEXT

The second approach is easier to maintain but slower (FOR/NEXT is slow!) and applicable to specific hierarchy.

Vadim

[Former Member · Accepted solution]

Vadim,

Thanks for your help I have removed the commit.

My problem lies in the fact that the base member has the property showing its parent not the other way round. Is there a way of doing the reverse. for example

*WHEN ACCOUNT

*IS BAS(%ACCT%.Plan_Parent)

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

*ENDWHEN

Nathan

[Former Member · Accepted solution]

Vadim,

It is this second approach I am trying, luckly it is not that many accounts I am copying and it will run as part of the overnight processing.

Thanks for you help

[former_member186338 · Accepted solution]

If you are talking about the second approach - the please, correctly fill this property for the required PARENT members (put target base member)!

In general, you can't use *SELECT inside FOR/NEXT! And the syntax *IS BAS(%ACCT%.Plan_Parent) is also not supported.

Hope, it's clear.

Vadim

[Former Member · Accepted solution]

Thanks for all your help

[former_member200327]

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

[former_member186338]

yes, on top of all...

[former_member186338]

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

[former_member186338]

P.S. Also, please remove useless COMMIT from your code - it simply reset scope doing nothing. And describe the calculation logic - from your code it's not clear what you want to achieve.

Read

Vadim