Script Logic Variables in Select Statement

[Former Member]

Hello BPC experts,

We are trying to get all the months that are greater than the one that we have entered by the pop-up in the datamanager package.

We are working with the following syntax that used to work in other proyect over BPC 7.5 NW, but it is not working in our case in BPC 10 MS.

The variable in LE2 is only getting one month and not all the months greater than the proposed one. For instances if we are entering 2012.SEP, the variable %LE2% should store 2012.SEP, 2012.OCT, 2012.NOV and 2012.DEC, but it is only getting 2012.SEP.

These are the instructions we are using.

*SELECT(%MONTH_PLAN%,"[BASE_PERIOD]",TIME,"[ID]"=''TIME_SET")

*SELECT(%CY%,"[YEAR]","TIME","[ID]='%TIME_SET%'")

*SELECT(%LE2%,"[ID]",TIME,"[BASE_PERIOD]>='%MONTH_PLAN%' AND [CALC]='N' AND [YEAR]='%CY%'")

Please let us know if you have any ideas.

Best regards and thanks in advance!!

[Former Member · Accepted solution]

Hi,

The first select statement needs a small change.

*SELECT(%MONTH_PLAN%,"[BASE_PERIOD]",TIME,"[ID]"='%TIME_SET%')

Karthik AJ

[Former Member · Accepted solution]

Hello Kaarhthic,

Your statement is the one that I was using. It has been a mistake when copying it into the post.

But the select is still not working properly

Thanks

[Former Member · Accepted solution]

Hi,

I just now figured it out. The values for any property are stored as NVarChar in the backend tables.

I suppose, Base_Period has values similar to integers.

What you are trying to do is to compare an NvarChar with a numeric value.

So use cast syntax to compare correctly.

*SELECT(%MONTH_PLAN%,[BASE_PERIOD],TIME,[ID]='%TIME_SET%')

*SELECT(%CY%,[YEAR],TIME,[ID]='%TIME_SET%')

*SELECT(%LE2%,[ID],TIME,cast( [BASE_PERIOD] as integer) >=%MONTH_PLAN% AND [CALC]='N' AND [YEAR]='%CY%')

Karthik AJ

[Former Member · Accepted solution]

Thank you very much Kaarthic,

It is working now.

The solution was to make the cast as you indicated in the third instruction.

Best regards