How to select group records in a Crystal Report based on a Formula Result

I have a report from Crystal Reports that is a statement run, i.e. prints hundreds of statements.

However, many statements have a zero opening balance and zero transactions, so I would like to filter them using the Select option.

I have tried to Select Group, and I can filter on the opening balance, but I cannot filter on the formula field that checks if have any transactions.  

I understand the reason, because the formula is calculated  after the select process.  

Other than rewriting the stored procedure that initially collects the data to filter the dataset, is there anyway to achieve this in Crystal Reports?
LVL 6
townsmaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
What is the formula that checks the transactions?

 What do you mean by "zero transactions"?  Do you mean that there are no transactions, or there are transactions, but the amounts are all 0?  Normally if you have no records for a group, the group won't appear on the report.

 James
townsmaAuthor Commented:
The formula adds the opening balance to the sum of a field in the rows, effectively giving us a closing balance.

Sorry, by zero transactions, there will always be at least one row, but that row may have a zero as a value for the item amount.
mlmccCommented:
You should be able to check for count of records = 1 and ending balance = 0 and starting balance = 0 then suppress the group

mlmcc
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

James0628Commented:
Sorry.  I was asking to see the actual formula.  If you're using group selection, you should be able to use something like
Sum ({transaction amount}, {group field}) <> 0

 So, I'm wondering what's in your formula that's preventing you from using it.

 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
townsmaAuthor Commented:
OK, thanks to you both, you both helped me find a working solution.

The final solution I use is:

Maximum({AccountsReceivable.InvoiceTotal}, {AccountsReceivable.AccountCode}) > 0 OR
Minimum({AccountsReceivable.InvoiceTotal}, {AccountsReceivable.AccountCode}) < 0 OR
Maximum({AccountsReceivable.OpeningBalance}, {AccountsReceivable.AccountCode}) > 0 OR
Minimum({AccountsReceivable.OpeningBalance}, {AccountsReceivable.AccountCode}) < 0

This covers all options, those with a zero opening balance, but has new transactions, and those with an opening balance, and no new payments or invoices.

Thanks for pointing me in the right direction.
James0628Commented:
It's not really important, but if each account only has one opening balance, and the same figure is just repeated in each record, then you don't really need to check both the Minimum and the Maximum for that.  If the field is the same in each record for an account, then the min and max will be the same, so you could just check one or the other.  For that matter, in that situation, I think you could just check the field

{AccountsReceivable.OpeningBalance} <> 0

 James
townsmaAuthor Commented:
I checked min and max, as the opening balance can be either a plus or minus number, just as individual invoice totals can be either, plus for invoice, minus for payment.

Many thanks
James0628Commented:
I understand that the opening balance is + or -, but if _every_ record for an account has that same value (which seems likely, since there is, presumably, only one opening balance for an account), the min and max will be the same.  Say you've got 5 records for an account and the opening balance is
-100
-100
-100
-100
-100

 Minimum and Maximum will both give you -100.  Or, you could just skip Minimum and Maximum and check the value in any record, and get -100.

 That idea won't work for the transaction amount, assuming that a single account could have some records with a 0 transaction amount, and some records with a positive or negative transaction amount.  If no account will have both, so you either have a record with a 0 transaction amount (meaning that there are no new transactions), or you have records with positive or negative amounts (and none with 0), then you could do the same thing for the transaction amount.  A 0 minimum or maximum (or a 0 in _any_ record) means no new transactions.  A non-0 minimum or maximum (or a non-0 in _any_ record) means that there are new transactions.

 Of course what you're doing should be fine.  It just didn't seem necessary for the opening balance, assuming that every record for an account has the same figure in that field.

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.