Problem with FACTOR in *REC statement

[Former Member]

Hi all,

I am trying to get this bit of script logic to work

*WHEN ACCDETAIL
*IS NON_FLOW                                                                                
*WHEN CONSOLVIEW
*IS LC                                                                                
*WHEN ACCOUNTL.RATETYPE
*IS END

*REC(FACTOR=0.6,ACCDETAIL=F_CLO_INTCO,ACCOUNTL=ACCOUNTL.PMATCH_ACCOUNT,CONSOLVIEW=EUR,ENTITY=INTCO.ENTITY,INTCO=ENTITY.INTCO)

*ENDWHEN

*ENDWHEN

*ENDWHEN

The problem is the factor. I can use any numeric value with no decimals (i.e. 1 or 2 or 20) and it works fine. If I try 0.6 as above, the calculation returns a 0 (as if the factor was 0) and if I try 0,6 the script won't validate. I also tried (0,6) which let me validate the script but also returned a calculated value of zero.

Surely there must be a way to use decimals in a factor.

Thanks,

Arnold

[Former Member]

Hi,

FACTOR of 0.6 is completely valid. When you say you are getting the value of 0, do you mean this value is in the SQL database or in the report? Please check the value in the database. How does it look like? Since you are running the logic in script, you should check in the FAC2 table.

[Former Member]

Apologies if I was a bit unclear. What I mean is that the system uses 0 as the factor.

So, I have a value of 2000 in LC. With a factor of 0.6 I would expect the result to be 1200, instead I get a result of 0. I have checked via EVDRE and directly in all three tables (FACT, FACTWB and FAC2). If in my script I enter a factor of 1.7 the result I get is 2000, and these tests have led me to believe that the script ignores the decimals of the factor.

@John, I have asked the DB Admin to give me the info as I do not have access to it. I will post it once I have it.

[Former Member]

Hi,

Have you maintained any style property for the accounts on which you are working?

[Former Member]

Hi,

no, we have not used the style property at all.

Thanks,

Arnold

[Former Member]

Hi,

Do you have any decimal figures for those accounts in the database? Or all of them are whole numbers?

[Former Member]

The base values that the script should multiply with the factor? They have decimals.

[Former Member]

Hi,

What happens if you write the code as

*REC(EXPRESSION = %VALUE% * 0.6, ACCDETAIL = F_CLO_INTCO, ACCOUNTL = ACCOUNTL.PMATCH_ACCOUNT, CONSOLVIEW = EUR, ENTITY = INTCO.ENTITY, INTCO = ENTITY.INTCO)

Do you have this code in the default logic or another logic?

Does this work?

[Former Member]

The code is not in DEFAULT.LGF but the file it is in is called by DEFAULT.LGF.

I will make the suggested change and see it that helps.

[Former Member]

Hi all,

thanks for your input, turns out it was the regional settings of the bpc_sysadmin user. Once we changed them, it all started working again.

[Former Member]

Hi Arnold,

Here is code including variable,

I had a similar problem, I replaced the logic to dimension formula at that time, it seems to be impossible in your case, therefore I used Memory variable like below.

*WHEN ACCDETAIL

*IS NON_FLOW

*WHEN CONSOLVIEW

*IS LC

*WHEN ACCOUNTL.RATETYPE

*IS END

*REC(EXPRESSION= 6,ACCDETAIL=F_CLO_INTCO,ACCOUNTL=ACCOUNTL.PMATCH_ACCOUNT,CONSOLVIEW=#EUR,ENTITY=INTCO.ENTITY,INTCO=ENTITY.INTCO)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*GO

*WHEN CONSOLVIEW

*IS #EUR

*REC(EXPRESSION=%VALUE%/10,NOADD,CONSOLVIEW=EUR)

*ENDWHEN

Let me know if the code is working.

Regards,

YH Seo

[Former Member]

Hi YH Seo,

That won't really work, the actual script in my application calculates the factor from two values and it will have 6 decimals, not one. Also, in BPC 5.1 and BPC 7.0 this worked absolutely fine, so there must be an easier way.

Thanks,

Arnold

[JohnL]

Hi Arnold,

When I run the following code in ApShell:

//Data record: ACTUAL,CE0004010,A005,AUSTRALIA,MANUAL,EUR,2008.JAN, 24

//Scope
*XDIM_MEMBERSET P_ACCT = CE0004010
*XDIM_MEMBERSET P_Activity = A005
*XDIM_MEMBERSET P_CC = AUSTRALIA
*XDIM_MEMBERSET P_DataSrc = MANUAL
*XDIM_MEMBERSET TIME = 2008.JAN
*XDIM_MEMBERSET RPTCURRENCY = LC

*WHEN CATEGORY
*IS ACTUAL

*WHEN RPTCURRENCY
*IS LC
 
*WHEN P_ACCT.RATETYPE
*IS AVG
 
*REC(FACTOR=0.6873823,CATEGORY=FORECAST,RPTCURRENCY=EUR)
 
*ENDWHEN
*ENDWHEN
*ENDWHEN

*COMMIT

I get a value of 27.4952920000 in my write back table so the FACTOR parameter is working for me.

What do you have for the format of your SIGNEDDATA column in your fact tables?

Check this in SQL Server Enterprise Manager.

Is it "decimal (25,10)"?

Thanks,

John