Script Logic

[former_member266029]

Hello:

I'm new on script logic and MDX sintaxis.

I need to built an script logic for a pxq calculation using this considerations:

For each material (0material):

Price: account dimension member P_001 - Stored only in one month 2014.JAN

Quantity: Account dimension member P_002 - Stored monthly 2014.JAN, 2014.FEB....2014.DIC

PXQ: stored the result in the account dimension member according to the property "CUENTA"of the respective material.

Please Help.

Thank you in advance.

Luciana

[former_member186338 · Accepted solution]

Hi Luciana,

Please add missing info in line with

Additional question: "PXQ: stored the result in the account dimension member according to the property "CUENTA"of the respective material." - usage of property is unclear!

Vadim

[former_member266029 · Accepted solution]

Thanks for the answer:

BPC 10 NW 801 SP 4

Dimension of the model: ACCOUNT (A), TIME (T), CATEGORY (C), ENTITY (E), CURRENCY (R), MATERIAL (User Def)

In the ACCOUNT dimension there are members PRICE (EXP),QUANTITY (EXP), OTHERS ACCOUNTS (EXP).

I need to built an script logic for a pxq calculation using this considerations:

For each material:

Price: account dimension member P_001 - Stored only in one month 2014.JAN

Quantity: Account dimension member P_002 - Stored monthly 2014.JAN, 2014.FEB....2014.DIC

PXQ: the result has to be stored in a respective account.  Each material has an account assigned in a property called "CUENTA".

In the image attached yo can see the example:

The user will input Price for one month and quantity for the complete year.

The cell in red are the pxq calculations stored in the respective account according to the material property.

Thank you Vadim.

Luciana

[former_member186338 · Accepted solution]

Hi Luciana,

DO YOU WANT THIS SCRIPT TO BE A DEFAULT.LGF OR DM PACKAGE?????????

I have to ask this question practically in any discussion!

Vadim

[former_member266029 · Accepted solution]

Hello Vadim:

DM Package with the following prompt values:

ENTITY: xxxxx

TIME: 2014.JAN, 2014.FEB....2014.DIC

(Next time I will consider all the tips to post a discussion of script logic)

Thanks Vadim.

Luciana

[former_member186338 · Accepted solution]

What do you mean by "TIME: 2014.JAN, 2014.FEB....2014.DIC"? The script will be always executed for all months of some year? Or?

If it will be executed for the whole year then it's better to ask user to enter (or select) year?

Vadim

[former_member266029 · Accepted solution]

Yes.

The user will enter all the base members of the year they want to execute.

Luciana

[former_member186338 · Accepted solution]

OK, then it's better to ask user to select Year with COMBOBOX prompt:

PROMPT(SELECTINPUT,,,,"%ENTITY_DIM%")

PROMPT(COMBOBOX,%Y%,"Select Year:",0,,{2013,2014,2015,2016,2017})

INFO(%EQU%,=)

INFO(%TAB%,;)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)

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,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM,Y%EQU%%Y%)

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

The script will be:

//$Y$ - DM variable will contain year selected by user

*XDIM_MEMBERSET ENTITY=%ENTITY_SET% //xxxxx - from SELECTINPUT

*XDIM_MEMBERSET ACCOUNT=P_002 //quantity

*XDIM_MEMBERSET TIME=$Y$.JAN,$Y$.FEB,$Y$.MAR...$Y$.DEC //list all months

*WHEN ACCOUNT

*IS * //scoped

*REC(EXPRESSION=%VALUE%*([TIME].[$Y$.JAN],[ACCOUNT].[P_001]),ACCOUNT=MATERIAL.QUENTA) //tuple expression used for price

*ENDWHEN

Vadim

[former_member266029 · Accepted solution]

Hi Vadim:

Thanks for the answer.

For this part of the  prompt :

PROMPT(SELECTINPUT,,,,"%ENTITY_DIM%") .

Im a using a user defined dimension not the entity. How is the sintaxis?

The dimension name is: CECO_CEBE.

THANKS!!

Luciana

[former_member186338 · Accepted solution]

Without %%:

PROMPT(SELECTINPUT,,,,"CECO_CEBE")

[former_member266029 · Accepted solution]

Hi vadim:

This my prompt:

Im getting this error when I execute the package:

Fail in the instruction /CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%

Thanks,

Luciana

[former_member186338 · Accepted solution]

But what chain you are using for this DM? The script was developed for Default Formulas chain...

Vadim

[former_member266029 · Accepted solution]

Tnak yo very much Vadim!

I changed the Chain to default Formulas and its working!

1 more question.

Is there a way to restrict the quantity for the zero values?

*XDIM_MEMBERSET ACCOUNT=P_002  -> I need to take values  greater than zero to restrict Data.

Thanks in advance.

Luciana

[former_member186338 · Accepted solution]

What do you mean by "restrict the quantity for the zero values"? Business case is not clear!

Quantity can be:

1. No record at all -> nothing will be selected in when/endwhen - nothing written

2. zero record (user cleared previous non zero value) -> zero will be written to destination.

3. non zero -> result of multiplication will be written

zero records will be deleted when light optimization will be done with zero elimination enabled...

Vadim

[former_member266029 · Accepted solution]

Hi Vadim:

Quantity can be:

1. No record at all -> nothing will be selected in when/endwhen - nothing written

You mean that the script skip that records when quantity has no record at all?

Thats what I want! Also to skip the records when quantity is zero.

Thanks,

Luciana

[former_member186338 · Accepted solution]

Looks not very logical:

1. User entered price and monthly quantities.

2. DM package was launched and calculated result stored.

3. Then user decided to correct monthly quantities and changed some values to zero.

4. You want DM to ignore this changes????

Vadim

P.S.

I can even recommend clearing everything in destination before calculation to ensure that zero elimination do not clear some records. The idea is to ensure consistency between price, quantity and result.

[former_member266029]

Hi Vadim:

For example:

In this case, i just want the script to run for 3 records that have quantity inputed , not for the all the records.

Thanks,

Luciana

[former_member186338]

To run the script for the inputed values you have to use default.lgf

The rest is unclear.

Vadim

P.S. Looks like none of my answers are helpful...

[former_member266029]

Thanks Vadim!!!!

You solved my problem.

Luciana