Suppress records where subtotal = 0

[futbolista]

I am querying a database that contains debit and credit information. I have to query three years worth of data to be sure that I don't miss any records. I subtotal by another criterium, and wherever the subtotal is not = to zero, I have the information that I need. The problem is that 57 of a 60 page report subtotals to 0, so it is unnecessary. Can I suppress all records associated with a zero subtotal?

 

[dgillz]

Rather than supress the records, you need to exclude them from your report entirely (these are two different things, as suppressed records will still evaluate on your report).

Go to report, edit selection formula, group and enter a formula for Subtotal<>0

Let me know if you have any problems with this. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[AndrewBaines]

In the selection expert, use the group select.
eg
Sum ({Customer.Last Year's Sales}, {Customer.Country}) <> 0.00 Andrew Baines
Chase International

 

[Ngolem]

AndrewB....Don't think that you can use summary formulas in Selection criteria like that

dgillz....I don't think you can eliminate them that way since you must total many records potentially before you find out is the subtotla is zero....this cannot be done in the selection process...perhaps in a complicated SQL you could but not in a regular Crystal linking of tables.

futbolista .... if I read your problem correctly, you have a number of customers with debits and credits and you want your report to only show the customers with overall balances that are not zero...ie. there is no outstanding debit or credit.

You don't say how your report is structured but this could be as simple as putting the the following formula into the SECTION EXPERT conditional suppress for the section that displays the data

whilePrintingRecords;
{@subtotal} = 0;

it gets a little more complicated if there is more customer information (ie. you are currently displaying customer information in a header that you wish to suppress as well.

If this is the case describe the structure of your report and we shall suggest an alternate way of displaying the data.

Jim Broadbent

 

[futbolista]

Thanks for the input, folks.

I am trying to resolve an issue stemming from a report that my finance department is running against a NxTrend application on a Progress database using the Results reporting tool (primitive and ugly), so I am still trying to translate that report into a Crystal version using ODBC access. For those of you unfamiliar with Progress, relationships are established on the fly. In addition, there are some composite PK's involved, so I have my hands full. My initial gut reaction is that dgillz is correct in assuming that records cannot be eliminated before the subtotals are calculated. The &quot;WhilePrintingRecords&quot; suggestion looks like it may have some merit, because I am assuming that the condition will be tested after all Subtotal calculations are performed.

 

[AndrewBaines]

NGolem - funnily enough I copied the selection formula from the selection expert after I'd done the group select. Andrew Baines
Chase International

 

[dgillz]

You can absolutely use a selection formula to do this. This is a GROUP selection, not a RECORD selection, and it works fine. Since it is a GROUP selection and not a RECORD selection, crystal runs an additional pass through the report after the subtotals are calculated, removes the groups that do not fit the criteria, and then runs another pass to get the grand total.

Please try what I said and post if you have any questions. I guarantee you this will work.
Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[AndrewBaines]

Furthermore, if you're using CR8+ and a SQL based database, Crystal can pass the group selection to the server and return just summary records - much less data is returned and the report runs quicker.
Andrew Baines
Chase International

 

[futbolista]

I am discovering that the version of Progress that I am querying against has some ODBC issues with certain types of joins. I have no connection problems with WinSQL, but CR is not happy so far.