Script Logic REC Instruction

[Former Member]

Hello,

The below script is attempting to calculate the cost of production using cases and COGS housed in Fcst_Master_Data version.  After completing the calculation it should place the calculated values in the forecast version.  It seems to be calcuating correctly for the sku's that it selects but it is "missing" some sku's and I haven't been able to identify the reason some records are missed.

//Calculate COGS for each account using Frozen Standards

*XDIM_MEMBERSET RptCurrency = LC

*XDIM_MEMBERSET Company_Code = %Company_Code_SET%

*XDIM_MEMBERSET MEASURES = PERIODIC

*XDIM_MEMBERSET Version = Fcst_Master_Data

*XDIM_MEMBERSET Summary_Account =cogsRawMaterial, cogsPackageMaterial, cogsFixedOverhead, cogsDirectLabor, cogsVariableOverhead, cogsThirdPartyConv, cogsCorporateAdj, cogsDepreciation, cogsFixDepreciation

*XDIM_MEMBERSET SKU = BAS(SKU_ROLLUP)

*XDIM_MEMBERSET TIME =%TIME_SET%

*XDIM_MEMBERSET Profit_Center =BAS(%Profit_Center_SET%)

*WHEN Summary_Account

*IS*

*REC(EXPRESSION = (([Version].[Fcst_Master_Data],[Data_Source].[fromAPO],[Summary_Account].[Cases]) + ([Version].[Fcst_Master_Data],[Data_Source].[Input],[Summary_Account].[Cases])) * 1 *  ([Version].[Fcst_Master_Data],[Data_Source].[fromFrozenStds],[Time].[2016.001],[Channel].[18],[Profit_Center].[ADMINBU]),VERSION=Forecast)

*ENDWHEN

*COMMIT

Can anyone please help?

[Former Member · Accepted solution]

Thank you Vadim,  going forward I will ask questions in the format recommeded.    Plesae find below the additional information.

1. & 2. BPC NW 10 SP 11, Engine JAVASCRIPT

3. Full list of Dimensions: RptCurrency, Company_Code, Measures, Version, Summary_Account, Data_Source, Sku, Time, Profit_Center, Channel

4. The script is to be launced by DM package

5. Description of Logic: Cases * Raw Material= Cost of production.  Cases and COGS will be housed in version Fcst_Master_Data and the calculated value should be houed in Forecast version.  Current logic calculates correctly but does not calculate all sku's that have cases and standard cost.

6. Logic Script Revised based on your note above.

*XDIM_MEMBERSET RptCurrency = LC

*XDIM_MEMBERSET Company_Code = %Company_Code_SET%

*XDIM_MEMBERSET MEASURES = PERIODIC

*XDIM_MEMBERSET Version = Fcst_Master_Data

*XDIM_MEMBERSET Summary_Account =cogsRawMaterial, cogsPackageMaterial, cogsFixedOverhead, cogsDirectLabor, cogsVariableOverhead, cogsThirdPartyConv, cogsCorporateAdj, cogsDepreciation, cogsFixDepreciation

*XDIM_MEMBERSET SKU = BAS(SKU_ROLLUP)

*XDIM_MEMBERSET TIME =%TIME_SET%

*XDIM_MEMBERSET Profit_Center =BAS(%Profit_Center_SET%)

*XDIM_MEMBERSET Data_Source = LogicCalculated

*XDIM_MEMBERSET Channel = <ALL>

*WHEN Summary_Account

*IS*

*REC(EXPRESSION = (([Data_Source].[fromAPO],[Summary_Account].[Cases]) + ([Data_Source].[Input],[Summary_Account].[Cases])) * 1 *  ([Data_Source].[fromFrozenStds],[Time].[2016.001],[Channel].[18],[Profit_Center].[ADMINBU]),VERSION=Forecast)

*ENDWHEN

*COMMIT

7. DM Prompts: Company Code, Profit Center, and Time.

Please let me know if you need additional information.  I am new to logic script, apologies in advance for incomplete information.

[former_member186338 · Accepted solution]

In general you loop Cases! Then the script has to be:

*XDIM_MEMBERSET RptCurrency = LC

*XDIM_MEMBERSET Company_Code = %Company_Code_SET%

*XDIM_MEMBERSET MEASURES = PERIODIC

*XDIM_MEMBERSET Version = Fcst_Master_Data

*XDIM_MEMBERSET Summary_Account =Cases //Scope

*XDIM_MEMBERSET SKU = BAS(SKU_ROLLUP)

*XDIM_MEMBERSET TIME =%TIME_SET%

*XDIM_MEMBERSET Profit_Center =BAS(%Profit_Center_SET%)

*XDIM_MEMBERSET Data_Source = fromAPO,Input //scope!

*XDIM_MEMBERSET Channel = <ALL>

*WHEN Summary_Account

*IS * //Cases!

*FOR %ACC%=cogsRawMaterial, cogsPackageMaterial, cogsFixedOverhead, cogsDirectLabor, cogsVariableOverhead, cogsThirdPartyConv, cogsCorporateAdj, cogsDepreciation, cogsFixDepreciation

*REC(EXPRESSION =%VALUE%*([Data_Source].[fromFrozenStds],[Time].[2016.001],[Channel].[18],[Profit_Center].[ADMINBU],[Summary_Account].[%ACC%]),VERSION=Forecast,Data_Source=LogicCalculated,Summary_Account=%ACC%)

*NEXT

*ENDWHEN

And never use COMMIT for WHEN/ENDWHEN - useless!

Vadim

[Former Member · Accepted solution]

Wow, thanks Vadim! 

The item I'm not clear on is "*FOR %ACC%."   Is %ACC% a BPC keyword?  It is not a dimension, member, or property that we have setup.  I don't quite understand how that works.

Thank you again for your help.

[former_member186338 · Accepted solution]

*FOR %VAR%=MEM1,...

*NEXT

is a loop - please, read help!

I use it here just to create multiple *REC(..) lines for each Summary_Account member!

Use UJKT to test script and look on lgx result!

Vadim

*FOR / *NEXT - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP Library

P.S. Result

*REC(EXPRESSION =%VALUE%*([Data_Source].[fromFrozenStds],[Time].[2016.001],[Channel].[18],[Profit_Center].[ADMINBU],[Summary_Account].[cogsRawMaterial]),VERSION=Forecast,Data_Source=LogicCalculated,Summary_Account=cogsRawMaterial)

*REC(EXPRESSION =%VALUE%*([Data_Source].[fromFrozenStds],[Time].[2016.001],[Channel].[18],[Profit_Center].[ADMINBU],[Summary_Account].[cogsPackageMaterial]),VERSION=Forecast,Data_Source=LogicCalculated,Summary_Account=cogsPackageMaterial)

...

[Former Member · Accepted solution]

Again Vadim, thank you

I read through the help and now understand how it should work.  However, I am getting an error in my validation.

On the line with the *For statement

*FOR %ACC% = cogsRawMaterial, cogsPackageMaterial, cogsFixedOverhead, cogsDirectLabor, cogsVariableOverhead, cogsThirdPartyConv, cogsCorporateAdj, cogsDepreciation, cogsFixDepreciation

I receive the following validation error "unknown dimension name in keyword: Datasource

Do you know what could be causing this?

Best,

Esther

[former_member186338 · Accepted solution]

Screenshot, please!

Errors are often located not on the lines mentioned. And UJKT is the right tool to test scripts...

Vadim

[Former Member · Accepted solution]

[former_member186338 · Accepted solution]

And this is not UJKT!

I don't see the reason for error, but you  have to check - Dimension names and Member names are case sensitive!

Vadim

P.S. read:

Use Data Region to provide members for %xxx_SET% variables!

[former_member186338 · Accepted solution]

I see at least one error:

Version

and

VERSION

...

[Former Member · Accepted solution]

One final question for you, is it possible to make this more dynamic that in the data promtps the user could select either Budget or Forecast.  Depending on that selection the logic would use either Budgt_Master_Data version or Fcst_Master_Data version to retrieve the cases and cost of production.  Than, complete the calcuation and input the calcuated values in either Budget or Forecast depending on the selection.

The idea is to keep master data separate for forecast and budget and have the ability to run one package for both cycles.

Thank you for help and insight.

[former_member186338 · Accepted solution]

"One final question for you, is it possible to make this more dynamic that in the data prompts the user could select either Budget or Forecast. " - yes, just prompt!

But the rest is unclear, you have to explain details - including Version members to be used. In general you will have to create some property to store:

Version ID   Master property

Budget        Budgt_Master_Data

Forecast     Fcst_Master_Data

Then, when user will select version in %Version_SET%

You can find the Master  ID:

*SELECT(%MAST%,[Master],Version,[ID]=%Version_SET%)

...

*XDIM_MEMBERSET Version =%MAST%

...

*REC(...,Version=%Version_SET%,...)

...

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I hadn't used *select before so it took me a little while to understand that one.  Once I did, it worked perfectly.

Thank you again for all your help!

Esther

[former_member186338]

And some extra questions:

*XDIM_MEMBERSET Data_Source=????

*XDIM_MEMBERSET Channel=???

Also REC can be simplified:

*REC(EXPRESSION = (([Data_Source].[fromAPO],[Summary_Account].[Cases])+([Data_Source].[Input],[Summary_Account].[Cases]))*([Data_Source].[fromFrozenStds],[Time].[2016.001],[Channel].[18],[Profit_Center].[ADMINBU]),VERSION=Forecast)

Vadim

[former_member186338]

"Can anyone please help?" - Yes, but, please read:

Vadim