*REC EXPRESSION logic scripts

[Former Member]

Hi Experts,

i am having a trouble on my logic scripts specifically in *rec expression.

my scripts goes like this.

*XDIM_FILTER ACCOUNT = [ACCOUNT].Properties("CURR_ACCNT") = Y //(ONLY GL ACCOUNTS I WANT TO TRANSLATE, THE DATA ARE STORED IN "LC" IN CURRENCY DIM)

*XDIM_MEMBERSET ACCOUNT = R_BHT, R_SGD, R_USD //(ACCOUNTS WHICH I INPUTTED THE DATA INTO "NO_CURR" IN CURRENCY DIM)

*XDIM_MEMBERSET CURRENCY = %CURRENCY_SET% // I NEED TO SELECT ONLY THE CURRENCY I WANT TO TRANSLATE AND STORE THE DATA INTO SELETECTED CURRENCY

*WHEN ACCOUNT

*IS "R_&%CURRENCY_SET%" // this one to concatenate the "R_" + the selected currency.

*REC(EXPRESSION=%VALUE%*[ACCOUNT].[CURR_ACCNT],CURRENCY=%CURRENCY_SET%)

*ENDWHEN

but i got a error message.

[former_member186338 · Accepted solution]

Hi Marlon,

First of all, please read

Second: You can't combine *XDIM_FILTER ACCOUNT and *XDIM_MEMBERSET ACCOUNT

Third - don't use & to concatenate *IS R_%CURRENCY_SET% It will work only for single currency selected!

And why not to use some property in the ACCOUNT dimension like CUR and LOOKUP based on this property?

Vadim

[Former Member · Accepted solution]

Hi Vadim,

please see my reply with bold font

Second: You can't combine *XDIM_FILTER ACCOUNT and *XDIM_MEMBERSET ACCOUNT > should i tagged the the XDIM_MEMBERSET ACCOUNT into the account property of my XDIM_FILTER? please advise

Third - don't use & to concatenate *IS R_%CURRENCY_SET% It will work only for single currency selected! > i only need to translate one currency only depend on my selected currency, to avoid bulk of data. if they want to translate another currency, they need to run it again.

And why not to use some property in the ACCOUNT dimension like CUR and LOOKUP based on this property? > i used the property "CURR_ACCNT"  and tagged "Y" all the GL account, to avoid to translate all the the dummy accounts.

Thanks and Best Regards,

Marlon Manalo

[former_member186338 · Accepted solution]

If you want to create script for single currency translation then:

*XDIM_MEMBERSET CURRENCY = %CURRENCY_SET%

*XDIM_MEMBERSET ACCOUNT = R_%CURRENCY_SET%

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%*[ACCOUNT].[CURR_ACCNT],CURRENCY=%CURRENCY_SET%) //This is absolutely unclear!!!!

*ENDWHEN

Can you read

And provide required info?? Without calculation logic explanation it's a waste of time!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

i have a dummy member into accounts dimension called "R_USD", "R_SGD" etc. and saved the data into "NO_CURR" of currency dimension.

then the GL accounts i saved the data into "LC" of currency dimension.

i created a property called "CURR_ACCNT" and tagged "Y" all the true accounts or GL so that only the GL accounts will be translated and exclude all the dummy members.

for the formula of my "currency translation" is [ACCOUNT] from "R_USD" multiple by [ACCOUNT] from GL. and save the data into "USD" currency dimension.

i need only one currency translation at a time. and i want only the GL accounts to be translated.

i modify my scripts like this:

*XDIM_MEMBERSET CURRENCY = %CURRENCY_SET%

*XDIM_MEMBERSET ACCOUNT = R_%CURRENCY_SET%

*WHEN ACCOUNT.CURR_ACCNT

*IS Y

*REC(EXPRESSION=%VALUE%*R_%CURRENCY_SET%,CURRENCY=%CURRENCY_SET%)

*ENDWHEN

but i got a error message.

i hope this will be enough info.

Thanks and Best Regards

[former_member186338 · Accepted solution]

I will try to translate what you have written

1. You do not want to use standard currency conversion mechanism (strange, why?)

2. In the ACCOUNT dimension I have created number of accounts to store currency rates for number of currencies - R_USD, R_SGD, etc..

3. The rate is stored in this accounts with CURRENCY = NO_CURR - you have to explain other rules (other dimensions ENTITY, CATEGORY...???)!

4. ACCOUNT dimension has property CURR_ACCNT and it's set to "Y" for normal GL accounts, not for R_xxx accounts (strange name for this property - can lead to misunderstanding)

5. Data is stored originally in CURRENCY=LC

6. DM package with single CURRENCY selected has to be executed to perform currency conversion for this single currency: [CURRENCY].[LC]*([ACCOUNT].[R_USD],[CURRENCY].[NO_CURR])

Then the script will be (using tuple expression to get rate):

//%CURRENCY_SET% - contain single currency - like USD

*XDIM_MEMBERSET CURRENCY = LC //select original data in LC

*XDIM_FILTER ACCOUNT = [ACCOUNT].Properties("CURR_ACCNT") = Y //select normal accounts

*WHEN ACCOUNT

*IS * //already scoped! normal accounts!

*REC(EXPRESSION=%VALUE%*([ACCOUNT].[R_%CURRENCY_SET%],[CURRENCY].[NO_CURR]),CURRENCY=%CURRENCY_SET%)

*ENDWHEN

If you define ACCT property in CURRENCY dimension and fill it like: R_USD for USD etc...

Then (using LOOKUP to get rate):

*LOOKUP CURRENTMODEL //the real name of your model

*DIM CURRENCY="NO_CURR"

*DIM RATE: ACCOUNT=CURRENCY.ACCT

*ENDLOOKUP

*XDIM_MEMBERSET CURRENCY = LC //select original data in LC

*XDIM_FILTER ACCOUNT = [ACCOUNT].Properties("CURR_ACCNT") = Y //select normal accounts

*WHEN ACCOUNT

*IS * //already scoped! normal accounts!

*REC(EXPRESSION=%VALUE%*LOOKUP(RATE),CURRENCY=%CURRENCY_SET%)

*ENDWHEN

Hope it's clear!

Vadim

P.S. I do not recommend this approach, use standard rate cube! *RUN_PROGRAM CURR_CONVERSION - is fast and easy to setup!

[Former Member · Accepted solution]

Hi Vadim,

here is some answers to you question:

1. You do not want to use standard currency conversion mechanism (strange, why?) > my model is standard and I don’t have a rate model for this project, they configured it like this, I joined the project in the middle of dev. That is why the currency translation will happen in same model using script logic.

          e.g. in my revenue model,  into account dimension i used dummy accounts “R_USD, R_SGD ETC.” and send the data into “NO_CURR” and send the GL accounts into “LC” to translate is the GL accounts into specific rate for example in usd the formula will be, [R_USD(NO_CURR)] MULTIPLE BY [GL(LC)] .

The R_USD, R_SGD and GL accounts are in the same dimension using the same model

3. The rate is stored in this accounts with CURRENCY = NO_CURR - you have to explain other rules (other dimensions ENTITY, CATEGORY...???)!  I already defined them In the logic you created. See logic below.

*XDIM_MEMBERSET CURRENCY="LC"

*XDIM_FILTER ACCOUNT = [ACCOUNT].Properties("CURR_ACCNT") = Y

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=([ACCOUNT].[R_%CURRENCY_SET%],[CURRENCY].[NO_CURR],[PROFITCENTER].[PC_RATES],[MATGROUP].[NO_MATGRP])*%VALUE%,CURRENCY=%CURRENCY_SET%)

*ENDWHEN

i created this logic in my revenue model, where the the "R_USD" "R_SGD" were stored and have a successful status in running the DM package

.

my next problems is how can i pull the value of "R_USD" into different model.

Thank you very much for the support.

Thanks and Best Regards

[former_member186338 · Accepted solution]

"my model is standard and I don’t have a rate model for this project, they configured it like this, I joined the project in the middle of dev."

The previous project team made a serious mistake to avoid rate model. It's the time to fix it!!! Strange idea to continue living with the hand made currency conversion!

It's not a big job to correct!

Vadim

"my next problems is how can i pull the value of "R_USD" into different model." - LOOKUP...