| Author |
Message |
JLH82
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 6
|
  
Topic: How to suppress a row with zero's & Divide by Zero
Posted: 24 Oct 2011 at 9:58am |
|
I have as column headers: Item #, QtyonHand, YTDQtySold, STdPrice, AveCost, LastCost
They are grouped by Item # without subtotals and grand totals, if the QtyOnHand is zero, I don't want it to be displayed.
I thought it was something like
if QtyOnHand = 0 then something else something
Not really sure what those somethings should be... and I'm pretty sure I need to put it in the Item # under the details and then the suppression formula...
Also, I'm getting a divide by zero error, the formula is:
formula = ({IM1_InventoryMasterfile.StdPrice} - {IM1_InventoryMasterfile.AveCost}) / {IM1_InventoryMasterfile.StdPrice} * 100
I've seen a couple of these on the forums, but I can't figure out how to turn it into an IF statement, this formula is another field called @AvgCostMargin which is inserted into the report under the details.
Any help would be greatly appreciated!
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Oct 2011 at 10:52am |
divide by zero
if {IM1_InventoryMasterfile.StdPrice}=0 then 0 else ({IM1_InventoryMasterfile.StdPrice} - {IM1_InventoryMasterfile.AveCost}) % {IM1_InventoryMasterfile.StdPrice}
suppression I assume is a group level summary of zero
ou can excude the group using a group select statement or you can suppress each of its section using a suppressino statement but both require the summry to be able to be obtained via an insert summry function (or its eqivalent)
SUM(value,group)=0
|
| IP Logged |
|
JLH82
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 6
|
Posted: 24 Oct 2011 at 3:30pm |
get another problem, if {IM1_InventoryMasterfile.StdPrice}=0 then
it says a statement is expected after "Then" I copied what you have, am I missing something? as for the suppression, I tried to get the subtotals and grand totals back, which would have been easy, but crystal just crashed... I'll try again in the morning. Thanks!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Oct 2011 at 4:11pm |
|
In the formula editor ther is a pick list for the language.
Change it to 'crystal syntax'
|
| IP Logged |
|
JLH82
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 6
|
Posted: 25 Oct 2011 at 4:47am |
|
Changed it, no more divide by zero error...
Now onto the suppression. Which way is easier? also, the field object I'm editing is the Group #1 Footer which holds the subtotals for the Item #'s, correct?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Oct 2011 at 4:50am |
|
you only need to suppress the group footer or the header, details and footer?
|
| IP Logged |
|
JLH82
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 6
|
Posted: 25 Oct 2011 at 4:59am |
|
only need to suppress the details
see design screen shot: http://tinypic.com/r/2vbk5kz/7
and the preview of the report: http://tinypic.com/r/21eyptu/7
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Oct 2011 at 7:26am |
in order to suppress details based on a group total the value has to be gotten via a SUM(field,group). It look like you have a straight forward design so you can use the sum of your quantity on hand at the item group level (group1)
in the section expert select the detail section and the fromula box for 'Suppress (no drill down)'
add your suppression formula here
sum(table.quantityonhand,tableitem)=0
|
| IP Logged |
|
JLH82
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 6
|
Posted: 25 Oct 2011 at 7:45am |
|
the above formula comes back with the error " The ) is missing" and I've checked it, i copied it and pasted it where you said for me to put it, and still the error... "The ) is missing" and it places the cursor after ( and before the "t"in table.
sum(table.quantityonhand,tableitem)=0
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Oct 2011 at 7:55am |
you have to replace the inner values with your actual table names and fields
sum(table.quantityonhand, tableitem)=0
an example would be like this..
sum({sales.monthlysales},{employee.id})=0
Edited by DBlank - 25 Oct 2011 at 7:56am
|
| IP Logged |
|
|
|
