Division problem

[Former Member]

I have a crystal sql command as follows

select uni.uprovince,sum(Tamt_r1 + Tamt_r2 +Tamt_r3 +Tamt_r4 +Tamt_r5) / sum(uheadcount) as averagesales, from uni join guide on uni.uguicode = guide.gcode join travel on uni.utracode = travel.tagent where uni.utracode ='{?tradecode}' and uni.udate between {^{{?date}} and {}{?date2}}

group by uni.uprovince

For the result of averagesales, I can see the value to hundred digit like 234.00 , if the value exceed hundred value like 1345.00,

which means when it exceed hundred value, it shows blank on the crystal report. I am using the sql command in VS 2008.

What's wrong with the field setting or the sql command. I run the sql command in visual foxpro, and it can return normal value.

Why crystal report limit the division result to hundred value.

Thanks.

[former_member11696 · Accepted solution]

Hello,

Try increasing the size of the field by dragging the right side to the right and refresh. Also look at the properties of the field in the report by right clicking on the field and then look at the rounding options etc.

If that doesn't fix it then try copying the SQL from Show SQL in the designer and then use, I am assuming you are using ODBC to connect, a ODBC test tool like SQLCON32 that takes CR and Foxpro out of the picture to see if it's ODBC doing this.

Try dropping one of the fields onto the report that is used in the formula that you do know is bigger then 1000 and see what happens.

Thank you

Don

[Former Member · Accepted solution]

Thanks Don,

I found that I can use explicit conversion function Int() for

Int(sum(Tamt_r1 + Tamt_r2 +Tamt_r3 +Tamt_r4 +Tamt_r5) /

sum(uheadcount)) as averagesales, then it can be shown in Crystal

Report. But the decimal is not pertained, so how can I let it show and

pertained the decimal.

Thanks

[former_member11696 · Accepted solution]

Hi Peter,

Not sure what you mean by "pertained the decimal"?

I think you are saying the decimal place is either removed or maybe it just doesn't display it?

If it is consistent in not showing it then try this:

Int((sum(Tamt_r1 + Tamt_r2 +Tamt_r3 +Tamt_r4 +Tamt_r5) / sum(uheadcount))/100)

If the above is not the answer then please show the values as you see them. That was an issue in one version of CR when using ODBC.

What version of Crystal are you using and are you fully patched?

Thank you

Don