Time period to be set as current month in script logic

[Former Member]

Hi Team,

We have  a data manager package which runs by selecting only time period in the prompt for the current month and there is a script logic to calculate the seeding for the model opex.

we would like to automate in such a way that DMP is run by taking only the current period without the prompt selection.

I have created a time property currentmonth in time dimension and added X to the member for period say current month(2016.08)

now in the script logic used the xdim filter as shown as below .

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM FILTER TIME = [TIME].PROPERTIES ("CURRENTMONTH") = "X"

I would like to know what change I need to make for %"TIME_SET% because this %time_set% is specified in many lines of the code in the script logic,

please share your inputs.

I am attaching the screen shot for your reference.

thanks

[former_member186338 · Accepted solution]

Simple:

At the beginning of script:

*SELECT(%TIMECUR%,[ID],TIME,[CURRENTMONTH]=X)

And replace all %TIME_SET% with %TIMECUR%

Vadim

P.S. And remove the line: *XDIM FILTER TIME = [TIME].PROPERTIES ("CURRENTMONTH") = "X"

[Former Member · Accepted solution]

Hi Vadim, thanks for your inputs. i have tried with the select statement and replaced as suggested. i am getting invalid select statement error when running the DMP. please see the script change thanks Aditya

[former_member186338 · Accepted solution]

Sorry, but you can't have multiple SELECT on the single line!

Looks like you have some copy/paste issues.

Please provide full text of your script as text - not as screenshot.

Read:

[Former Member · Accepted solution]

Hi Vadim,

please find the details below.

*SYSLIB SYSTEM_CONSTANTS.LGF

// STEP 1

*SELECT(%CE%,"[ID]",COSTELEMENT,"[ID] >'39999999' AND [ID] <'99999999'")

*XDIM_MEMBERSET TIME = %TIME_SET%

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

*SELECT(%TIMEVAR1%,"[ID]",TIME,"[LEVEL] = 'MONTH' AND [YEAR] = '%YR2%'")

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

*SELECT(%JANP%,"[ID]",CATEGORY,"[ID] = 'PLAN'")

*SELECT(%FEBP%,"[ID]",CATEGORY,"[ID] = 'JANFCST'")

*SELECT(%MARP%,"[ID]",CATEGORY,"[ID] = 'FEBFCST'")

*SELECT(%APRP%,"[ID]",CATEGORY,"[ID] = 'MARFCST'")

*SELECT(%MAYP%,"[ID]",CATEGORY,"[ID] = 'APRFCST'")

*SELECT(%JUNP%,"[ID]",CATEGORY,"[ID] = 'MAYFCST'")

*SELECT(%JULP%,"[ID]",CATEGORY,"[ID] = 'JUNFCST'")

*SELECT(%AUGP%,"[ID]",CATEGORY,"[ID] = 'JULFCST'")

*SELECT(%SEPP%,"[ID]",CATEGORY,"[ID] = 'AUGFCST'")

*SELECT(%OCTP%,"[ID]",CATEGORY,"[ID] = 'SEPFCST'")

*SELECT(%NOVP%,"[ID]",CATEGORY,"[ID] = 'OCTFCST'")

*SELECT(%DECP%,"[ID]",CATEGORY,"[ID] = 'NOVFCST'")

*SELECT(%CAT2%,"[ID]",CATEGORY,"[ID] = '%%CAT1%P%'")

//CAT2 will store the reference forecast category

*SELECT(%JANW%,"[ID]",CATEGORY,"[ID] = 'PLAN'")

*SELECT(%FEBW%,"[ID]",CATEGORY,"[ID] = 'JANW1'")

*SELECT(%MARW%,"[ID]",CATEGORY,"[ID] = 'FEBW1'")

*SELECT(%APRW%,"[ID]",CATEGORY,"[ID] = 'MARW1'")

*SELECT(%MAYW%,"[ID]",CATEGORY,"[ID] = 'APRW1'")

*SELECT(%JUNW%,"[ID]",CATEGORY,"[ID] = 'MAYW1'")

*SELECT(%JULW%,"[ID]",CATEGORY,"[ID] = 'JUNW1'")

*SELECT(%AUGW%,"[ID]",CATEGORY,"[ID] = 'JULW1'")

*SELECT(%SEPW%,"[ID]",CATEGORY,"[ID] = 'AUGW1'")

*SELECT(%OCTW%,"[ID]",CATEGORY,"[ID] = 'SEPW1'")

*SELECT(%NOVW%,"[ID]",CATEGORY,"[ID] = 'OCTW1'")

*SELECT(%DECW%,"[ID]",CATEGORY,"[ID] = 'NOVW1'")

*SELECT(%CAT3%,"[ID]",CATEGORY,"[ID] = '%%CAT1%W%'")

//CAT3 will store the reference forecast category

*SELECT(%JANF%,"[ID]",CATEGORY,"[ID] = '%CAT1%FCST'")

*SELECT(%FEBF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%MARF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%APRF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%MAYF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%JUNF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%JULF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%AUGF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%SEPF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%OCTF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%NOVF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%DECF%,"[ID]",CATEGORY,"[ID] = 'FCSTACTUAL'")

*SELECT(%CATFCST%,"[ID]",CATEGORY,"[ID] = '%%CAT1%F%'")

*SELECT(%NEXT%,"[ID]",TIME,"[LEVEL] = 'MONTH' AND [YEAR] = '%YR2%' AND [ID] >'%TIME_SET%'")

*SELECT(%PRIOR%,"[ID]",TIME,"[LEVEL] = 'MONTH' AND [YEAR] = '%YR2%' AND [ID] <'%TIME_SET%'")

*XDIM_MEMBERSET CATEGORY = %CAT1%FCST,FCST1,FCST2,WKFCST1

*XDIM_MEMBERSET TIME = %TIMEVAR1%

*WHEN CATEGORY

*IS *

*Rec(FACTOR = 0)

*ENDWHEN

*XDIM_MEMBERSET CATEGORY = %CAT1%W1,%CATFCST%

*XDIM_MEMBERSET TIME = "",%PRIOR%

*WHEN CATEGORY

*IS JANW1

*ELSE

*Rec(FACTOR = 0)

*ENDWHEN

*XDIM_MEMBERSET TIME = "",%PRIOR%

*XDIM_MEMBERSET CATEGORY = ACTUAL

*XDIM_MEMBERSET COSTELEMENT = FTE,FTE_EH_EEQ,FTE_EY_EEQ,LOA,%CE%

*WHEN CATEGORY

*IS ACTUAL

*Rec(FACTOR = 1,DATASRC = INPUT,CATEGORY = %CAT1%FCST)

*Rec(FACTOR = 1,DATASRC = INPUT,CATEGORY = FCST2)

*Rec(FACTOR = 1,DATASRC = INPUT,CATEGORY = WKFCST1)

*Rec(FACTOR = 1,DATASRC = INPUT,CATEGORY = %CAT1%W1)

*Rec(FACTOR = 1,DATASRC = INPUT,CATEGORY = FCSTACTUAL)

*ENDWHEN

*XDIM_MEMBERSET TIME = %NEXT%,%TIME_SET%

*XDIM_MEMBERSET CATEGORY = %CAT2%

*WHEN TIME

*IS %TIME_SET%

*Rec(FACTOR = 1,CATEGORY = FCST2,DATASRC = INPUT)

*ELSE

*Rec(FACTOR = 1,CATEGORY = %CAT1%FCST,DATASRC = INPUT)

*Rec(FACTOR = 1,CATEGORY = FCST2,DATASRC = INPUT)

*ENDWHEN

*XDIM_MEMBERSET COSTELEMENT = %CE%

*XDIM_MEMBERSET TIME = %TIMEVAR1%

*XDIM_MEMBERSET CATEGORY = %CAT2%,%CAT1%FCST

*WHEN CATEGORY

*IS %CAT2%

*REC(EXPRESSION = %VALUE%,CATEGORY = %CAT1%FCST, TIME=%TIME_SET%,DATASRC = INPUT)

*IS %CAT1%FCST

*REC(EXPRESSION = -%VALUE%,CATEGORY = %CAT1%FCST, TIME=%TIME_SET%,DATASRC = INPUT)

*ENDWHEN

*XDIM_MEMBERSET COSTELEMENT = FTE,FTE_EH_EEQ,FTE_EY_EEQ,LOA

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET CATEGORY = %CAT2%

*WHEN CATEGORY

*IS %CAT2%

*REC(EXPRESSION = %VALUE%,CATEGORY = %CAT1%FCST, TIME=%TIME_SET%,DATASRC = INPUT)

*ENDWHEN

*XDIM_MEMBERSET CATEGORY = %CAT1%FCST

*XDIM_MEMBERSET TIME = %TIMEVAR1%

*XDIM_MEMBERSET COSTELEMENT = FTE,FTE_EH_EEQ,FTE_EY_EEQ,LOA,%CE%

*WHEN CATEGORY

*IS *

*Rec(FACTOR = 1,CATEGORY = FCST1,DATASRC = INPUT)

*ENDWHEN

*XDIM_MEMBERSET CATEGORY = %CAT3%

*XDIM_MEMBERSET TIME = %TIME_SET%,%NEXT%

*WHEN CATEGORY

*IS *

*Rec(FACTOR = 1,CATEGORY = WKFCST1,DATASRC = INPUT)

*ENDWHEN

[former_member186338 · Accepted solution]

Sorry, but what is the issue???

Just:

At the beginning of script:

*SELECT(%TIMECUR%,[ID],TIME,[CURRENTMONTH]=X)

And replace all %TIME_SET% with %TIMECUR%

Please, do it accurately, without removing line breaks!

[former_member186338 · Accepted solution]

Like:

// STEP 1

*SELECT(%CE%,"[ID]",COSTELEMENT,"[ID] >'39999999' AND [ID] <'99999999'")

*SELECT(%TIMECUR%,[ID],TIME,[CURRENTMONTH]=X)

*XDIM_MEMBERSET TIME = %TIMECUR%

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

*SELECT(%TIMEVAR1%,"[ID]",TIME,"[LEVEL] = 'MONTH' AND [YEAR] = '%YR2%'")

*SELECT(%CAT1%,"[PERIOD]",TIME,"[ID] = '%TIMECUR%'")

*SELECT(%JANP%,"[ID]",CATEGORY,"[ID] = 'PLAN'")

...

[Former Member · Accepted solution]

we are making changes to the script to take the current month without prompt selection in the Data manager package.

current month property has been created and value has been kept as X for time period 2016.08.

do we need to keep

*xdim memberset time = %timecur%

I have kept the code as you suggested, but its giving me Invalid select statement in DM

[former_member186338 · Accepted solution]

In the DM advanced script remove TIME SELECTINPUT and the line with SELECTION

Please show the DM advanced script

[former_member186338 · Accepted solution]

DM advanced script has to be like:

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,YourScriptName.LGF)

[former_member186338 · Accepted solution]

"do we need to keep

*xdim memberset time = %timecur%" - yes!

You have to replace all %TIME_SET% with %TIMECUR% in the script!

P.S. And ensure that ONLY SINGLE Month has property set to X!!!

[Former Member · Accepted solution]

Thanks Vadim,

it worked fine,

In the prompt I also commented the statement under TASK which has the %selection%

[former_member186338 · Accepted solution]

Yes, selection has to be removed because you don't have anything in selection

[Former Member · Accepted solution]

Vadim,

in a DM package for pushing data, i have to automate category with current month forecast(AUGFCST)

and time period (2016.01 to 2016.12)

I will have to create property in  category and time dimension and currently working on this.

in script, I will update entire time period in time set

[former_member186338 · Accepted solution]

You can also create a property in TIME dimension to select the current forecast member....

[Former Member · Accepted solution]

Hi Vadim, I have created property ALLMONTHS in time dimension and marked X for all the periods in the ID column (2015.01 to 2015.12) Current Month Forecast needs to selected. I have hardcoded as AUGFCST ,but I would like to include in the script to select the current forecast. below is the code *SELECT(%TIMEYR%,"[ID]",TIME,"[ALLMONTHS] = 'X'") *XDIM_MEMBERSET CATEGORY = AUGFCST //Scope CostElement Dimension to member with Y in ACCOUNT_CS Property *SELECT(%CESET%, "[ID]", COSTELEMENT, "[ACCOUNT_CS]='Y'") *XDIM_MEMBERSET COSTELEMENT = %CESET% //Scope Company Dimension to member with value in COMPCODE Property *SELECT(%COMPANYSET%, "[ID]", COMPANY, "[COMPCODE] <> ' '") *XDIM_MEMBERSET COMPANY = %COMPANYSET% *XDIM_MEMBERSET COSTCENTER = BAS(ZSH0010) *DESTINATION_APP = CONSOLIDATION //Skip Dimensions excluded from OPEX because they don't exist in Consolidation *SKIP_DIM = PARTNERCOMPANY *SKIP_DIM = PARTNEROBJECT *SKIP_DIM = COSTCENTER *RENAME_DIM COSTELEMENT=ACCOUNT_CS *RENAME_DIM COMPANY=COMPCODE *RENAME_DIM DATASRC=DTSOURC //Add Dimensions to Consolidation that doesn't exist in OPEX and specify default value *ADD_DIM LEDGERTYPE=PL00 *ADD_DIM PARTNERUNIT=NO_PU *ADD_DIM SUBITEM=NODETAIL // Changed the %TIME_SET% to %TIMEYR%% //Data Push *WHEN TIME *IS %TIMEYR% *REC(FACTOR = 1,DATASRC = XFER_FROM_OPEX,COMPANY = COMPANY.COMPCODE) *ENDWHEN *COMMIT

[Former Member · Accepted solution]

sorry Vadim about the above code not pasted correctly

[former_member186338 · Accepted solution]

It was so ease to edit the message and to correct the code visibility. But after you post your last message you have to repost the code!

[Former Member · Accepted solution]

*SELECT(%COMPANYSET%, "[ID]", COMPANY, "[COMPCODE] <> ' '") *XDIM_MEMBERSET COMPANY = %COMPANYSET% *XDIM_MEMBERSET COSTCENTER = BAS(ZSH0010) *DESTINATION_APP = CONSOLIDATION //Skip Dimensions excluded from OPEX because they don't exist in Consolidation *SKIP_DIM = PARTNERCOMPANY *SKIP_DIM = PARTNEROBJECT *SKIP_DIM = COSTCENTER *RENAME_DIM COSTELEMENT=ACCOUNT_CS *RENAME_DIM COMPANY=COMPCODE *RENAME_DIM DATASRC=DTSOURC //Add Dimensions to Consolidation that doesn't exist in OPEX and specify default value *ADD_DIM LEDGERTYPE=PL00 *ADD_DIM PARTNERUNIT=NO_PU *ADD_DIM SUBITEM=NODETAIL // Changed the %TIME_SET% to %TIMEYR%% //Data Push *WHEN TIME *IS %TIMEYR% *REC(FACTOR = 1,DATASRC = XFER_FROM_OPEX,COMPANY = COMPANY.COMPCODE) *ENDWHEN *COMMIT

[former_member186338 · Accepted solution]

Please edit you message! It's unreadable! And explain what you want to achieve!

[Former Member]

Hi Vadim, we want to automate the time period and Category in the package.

Time period = All months of the current Year(2016.01 to 2016.12) Category = AUGFCST this should be current month forecast.

I have created a property (ALLMONTHS) in the time dimension and marked X for the current year all months.

I have used *select statement as below *SELECT(%TIMEYR%,"[ID]",TIME,"[ALLMONTHS] = 'X'") and replaced %TIMEYR% Where %timeset% is given. for Category I have just hardcoded,

I want to know how we can use to select the current forecast. please find the script below.

*SELECT(%TIMEYR%,"[ID]",TIME,"[ALLMONTHS] = 'X'")

*XDIM_MEMBERSET CATEGORY = AUGFCST //Scope CostElement Dimension to member with Y in ACCOUNT_CS Property

*SELECT(%CESET%, "[ID]", COSTELEMENT, "[ACCOUNT_CS]='Y'")

*XDIM_MEMBERSET COSTELEMENT = %CESET% //Scope Company Dimension to member with value in COMPCODE Property

*SELECT(%COMPANYSET%, "[ID]", COMPANY, "[COMPCODE] <> ' '")

*XDIM_MEMBERSET COMPANY = %COMPANYSET%

*XDIM_MEMBERSET COSTCENTER = BAS(ZSH0010) *DESTINATION_APP = CONSOLIDATION

*SKIP_DIM = PARTNERCOMPANY

*SKIP_DIM = PARTNEROBJECT

*SKIP_DIM = COSTCENTER //Dimension mapping from OPEX to Consolidation

*RENAME_DIM COSTELEMENT=ACCOUNT_CS

*RENAME_DIM COMPANY=COMPCODE *RENAME_DIM DATASRC=DTSOURC //Add Dimensions to Consolidation that doesn't exist in OPEX and specify default value

*ADD_DIM LEDGERTYPE=PL00

*ADD_DIM PARTNERUNIT=NO_PU

*ADD_DIM SUBITEM=NODETAIL // Changed the %TIME_SET% to %TIMEYR%% //Data Push

*WHEN TIME *IS %TIMEYR% *REC(FACTOR = 1,DATASRC = XFER_FROM_OPEX,COMPANY = COMPANY.COMPCODE)

*ENDWHEN

*COMMIT

Message was edited by: Folu Kehinde

[former_member186338]

Sorry, if you are unable to ask questions in the readable form I can't help you and will have to inform moderator to remove the posts

[former_member186338]

"I want to know how we can use to select the current forecast. please find the script below." - not clear!

What is the logic behind selection of "current" forecast??? What do you mean by "current"?

[Former Member]

Hi Vadim,

In the script how can we automate for the script to select the current Forecast  always,

we run the package every month and Example for this month we select category as AUGFCST and time period for the entire year.

I have attached the scrip logic .

I have create at property ALLMONTHS in time dimension and marked X for all the periods for the current year.

Push consolidation.txt.zip

[former_member186338]

The system have no idea about "for this month"

You can create a property in CATEGORY like CURRENTCATEGORY and mark with X current category.

*SELECT(%CURCATEG%,[ID],CATEGORY,[CURRENTCATEGORY]=X)

You can create property in TIME dimension CATEGORY and fill it with required CATEGORY for each month. In this case you will set CURRENTMONTH with X and:

*SELECT(%TIMECUR%,[ID],TIME,[CURRENTMONTH]=X)

*SELECT(%CURCATEG%,[CATEGORY],TIME,[ID]=%TIMECUR%)

[Former Member]

hi Vadim

the logic you explained above is for getting the current forecast (ex,Janfcst,febfcst,marfcst.....) as we will run the package every month.

I understood the property in category dimension and will mark it with X

for creating the property called category in time dimension , I will mention value as JANFCST,FEBFCST,MARFCST....under category property against the ID 2016.01,2016.02,2016.03 Respectively.

what does *select(%timecur%,[id],TIME,[CURRENTMONTH]=X) for..

do I still need to keep ALLMONTHS property in time dimension or remove it .

[former_member186338]

I am completely lost...

In my previous message I presented 2 alternatives

If you select the first you don't need the second!

"do I still need to keep ALLMONTHS property in time dimension or remove it" - I do not understand what for is this property! If you need all months of the current year you can do it in many different ways... You may use YEAR property in CATEGORY dimension filled with current year...

[Former Member]

Thanks Vadim,

I got that and will test on the one of the options.

actually we are running package with selection prompts which we want to automate in script.

1. Category = AUGFCST (it should select current month Forecast)

2. Time = 2016.01 to 2016.12 (It should take all periods for the current year)

For time periods to be selected, I have created a property ALLMONTHS In time dimension and marked with X for all the periods for the current year.

in scrip I used the select state as this , but when I ran the package it is not taking the time period at all.

*SELECT (%TIMEYR%),[ID],TIME,[ALLMONTHS] = X

and I have replaced %TIMEYR% With %TIME_SET% in the remaining line of code.

buts it not working.

[former_member186338]

Instead of maintaining ALLMONTH property for all months you can maintain it for any single month of the Year!

Then:

*SELECT (%YR%,[YEAR],TIME,[ALLMONTHS] = X)

*SELECT (%TIMEYR%,[ID],TIME,[YEAR] = %YR% AND [LEVEL]=MONTH)

1 is better then 12 to maintain

And please be accurate with script logic syntax: *SELECT (%TIMEYR%),[ID],TIME,[ALLMONTHS] = X - incorrect!

[former_member186338]

P.S. And read about DESTINATION_APP syntax in help *DESTINATION_APP - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP Library

Not:

*SKIP_DIM = PARTNERCOMPANY

*SKIP_DIM = PARTNEROBJECT

*SKIP_DIM = COSTCENTER

But:

*SKIP_DIM = PARTNERCOMPANY,PARTNEROBJECT,COSTCENTER

same for other..

And I don't see *DESTINATION_APP statement in the provided code ...

[Former Member]

Thanks Vadim for your valuable inputs.

I will test the same and have made the changes as suggested to the code.

*Destination_App is also mentioned in the code.

please find attached script with the changes .

script1.txt.zip

[former_member186338]

Sorry, but if you look on the help once again you will see that not:

*RENAME_DIM COSTELEMENT=ACCOUNT_CS

*RENAME_DIM COMPANY=COMPCODE

*RENAME_DIM DATASRC=DTSOURC

But:

*RENAME_DIM COSTELEMENT=ACCOUNT_CS,COMPANY=COMPCODE,DATASRC=DTSOURC

[Former Member]

Hi Vadim, i have used *SELECT(%TIMECUR%,"[ID]",TIME,"[CURRMONTH] = 'X'") *SELECT(%CURCAT%,"[CATEGORY]",TIME,"[ID]= '%TIMECUR%'") *SELECT(%TIMEYR%,"[ID]",TIME,"[ALLMONTHS] = 'X'") *XDIM_MEMBERSET TIME = %TIMEYR% *XDIM_MEMBERSET CATEGORY = %CURCAT%

[Former Member]

Hi Vadim,

To automate the category and time for the package.

Category = AUGFCST (Current Month Forecast it has to pick from Category Dimension ID)

Time  = Current Year all periods.

i have made the changes as suggested and now testing the data.

please see the attached code which i have added.

here i have created properties in time dimension only and not created property in Category dimension.

i have replaced %TIMEYR% in the remaining line at the end .

i have run the data for year 2015 and category febfcst in the test run.

is there any other change i have to make .

Test Script2.txt.zip

[former_member186338]

Sorry, your changes are not related to my proposals...