Solved: BPC SCRIPT LOGIC TIME DIMENSION PROBLEM

Hi Experts,

I'm on BPC 10 MS SP13, with EPM-ADD SP18

I've a problem with script logic: I need a data region with two periods : Period Selected from prompt and Same Period Last Year.respect to selected period.

I try to use %YEAR%(-1) or %PERIOD%(-12) but in time dimension I have other elements , see below images.

I try with this syntax:

*SELECT (%PREV_YEAR%,"PY",TIME,[ID]='%TIME_SET%')

*SELECT (%MONTH%,"[PERIOD]","TIME","[ID]='%TIME_SET')

*XDIM_MEMBERSET TIME=%PREV_YEAR%.%MONTH%,%TIME_SET%

When I check log i found that %PREV_YEAR% and %MONTH% are empty. Data in fact table are filtered only for %TIME_SET% .

If I substitute %TIME_SET% with fixed values (Ex: 2014.AUG ) %PREV_YEAR% and %MONTH% are populated correctly.

Any help appreciated

Thanks

Samuele

SAP Managed Tags:
SAP Business Planning and Consolidation, version for the Microsoft platform

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Finally I've found the problem:

In data manager script is used logic file with .lgx extension

TASK(Execute formulas,LOGICFILE,%ModelPath%\..\AdminApp\%Model%\FX_ACT_LY_VS_ACT.lgx)

If I Use .lgx extension It works correctly

TASK(Execute formulas,LOGICFILE,%ModelPath%\..\AdminApp\%Model%\FX_ACT_LY_VS_BDG.lgf)

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Answers (2)

Hi Samuele,

you don't need other properties, you can use two normal select from prompt (%TIME_SET = current period)

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

*SELECT (%PREV_YEAR%,"[YEAR]-1",TIME,[ID]='%TIME_SET%'),

you can use also

*XDIM_MEMBERSET PRIOR(-12),%TIME_SET%

in combination with

*WHEN TIME

*IS PRIOR(-12)

....

*ENDWHEN

you can also use GET funtion to obtain a value of the previous period, e.g.

GET(TIME=PRIOR(-12))

Regards

Roberto

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Roberto, I've tried you suggestions but it doesn't works correctly. Seems that PRIOR(-12) is not translated.. in a time element

This is the package log

****************************************************************************************************
Start time --->18:52:24 - Date:2014-10-19 (build version:10.0.13.0)
****************************************************************************************************

User:SORIN\S_BPCTEST
Environment:SorinGroup_Reporting
Model:Reporting
Logic mode:1
Logic by:
Scope by:
Data File:
Debug File:\\SORIN-BOPCDQ\OSoft\WebFolders\SorinGroup_Reporting\Reporting\PrivatePublications\S_BPCTEST\Te...
Logic File:\\SORIN-BOPCDQ\OSoft\WebFolders\SorinGroup_Reporting\Reporting\\..\AdminApp\Reporting\FX_ACT_LY...
Selection:\\SORIN-BOPCDQ\OSoft\WebFolders\SorinGroup_Reporting\Reporting\PRIVATEPUBLICATIONS\S_BPCTEST\TempFiles\FROM_272_.TMP
Run mode:1
Query size:0
Delim:,
Query type:0
Simulation:0
Calc diff.:1
Formula script:
Max Members:
Test mode:0
Is Modelling:1
Work status Check:1
Task name:DM

Number of logic calls: 1
----------------------------------------------------------------------------------------------------
Call no. 1, logic: \\SORIN-BOPCDQ\OSoft\WebFolders\SorinGroup_Reporting\Reporting\\..\AdminApp\Reporting\FX_ACT_LY_VS_B...
----------------------------------------------------------------------------------------------------
signeddata is YTD

-------------------------
Building sub-query 1
-------------------------
Query Type: 0
Max members:
Region:
DIMENSION:CATEGORY
ACTUAL,BUDGET,FORECAST,OUTLOOK,LATEESTIMATE,ACT_LY_VS_BDG,ACT_VS_BDG,BUDGET_LE,ACT_VS_ACT_LY,MONTH13,ACT_LY_VS_ACT,ACTDEC_LY_VS_ACT,ACTDEC_LY_VS_BDG,ACT_TEST,FLASH,FLASH2,ACT_L2Y_VS_BDG
DIMENSION:LE
L_351
DIMENSION:TIME
2014.SEP

DIMENSION:CATEGORY
ACT_LY_VS_BDG,ACTUAL
DIMENSION:TIME
2014.SEP,PRIOR(-12)
DIMENSION:DATASRC
INPUT
DIMENSION:INPUTCURRENCY
USD,GBP,CHF,JPY,EUR,CAD,NOK,SEK,DKK,PLN,CZK,HUF,SKK,HKD,SGD,BRL,ARS,AUD,INR,ZAR,TRL,CNY,AED,NZD,RUB,DOP,LC
Loading ACCOUNT.RATETYPE
Loading INPUTCURRENCY.ID,GROUP
Loading LE.CURRENCY
Loading TIME.TIMEID
Time to load properties: 0.01 sec
----------------------------------------------------------------------------------------------------
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
into #tblTempLogic_284528
from tblFactReporting
WHERE CATEGORY in (N'ACT_LY_VS_BDG',N'ACTUAL') AND DATASRC=N'INPUT' AND INPUTCURRENCY in (N'USD',N'GBP',N'CHF',N'JPY',N'EUR',N'CAD',N'NOK',N'SEK',N'DKK',N'PLN',N'CZK',N'HUF',N'SKK',N'HKD',N'SGD',N'BRL',N'ARS',N'AUD',N'INR',N'ZAR',N'TRL',N'CNY',N'AED',N'NZD',N'RUB',N'DOP',N'LC') AND LE=N'L_351' AND TIMEID=N'20140900'
insert into #tblTempLogic_284528 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFactWBReporting
WHERE CATEGORY in (N'ACT_LY_VS_BDG',N'ACTUAL') AND DATASRC=N'INPUT' AND INPUTCURRENCY in (N'USD',N'GBP',N'CHF',N'JPY',N'EUR',N'CAD',N'NOK',N'SEK',N'DKK',N'PLN',N'CZK',N'HUF',N'SKK',N'HKD',N'SGD',N'BRL',N'ARS',N'AUD',N'INR',N'ZAR',N'TRL',N'CNY',N'AED',N'NZD',N'RUB',N'DOP',N'LC') AND LE=N'L_351' AND TIMEID=N'20140900'
and SOURCE = 0
insert into #tblTempLogic_284528 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFAC2Reporting
WHERE CATEGORY in (N'ACT_LY_VS_BDG',N'ACTUAL') AND DATASRC=N'INPUT' AND INPUTCURRENCY in (N'USD',N'GBP',N'CHF',N'JPY',N'EUR',N'CAD',N'NOK',N'SEK',N'DKK',N'PLN',N'CZK',N'HUF',N'SKK',N'HKD',N'SGD',N'BRL',N'ARS',N'AUD',N'INR',N'ZAR',N'TRL',N'CNY',N'AED',N'NZD',N'RUB',N'DOP',N'LC') AND LE=N'L_351' AND TIMEID=N'20140900'
select tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER,sum(SIGNEDDATA) as SIGNEDDATA
from #tblTempLogic_284528 as tmpTable
group by tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER
drop table #tblTempLogic_284528
----------------------------------------------------------------------------------------------------
Time to load Source data: 0.03 sec.
181 records to process
----------------------------------------------------------------------------------------------------
select INPUTCURRENCY,SIGNEDDATA
into #tblTempLogic_388619
from tblFactRATE
WHERE CATEGORY=N'BUDGET' AND RATE=N'AVG' AND RATESRC=N'RATEINPUT' AND TIMEID=N'20140900'
insert into #tblTempLogic_388619 (INPUTCURRENCY,SIGNEDDATA)
select INPUTCURRENCY,SIGNEDDATA
from tblFactWBRATE
WHERE CATEGORY=N'BUDGET' AND RATE=N'AVG' AND RATESRC=N'RATEINPUT' AND TIMEID=N'20140900'
and SOURCE = 0
insert into #tblTempLogic_388619 (INPUTCURRENCY,SIGNEDDATA)
select INPUTCURRENCY,SIGNEDDATA
from tblFAC2RATE
WHERE CATEGORY=N'BUDGET' AND RATE=N'AVG' AND RATESRC=N'RATEINPUT' AND TIMEID=N'20140900'
select tmpTable.INPUTCURRENCY,sum(SIGNEDDATA) as SIGNEDDATA
from #tblTempLogic_388619 as tmpTable
group by tmpTable.INPUTCURRENCY
drop table #tblTempLogic_388619
----------------------------------------------------------------------------------------------------
Time to load Lookup data: 0.01 sec.
27 LOOKUP records
Time to prepare data process: 0.00 sec.
----------------------------------------------------------------------------------------------------
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
into #tblTempLogic_699514
from tblFactREPORTING
WHERE CATEGORY=N'ACT_LY_VS_BDG' AND DATASRC=N'INPUT' AND INPUTCURRENCY=N'RPTEUR' AND LE=N'L_351' AND TIMEID=N'20140900'
insert into #tblTempLogic_699514 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFactWBREPORTING
WHERE CATEGORY=N'ACT_LY_VS_BDG' AND DATASRC=N'INPUT' AND INPUTCURRENCY=N'RPTEUR' AND LE=N'L_351' AND TIMEID=N'20140900'
and SOURCE = 0
insert into #tblTempLogic_699514 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFAC2REPORTING
WHERE CATEGORY=N'ACT_LY_VS_BDG' AND DATASRC=N'INPUT' AND INPUTCURRENCY=N'RPTEUR' AND LE=N'L_351' AND TIMEID=N'20140900'
select tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER,sum(SIGNEDDATA) as SIGNEDDATA
from #tblTempLogic_699514 as tmpTable
group by tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER
drop table #tblTempLogic_699514
----------------------------------------------------------------------------------------------------
Time to load Destination data: 0.03 sec.
176 records in destination region
Time to initialize destination: 0.00 sec.
----------------------------------------------------------------------------------------------------
Time to get source data: 0.00 sec.
----------------------------------------------------------------------------------------------------
181 records scanned
181 records skipped
0 records processed
0 records generated
Time to scan data: 0.00 sec.
------------------------------
Time to prepare final strings: 0.00 sec.
Total processing time: 0.01 sec.
Model: REPORTING - Records to be posted are 0 (calc diff = 1)
DATASRC,ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIME,WORKCENTER,SIGNEDDATA
Time to post records: 0.00 sec.

call 1 completed and data posted in 0.12 sec.

Run completed in 0.14 sec.
****************************************************************************************************
End time --->18:52:24 - Date: 2014-10-19
****************************************************************************************************

File path: \SorinGroup_Reporting\Reporting\PrivatePublications\S_BPCTEST\tempfiles\DebugLogic_272_1389383221.Log

Hi Mattis, I've tried your suggesstion but It doesn't works.

This is the package LOG

****************************************************************************************************
Start time --->18:50:08 - Date:2014-10-19 (build version:10.0.13.0)
****************************************************************************************************

User:SORIN\S_BPCTEST
Environment:SorinGroup_Reporting
Model:Reporting
Logic mode:1
Logic by:
Scope by:
Data File:
Debug File:\\SORIN-BOPCDQ\OSoft\WebFolders\SorinGroup_Reporting\Reporting\PrivatePublications\S_BPCTEST\Te...
Logic File:\\SORIN-BOPCDQ\OSoft\WebFolders\SorinGroup_Reporting\Reporting\\..\AdminApp\Reporting\FX_ACT_LY...
Selection:\\SORIN-BOPCDQ\OSoft\WebFolders\SorinGroup_Reporting\Reporting\PRIVATEPUBLICATIONS\S_BPCTEST\TempFiles\FROM_271_.TMP
Run mode:1
Query size:0
Delim:,
Query type:0
Simulation:0
Calc diff.:1
Formula script:
Max Members:
Test mode:0
Is Modelling:1
Work status Check:1
Task name:DM

DIMENSION:CATEGORY
ACT_LY_VS_BDG,ACTUAL
DIMENSION:TIME
2014.SEP,.
DIMENSION:DATASRC
INPUT
DIMENSION:INPUTCURRENCY
USD,GBP,CHF,JPY,EUR,CAD,NOK,SEK,DKK,PLN,CZK,HUF,SKK,HKD,SGD,BRL,ARS,AUD,INR,ZAR,TRL,CNY,AED,NZD,RUB,DOP,LC
Loading ACCOUNT.RATETYPE
Loading INPUTCURRENCY.ID,GROUP
Loading LE.CURRENCY
Loading TIME.TIMEID
Time to load properties: 0.01 sec
----------------------------------------------------------------------------------------------------
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
into #tblTempLogic_97439
from tblFactReporting
WHERE CATEGORY in (N'ACT_LY_VS_BDG',N'ACTUAL') AND DATASRC=N'INPUT' AND INPUTCURRENCY in (N'USD',N'GBP',N'CHF',N'JPY',N'EUR',N'CAD',N'NOK',N'SEK',N'DKK',N'PLN',N'CZK',N'HUF',N'SKK',N'HKD',N'SGD',N'BRL',N'ARS',N'AUD',N'INR',N'ZAR',N'TRL',N'CNY',N'AED',N'NZD',N'RUB',N'DOP',N'LC') AND LE=N'L_351' AND TIMEID=N'20140900'
insert into #tblTempLogic_97439 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFactWBReporting
WHERE CATEGORY in (N'ACT_LY_VS_BDG',N'ACTUAL') AND DATASRC=N'INPUT' AND INPUTCURRENCY in (N'USD',N'GBP',N'CHF',N'JPY',N'EUR',N'CAD',N'NOK',N'SEK',N'DKK',N'PLN',N'CZK',N'HUF',N'SKK',N'HKD',N'SGD',N'BRL',N'ARS',N'AUD',N'INR',N'ZAR',N'TRL',N'CNY',N'AED',N'NZD',N'RUB',N'DOP',N'LC') AND LE=N'L_351' AND TIMEID=N'20140900'
and SOURCE = 0
insert into #tblTempLogic_97439 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFAC2Reporting
WHERE CATEGORY in (N'ACT_LY_VS_BDG',N'ACTUAL') AND DATASRC=N'INPUT' AND INPUTCURRENCY in (N'USD',N'GBP',N'CHF',N'JPY',N'EUR',N'CAD',N'NOK',N'SEK',N'DKK',N'PLN',N'CZK',N'HUF',N'SKK',N'HKD',N'SGD',N'BRL',N'ARS',N'AUD',N'INR',N'ZAR',N'TRL',N'CNY',N'AED',N'NZD',N'RUB',N'DOP',N'LC') AND LE=N'L_351' AND TIMEID=N'20140900'
select tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER,sum(SIGNEDDATA) as SIGNEDDATA
from #tblTempLogic_97439 as tmpTable
group by tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER
drop table #tblTempLogic_97439
----------------------------------------------------------------------------------------------------
Time to load Source data: 0.04 sec.
181 records to process
----------------------------------------------------------------------------------------------------
select INPUTCURRENCY,SIGNEDDATA
into #tblTempLogic_952263
from tblFactRATE
WHERE CATEGORY=N'BUDGET' AND RATE=N'AVG' AND RATESRC=N'RATEINPUT' AND TIMEID=N'20140900'
insert into #tblTempLogic_952263 (INPUTCURRENCY,SIGNEDDATA)
select INPUTCURRENCY,SIGNEDDATA
from tblFactWBRATE
WHERE CATEGORY=N'BUDGET' AND RATE=N'AVG' AND RATESRC=N'RATEINPUT' AND TIMEID=N'20140900'
and SOURCE = 0
insert into #tblTempLogic_952263 (INPUTCURRENCY,SIGNEDDATA)
select INPUTCURRENCY,SIGNEDDATA
from tblFAC2RATE
WHERE CATEGORY=N'BUDGET' AND RATE=N'AVG' AND RATESRC=N'RATEINPUT' AND TIMEID=N'20140900'
select tmpTable.INPUTCURRENCY,sum(SIGNEDDATA) as SIGNEDDATA
from #tblTempLogic_952263 as tmpTable
group by tmpTable.INPUTCURRENCY
drop table #tblTempLogic_952263
----------------------------------------------------------------------------------------------------
Time to load Lookup data: 0.01 sec.
27 LOOKUP records
Time to prepare data process: 0.00 sec.
----------------------------------------------------------------------------------------------------
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
into #tblTempLogic_387792
from tblFactREPORTING
WHERE CATEGORY=N'ACT_LY_VS_BDG' AND DATASRC=N'INPUT' AND INPUTCURRENCY=N'RPTEUR' AND LE=N'L_351' AND TIMEID=N'20140900'
insert into #tblTempLogic_387792 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFactWBREPORTING
WHERE CATEGORY=N'ACT_LY_VS_BDG' AND DATASRC=N'INPUT' AND INPUTCURRENCY=N'RPTEUR' AND LE=N'L_351' AND TIMEID=N'20140900'
and SOURCE = 0
insert into #tblTempLogic_387792 (ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA)
select ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIMEID,WORKCENTER,SIGNEDDATA
from tblFAC2REPORTING
WHERE CATEGORY=N'ACT_LY_VS_BDG' AND DATASRC=N'INPUT' AND INPUTCURRENCY=N'RPTEUR' AND LE=N'L_351' AND TIMEID=N'20140900'
select tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER,sum(SIGNEDDATA) as SIGNEDDATA
from #tblTempLogic_387792 as tmpTable
group by tmpTable.ACCOUNT,tmpTable.BUSINESSUNITS,tmpTable.CATEGORY,tmpTable.INPUTCURRENCY,tmpTable.INTCO,tmpTable.LE,tmpTable.PAESE,tmpTable.TIMEID,tmpTable.WORKCENTER
drop table #tblTempLogic_387792
----------------------------------------------------------------------------------------------------
Time to load Destination data: 0.02 sec.
176 records in destination region
Time to initialize destination: 0.00 sec.
----------------------------------------------------------------------------------------------------
Time to get source data: 0.00 sec.
----------------------------------------------------------------------------------------------------
181 records scanned
181 records skipped
0 records processed
0 records generated
Time to scan data: 0.00 sec.
------------------------------
Time to prepare final strings: 0.00 sec.
Total processing time: 0.01 sec.
Model: REPORTING - Records to be posted are 0 (calc diff = 1)
DATASRC,ACCOUNT,BUSINESSUNITS,CATEGORY,INPUTCURRENCY,INTCO,LE,PAESE,TIME,WORKCENTER,SIGNEDDATA
Time to post records: 0.00 sec.

call 1 completed and data posted in 0.12 sec.

Run completed in 0.16 sec.
****************************************************************************************************
End time --->18:50:08 - Date: 2014-10-19
****************************************************************************************************

File path: \SorinGroup_Reporting\Reporting\PrivatePublications\S_BPCTEST\tempfiles\DebugLogic_271_1801434309.Log

Hi Samuele.

are you sure you have typed exactly as I have written, this works for me I am using it.

Brgds

Mattias

Please paste your script in here and I will take a look

This the script

//Prompt: time (dest), entity

//ELABORA ACT_LY_VS_BDG a partire da Actual PY usando i cambi di BUDGET CY

*SELECT (%CURR%,"ID","INPUTCURRENCY","[GROUP]='RATEONLY'")
*SELECT (%PY%,"[YEAR]-1",TIME,"[ID]='%TIME_SET%'")
*SELECT (%MESE%,"[PERIOD]","TIME","[ID]='%TIME_SET%'")

*LOOKUP RATE
   *DIM CATEGORY="BUDGET"
   *DIM SOURCE:INPUTCURRENCY=INPUTCURRENCY.ID
   *DIM SOURCE_LC:INPUTCURRENCY=LE.CURRENCY
   *DIM DEST_EUR:INPUTCURRENCY="RPTEUR"
   *DIM DEST_USD:INPUTCURRENCY="RPTUSD"
   *DIM TIME="%TIME_SET%"
   *DIM RATESRC="RATEINPUT"
   *DIM RATE="AVG"
*ENDLOOKUP

*XDIM_MEMBERSET CATEGORY=ACT_LY_vs_BDG,ACTUAL
*XDIM_MEMBERSET TIME=%TIME_SET%,%PY%.%MESE%
*XDIM_MEMBERSET DATASRC=INPUT
*XDIM_MEMBERSET INPUTCURRENCY=%CURR%,LC

*WHEN CATEGORY
    *IS ACTUAL
*WHEN ACCOUNT.RATETYPE
          *IS AVG
             *WHEN INPUTCURRENCY.GROUP
                 *IS RATEONLY
      *WHEN TIME
         *IS %PY%.%MESE%
             *REC(FACTOR=LOOKUP(DEST_EUR)/LOOKUP(SOURCE),CATEGORY=ACT_LY_vs_BDG,TIME=%TIME_SET%,INPUTCURRENCY=RPTEUR)
      *ENDWHEN
   *ENDWHEN

        *WHEN ACCOUNT
             *IS BS310
                 *WHEN INPUTCURRENCY   //ADD
                   *IS LC //ADD
                           *WHEN TIME
                             *IS %PY%.%MESE%
                    *REC(FACTOR=LOOKUP(DEST_EUR)/LOOKUP(SOURCE_LC),CATEGORY=ACT_LY_vs_BDG,TIME=%TIME_SET%,INPUTCURRENCY=RPTEUR)
                *ENDWHEN
              *ENDWHEN
        *ENDWHEN
*IS END
               *WHEN INPUTCURRENCY
                   *IS LC
                    *WHEN TIME
         *IS %PY%.%MESE%
             *REC(FACTOR=LOOKUP(DEST_EUR)/LOOKUP(SOURCE_LC),CATEGORY=ACT_LY_vs_BDG,TIME=%TIME_SET%,INPUTCURRENCY=RPTEUR)
      *ENDWHEN
     *ENDWHEN
    *ENDWHEN
*ENDWHEN
*COMMIT

Hi Mattias, Have you used the script in BPC for MS or BPC for NW ?

not sure why there is a problem, but can you try to move down your Lookup section to after the XDIM_MEMBERSET section?

I've moved lookup section after XDIM_MEMBERSET but nothing change.

not really sure what the problem is, can you just try in the debugger a simple script with the select statements and XDIM_MEMBERSET TIME and then just do a simple :

*WHEN *

*IS *

*REC(CATEGORY=#TEST)

*ENDWHEN

*XDIM MEMBERSET TIME = %TIME_SET%,PRIOR(12) also works fine without the select statements

I've tried *XDIM MEMBERSET TIME = %TIME_SET%,PRIOR(12) but It doesn't work.

In *select I tried to change

*SELECT (%PY%,"[YEAR]-1",TIME,"[ID]='%TIME_SET%'")

*SELECT (%MESE%,"[PERIOD]","TIME","[ID]='%TIME_SET%'")

with

*SELECT (%PY%,"[YEAR]-1",TIME,"[ID]='2014.SEP'")

*SELECT (%MESE%,"[PERIOD]","TIME","[ID]='2014.SEP'")

and It works correctly.. but I need dynamic time selected from prompt

if you in the debugger say time = 2014.SEP and let your selects dynamic, does that work?

there might be some misstake in your Datamanager script

Brgds

Mattias

In Logic Debugger It's Works correctly. If I launch package from data manager It doesn't works, only %TIME_SET% parameter are populated but not %PY% and %MESE%

In SSIS package there is an "Execute Formula" task

This is the data manager script:

DEBUG(ON)

PROMPT(SELECTINPUT,,,"Please select Time and Entity to Translate","Time.LE")

TASK(Execute formulas,USER,%USER%)

TASK(Execute formulas,Environment,%Environment%)

TASK(Execute formulas,Model,%Model%)

TASK(Execute formulas,SELECTION,%SELECTIONFILE%)

TASK(Execute formulas,LOGICFILE,%ModelPath%\..\AdminApp\%Model%\FX_ACT_LY_VS_BDG.lgx)

TASK(Execute formulas,RUNMODE,1)

TASK(Execute formulas,LOGICMODE,1)

try change "TIME.LE" to %TIME_DIM%

I tried but nothing changes

in the datamanager package I mean

Hi Samuele,

sorry is my mistake you don't need minus sign for PRIOR and NEXT

*XDIM_MEMBERSET PRIOR(12),%TIME_SET%

in combination with

*WHEN TIME

*IS PRIOR(12)

....

*ENDWHEN

you can also use GET funtion in REC instruction to obtain a value of the previous period, e.g.

GET(TIME=PRIOR(12))

Regards

Roberto

*SELECT(%CMONTH%,"[PERIOD]","TIME","[ID]='%TIME_SET%' ")

*SELECT(%LYEAR%,"[YEAR]-1","TIME","[ID]='%TIME_SET%' ")

*XDIM_MEMBERSET TIME = %TIME_SET%,%LYEAR%.%CMONTH%

there are others ways to solve this but with this you dont need to use your property for last year

If you want to use your PY property you change the %LYEAR% select to:

*SELECT(%LYEAR%,"[PY]","TIME","[ID]='%TIME_SET%' ")

Brgds

Mattias

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Ask a Question

Top Q&A Solution Author

User

Count

BPC SCRIPT LOGIC TIME DIMENSION PROBLEM

Accepted Solutions (1)

Accepted Solutions (1)

Answers (2)

Answers (2)

Re: Budgeting System in SAP

Re: Syntax to include process selection in where c...

Re: Depreciation Run deletion

Re: How to retrive po in substitution during miro

SAP BO - Formatted Search Query