Display .01 as 0

[GCL2007]

I have a simple report that reads a database table. One of the fields from the database is a INVOICE.STILLTOPAY field. Occasionally this field will leave a .01 or a -.01 value. The user wants to just display this as a .00 variance. How would you do this? Would you create a formula saying
if STILLTOPAY=.01 or -.01 then 0 else STILLTOPAY
or is there a more elegant way of handling?
Thanks!

 

[kskid]

Pretty close. How about

if {Table.StillToPay} in [.01,-.01] then
    0
else
    {Table.StillToPay}

-lw

 

[GCL2007]

Thanks kskid,
Not quite sure what I'm doing wrong. Created formula
if {TABLE.STILLTOPAY} in [0.01,-0.01] then
0
else
{TABLE.STILLTOPAY}

Still showing -0.01 in the report. Any thoughts?

 

[CR85user]

try the following:

if abs({TABLE.STILLTOPAY}) in [0.01,-0.01] then
0
else
{TABLE.STILLTOPAY}

CR8.5 / CRXI - Discovering the impossible

 

[GCL2007]

Thanks CR85user,
But still getting the same results... Showing -0.01 as the result of that formula.

if abs({TABLE.STILLTOPAY}) in [0.01,-0.01] then
0
else
{TABLE.STILLTOPAY}

That didn't work.. Still getting -0.01 as a result....Not sure why that is not working. Any thoughts?

 

[LizSara]

haven't you got too many following zeroes in your formula compared to your original request?

"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you." ~ Nietzsche"

 

[GCL2007]

The report is showing -0.01 in the output. I get the same results whether I use
if abs({TABLE.STILLTOPAY}) in [.01,-.01] then
0
else
{TABLE.STILLTOPAY}

-or-

if abs({TABLE.STILLTOPAY}) in [0.01,-0.01] then
0
else
{TABLE.STILLTOPAY}

Any thoughts?

 

[CR85user]

Are you using any rounding on the displayed field?

To be certain - create a new formula:

//{@Absvalue}
abs({TABLE.STILLTOPAY})

Insert it into the report on the relevant row and look at the value shown with no formatting of the field enabled.

It may be that we need to either round the value in the same way formulaicly prior to comparing the value or it may highlight why the value is shown in the way it is.

'J

CR8.5 / CRXI - Discovering the impossible

 

[GCL2007]

abs({TABLE.STILLTOPAY}) returns 0.01 where the original value was -0.01

 

[lbass]

Try something like:

if {table.stilltopay} in -.01 to +.01 then
0 else
{table.stilltopay}

-LB

 

[GCL2007]

Thanks LB (and all)...
That did the trick.. Really appreciate it....