Report Builder question-how do I replace a negative number with

Yahoo Archive
1
Carolee

I use "iif" or "case" statements to eliminate unknowns. In this case
the statement like "iif(payhrs > 8, payhrs - 8,0)" should work. You
can also ignore unknown values when you aggregate your present
calculation. That's under "options" when you are editing the field.

HTH
RRD


On 6 Jun 2001, at 8:26, Carolee Elder wrote:

> This is a question for all you Report Builder Guru's out there......I have a
> calculated field on my report the takes the payhrs-8.00 to calculate the
> daily overtime hours, but if they don't have 8.00 hours it returns ????? (I
> went into the calculated fields properties and set it to include positive
> numbers only and to set unknown values as 0) but now instead of the negative
> #'s it's giving me the ?????. How can I make it return a zero or blank in
> those cases so that I can do a summary of the calculated field? Hope this
> makes sense, Thanks in advance for any help :)
>
>
>
> To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please go to: http://groups.yahoo.com/group/vantage/files/. Note: You must have already linked your email address to a yahoo id to enable access.
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>


Russ Dover
IS Manager
Weaver Industries, Inc.
717 336 7507 phone
717 336 4182 fax
rdover@...
www.weaverind.com
2
This is a question for all you Report Builder Guru's out there......I have a
calculated field on my report the takes the payhrs-8.00 to calculate the
daily overtime hours, but if they don't have 8.00 hours it returns ????? (I
went into the calculated fields properties and set it to include positive
numbers only and to set unknown values as 0) but now instead of the negative
#'s it's giving me the ?????. How can I make it return a zero or blank in
those cases so that I can do a summary of the calculated field? Hope this
makes sense, Thanks in advance for any help :)
3
The trick here is that a negative number is not an unknown value, so Report
Builder will not replace negative values with the zero you specified. In
this instance, you'd want your calculated field to read something like this:

iif(YourTable.YourField < 0,0,YourTable.Yourfield)

This will turn any negative values to zero.

Jon Hellebuyck
Stremel Manufacturing

-----Original Message-----
From:
sentto-20369-21586-991838065-jhellebuyck=stremel.com@...
[mailto:sentto-20369-21586-991838065-jhellebuyck=stremel.com@...
list.com]On Behalf Of Carolee Elder
Sent: Wednesday, June 06, 2001 9:27 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder question-how do I replace a negative
number with 0 or a blank so I can sum the field?


This is a question for all you Report Builder Guru's out there......I have a
calculated field on my report the takes the payhrs-8.00 to calculate the
daily overtime hours, but if they don't have 8.00 hours it returns ????? (I
went into the calculated fields properties and set it to include positive
numbers only and to set unknown values as 0) but now instead of the negative
#'s it's giving me the ?????. How can I make it return a zero or blank in
those cases so that I can do a summary of the calculated field? Hope this
makes sense, Thanks in advance for any help :)



To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please go to:
http://groups.yahoo.com/group/vantage/files/. Note: You must have already
linked your email address to a yahoo id to enable access.

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
4
IIF(payhrs>8.00,payhrs-8.00,0.00) - translated "If payhrs is greater than
8.00 then the calculated hours field will result in a value of payhrs minus
8.00,otherwise the calculated hours field will result in a value of 0.00".
You can then use this summarize this calculated field.

Terry L. Williams I.S. Manager Chipsco, Inc. 9936 Liberty St. Ext.
Meadville, PA 16335 814-333-6331 fax: 814-337-2548 tlw@...
www.chipsco.com

-----Original Message-----
From: Carolee Elder [mailto:celder@...]
Sent: Wednesday, June 06, 2001 10:27 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder question-how do I replace a negative
number with 0 or a blank so I can sum the field?

This is a question for all you Report Builder Guru's out there......I have a
calculated field on my report the takes the payhrs-8.00 to calculate the
daily overtime hours, but if they don't have 8.00 hours it returns ????? (I
went into the calculated fields properties and set it to include positive
numbers only and to set unknown values as 0) but now instead of the negative
#'s it's giving me the ?????. How can I make it return a zero or blank in
those cases so that I can do a summary of the calculated field? Hope this
makes sense, Thanks in advance for any help :)



To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please go to:
http://groups.yahoo.com/group/vantage/files/. Note: You must have already
linked your email address to a yahoo id to enable access.

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/