Computed Columns Dividing and Rounding

(1) Share

Report

Posted on by Greg's Mom

Hello,

Is it possible to control the rounding of the SysComputedColumn::Divide method for creating computed column in a view? I've noticed in AX 2012 when using the divide method, the value seems to be the floor taken to the 6th decimal place. For example, I created a simple table with two columns (Amount and Amount1) and a view which displays the value of both fields and a computed column that divides the two fields. My data set looks like this:

amount ---------------- amount1 --------------- viewfield1

10.0000000000000000 30.0000000000000000 0.3333330000000000
10.0000000000000000 60.0000000000000000 0.1666660000000000
10.0000000000000000 70.0000000000000000 0.1428570000000000
10.0000000000000000 90.0000000000000000 0.1111110000000000
7.0000000000000000 11.0000000000000000 0.6363630000000000
7.0000000000000000 22.0000000000000000 0.3181810000000000
22.0000000000000000 7.0000000000000000 3.1428570000000000

The code in the computed column method is straight forward:

public static server str getDivision()

{

return SysComputedColumn::divide(SysComputedColumn::returnField('myView', 'myDS', 'Amount'), SysComputedColumn::returnField('myView', 'myDS', 'Amount1'));

}

It seems that the SysComputedColumn::divide function divides the numbers and takes the floor after the 6th decimal. Is there any way to control the rounding here? I'd like to round the result at 10 decimal places instead of taking the floor at the 6th.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

Greg's Mom on at

Like (0)

Report

Forgot to say, thanks in advance if anyone has some ideas.

Was this reply helpful? Yes No
Faisal Fareed 10,796 User Group Leader on at

Like (1)

Report

Hi Brawndo,

You need to create new EDT same like Amount4decimal and set NoOfDecimals Property to 10 as required.

Assign this new EDT as ExtendedDataType property of your computed column field (real field).

Was this reply helpful? Yes No
Greg's Mom on at

Like (0)

Report

Thanks a lot, Faisal. That makes perfect sense. I'll try it out.

Was this reply helpful? Yes No
Greg's Mom on at

Like (0)

Report

Thanks for your suggestion, Faisal. I tried creating my own EDT of type real and set the NoOfDecimals and DisplayLength to 10 but I still see the values appear to be truncated after the 6th decimal place. I also checked the view definition in SSMS and I can see the columns in the source table and view are defined as numeric(32,16) so I figure the AX kernel is doing something to truncate these values. Any other suggestion would be greatly appreciated!

Was this reply helpful? Yes No
Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (1)

Report

Set 'NoOfDecimals' property = 10 of your EDT.

Was this reply helpful? Yes No
Martin Dráb 237,884 Most Valuable Professional on at

Like (2)

Report

Do you get correct results if you run the view directly (e.g. in SQL Server Management Studio)? If so, the view is all right and the problem must be in the handling of results in AX. It it's wrong already in database, look at the view definition in SSMS.

Was this reply helpful? Yes No
Verified answer

Douglas Noel 3,905 on at

Like (1)

Report

Hi Brawndo,

this is due to sql server rounding rules on decimals . The Division is scaled to 6 decimals on SQL Server side already.

see www.sqlteam.com/.../topic.asp

or msdn.microsoft.com/.../ms190476.aspx

see the following sql view

SELECT FLD1 -- Numeric 32,16 22.000000000000

,FLD2 -- Numeric 32,16 7.000000000000

,

CAST( (FLD1 / FLD2) AS NUMERIC (32,16)) As Fld3, -- 3.1428570000000000

( ( CAST( FLD1 As Float )) / (CAST(FLD2 As Float)) ) As Fld4, -- 3,14285714285714

CAST(( ( CAST( FLD1 As Float )) / (CAST(FLD2 As Float)) ) As NUMERIC(32,16) ) As Fld5 -- 3.1428571428571428

FROM [TableA]

to overcome this make a float division (non decimal) from ax

make a helper method within SysComputedColumn

public static client server str divideFloat(str _expression1, str _expression2)

{

return ' CAST( (' + _expression1 + ') As FLOAT) / CAST ( (' + _expression2 + ') As FLOAT)';

}

and on the view (don't forget to set the edt to 10 decimals) use the following line

return SysComputedColumn::divideFloat('FLD1', 'FLD2');

without this float casting (or onother casting) you don't seem to able to overcome the sql server scaling logic on dividing decimals

regards

Douglas

Was this reply helpful? Yes No
Faisal Fareed 10,796 User Group Leader on at

Like (1)

Report

Hi Brawndo,

You should give it try by setting NoOfDecimals = 10, I did it at myside and it worked. Do let me know with your findings

Was this reply helpful? Yes No
Greg's Mom on at

Like (0)

Report

Thanks, Douglas, I tried casting to float and it works. Luckly, the loss in precision from casting to float won't be an issue for my data set.

Thanks also Martin and Faisal. I appreciate the help. I'm not sure how the NoOfDecimals property is working for you, Faisal. Maybe you're on a different version of AX? I did try setting the NoOfDecimals to 10 and still I got the same view definition and the issues related to precision as described in Douglas' post.

Was this reply helpful? Yes No
Greg's Mom on at

Like (0)

Report

After revisiting this topic, this makes sense how we can come to this difference. Reading the article, we need to do the math on the precision and scale of the data types. My data types are Numeric(32,16) so the division goes over the maximum precision and the scale is reduced to 6 and truncated. If your data type has a smaller precision, you can actually get the correct result.

Was this reply helpful? Yes No