Suppress detail/group 1/group 2

[katididdy]

Hi, I'm writing an aging report in Crystal 8. What I'd like to do is suppress the detail section and group header/footer 2 if there isn't an open invoice. Can anyone assist?

 

[IanWaterman]

Not clear what you want to do.

You coulk try to exclude unopen invoices in your select statement.

Ian

 

[Madawc]

I assume each group has several invoices, and you don't want to see the group if all invoices are closed. But if one of them is open you want to see open and closed. (If you just want to see open invoices, that can be done by testing in record selection, saving a lot of work.)

Assuming you want to see both open and closed, a neat method would be to add the invoices twice. First just open invoices, then open invoices linked to all invoices using whatever it is you use to group. You'd also need a lower-level group based on invoice, since this method is likely to give you the same record several times.

Failing this, you might be able to do a summary total based on the open date. Maximum and minimum, which would probably not work if you do it on a date with null values, but you could use a formula field like

if isnull({OpenDate}) then "None" 
else ToText({OpenDate}, "dd/MM/yy")

This would give a maximum of 'None' when there are open invoices, you can suppress the group that way. (Groups can be selected using Report > Selection Formulas > Group in Crystal 10, not sure if 8 could do that.)

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[katididdy]

Below is an example of the date I am getting in return:

Cust# 2 has no open invoices, yet all the info prints. 5712 has an open invoice, and the invoice info prints.


cus_no cus no apply_to_no Doc Due Date doc_type total Days Late

2 //this is the group header group by customer 1st
//503582 is the 2nd group (invoice#)
2 503582 11/30/2005 I $64.60 1274 days late
2 503582 11/30/2005 P -$64.60
$0.00


5712
5712 749999 04/29/2009 I $40.38 28 days late
$40.38

5712 $40.38
how can i get just the open invoices to show? I've tried numerous equations which haven't helped.

 

[IanWaterman]

In group selection enter formula

sum(doctotal, cus_no) <> 0

this will suppress all cus_Id totals which total = 0

Ian

 

[katididdy]

That didn't work. I still have both open and closed on the report.

 

[IanWaterman]

Sounds like a rounding problem try

sum(doctotal, cus_no) <= -0.005 or sum(doctotal, cus_no) >= 0.005

Ian

 

[katididdy]

That did make a big difference in the number of pages, but I am still seeing open and closed invoices. I'm going to have to tweek this a lot to get only the open invoices to show. Thank you for your input. If you have any more suggestions, I will try them!

 

[lbass]

The group selection formula should be:

sum({table.amt},{table.invoice}) <> 0

Or if you want to round to the nearest dollar, use:

round(sum({table.amt},{table.invoice}),0) <> 0

-LB

 

[katididdy]

That worked...I now show only invoices that are open. Thank you! My next step is to only pull out those invoices that will be 60-90 past due in 10 days. I'm going to have the user enter in a date. That date - doc due date = x. If x is between 60-90, I want that open invoice to show.