BAQ Calculated field division appears to be all integers

Epicor ERP 10
1

I am trying to use a simple formula that uses Division and the results are all Whole numbers - no fractions.
Anyone run across this before? I tested this on two different systems running different version of E10 and both do the same thing. The result is always a whole number.

I have the calculated field set as Decimal with four decimals.

Aaaaarg! I have to be missing something simple.

Any Ideas?

Here is my simple query,

select
[RcvHead].[Company] as [RcvHead_Company],
(1 / 7) as [Calculated_test]
from Erp.RcvHead as RcvHead

The result is all of the “test” fields show 0.0000

DaveO

2

Called Support and they figured it out.

You have to INLCUDE all the decimals in your formula otherwise the system assumes integer.

so (1 / 7) will show as 0.00
and (1.00 / 7.00) will show as 0.14

Aaaaaarg! are you kidding me.

DaveO

3

You have to INCLUDE all the decimals in your formula otherwise the system assumes integer.

so (1 / 7) will show as 0.00
and (1.00 / 7.00) will show as 0.14

…but the system displays the integer result with two decimal places :rofl:

“Epicor is…highly illogical.” - Spock

4

That’s a SQL thing, not Epicor.
Try SELECT 1/7 vs SELECT 1.0/7.0

And it shows decimals because you have that calculated field set to decimal. So you get 0 from SQL, then Epicor turns that into a decimal 0.00

2 Likes
5

Thanks for the clarification. I’ve gotten so accustomed to dealing with Epicor “quirks” that it’s an habitual assumption :wink:

Not that MicroSoft doesn’t have their own brands of “quirks” - like with SSRS.

Right now, working on a request to modify a Report - just add two Fields to a customized address block. Should be an easy fix.

Not with SSRS.

Here’s what one Expression looked like before copying the Report over from Test into Live :

 =
	IIF
	(
		( Len( Split( First( Fields!Calc_ShipToAddressList.Value, "OrderHed" ), "~" )( 5 ).ToString( ) ) > 0 ),
		IIF
		(
			( Split( First( Fields!Calc_ShipToAddressList.Value, "OrderHed" ), "~" )( 5 ).ToString( ) Like "United States*" ),
			"",
			( Split( First( Fields!Calc_ShipToAddressList.Value, "OrderHed" ), "~" )( 5 ).ToString( ) & CHR( 10 ) )
		)
		,
		""
	)

Here’s what that Expression looked like after copying the .Rdl into Live :

 =Microsoft.VisualBasic.Interaction.IIF((Microsoft.VisualBasic.Strings.Len(Microsoft.VisualBasic.Strings.Split(First(Fields!Calc_ShipToAddressList.Value, "OrderHed"), "~")(5).ToString()) > 0), Microsoft.VisualBasic.Interaction.IIF((Microsoft.VisualBasic.Strings.Split(First(Fields!Calc_ShipToAddressList.Value, "OrderHed"), "~")(5).ToString() Like "United States*"), "", Microsoft.VisualBasic.Strings.Split(First(Fields!Calc_ShipToAddressList.Value, "OrderHed"), "~")(5).ToString() & Microsoft.VisualBasic.Strings.CHR(10)), "")

So I get to re-translate all six Expressions into readable form before starting the actual work of adding the two new Fields :dizzy_face:

Plus a tremendous dis-incentive to do development in the Test environment to avoid this hassle in the future.

Thank you, Micro$oft …

“MicroSoft is…highly illogical.” - Spock