Suppress a section if the subtotals equals zero

[tepeh789]

I have a report where I need to calculate a remaining balance.

There is a billable invoice amount (A), which is one line appearing in the
group header #1 section (Customer). In group section #2 (Invoices), I want to
subtotal the invoiced detail. The detail section shows invoiced detail
records (B), with a subtotal in group footer section #2.

I need to calculate the remaining billable amount (A)-(B), which looks like
this:
Net Remaining:[A]-. The field appears in group footer section #1.

Now the question is: how do I suppress a section (detail records and
subtotals) if the Net Remaining field equals zero? In other words, supress
all customersthat have been fully invoiced.

Thanks for your assistance.

 

[Allen Browne]

You only get the total in the group footer. I don't think you will be
successful in trying to backtrack and then suppress the previous sections
(header and detail for that group) at that stage.

The solution will therfore be to remove the records from the report's
recordsource. This will mean adding an expression to the query that the
report is based on that eliminates those records that end up with a zero
balance.

The expression might involve a couple of DSum() expressions (to give you the
[A] - result you referred to), or possible a couple of subqueries.
Subqueries are much faster, but often give a 'multi-level group-by error'
when you base a report on them. You just might avoid that error provided you
don't actually pass the totals to the report report (e.g. if they appear
only in the WHERE clause of the query.)

It may turn out that you use a main report (based on a totals query), with a
subreport to show the details.