Crystal Reports Selection Criteria

Hi.

I have several tables relating to invoices and notices and charges, the main tables relevant to this question are 'tblNotice', tblInvoices' and tblCharge

There are many invoices related to a notice and many notices (with the same notice number but an additional field with a suffix code) related to a charge.

What i want to do is to return a list of charges where all the notices under a charge have a status of 'F' and all the invoice statuses are 'cancelled'.

I can get it to return the specific notices as mentioned above by using:

{tblinvoice.DESCRIPTION} = 'Cancelled' 
and
{tblnotice.status} = 'F'

Open in new window


If i use the above criteria it will provide me just invoices where they are cancelled, but there may also be invoices associated with the same charge that are not cancelled if that makes sense?

Cheers

Brendan
eyeisystemsAsked:
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:
The most efficient thing would be to write your own query (eg. in a CR Command, or a stored procedure in the db).  Then you could theoretically use something like the following to only include the invoice and notice numbers that do _not_ include a record that is _not_ "Cancelled" or "F" (ie. all of the records are "Cancelled" and "F"):

WHERE
tblinvoice.invoice_number NOT IN
 (SELECT tblinvoice.invoice_number WHERE tblinvoice.DESCRIPTION <> 'Cancelled')
AND
tblnotice.notice_number NOT IN
 (SELECT tblnotice.notice_number WHERE tblnotice.status <> 'F')


 In CR, assuming that you group by the Charge, you could use group selection.  Create a formula similar to the following and name it count_check (for example):

if IsNull ({tblinvoice.DESCRIPTION}) or {tblinvoice.DESCRIPTION} <> 'Cancelled' or
 IsNull ({tblnotice.status}) or {tblnotice.status} <> 'F' then
  1
else
  0

 If the fields will never be null, then you don't need the IsNull tests, although it won't hurt to leave them in.

 Then you'd go to Report > Selection Formulas > Group and enter a formula like this:

Sum ({@count_check}, {Charge group field}) = 0

 Obviously you'd replace {Charge group field} with the field that you used for the Charge group (eg. a Charge number/ID).

 The idea is that count_check produces a 1 for any record where tblinvoice.DESCRIPTION is not 'Cancelled', or tblnotice.status is not 'F'.  If the total from that formula for a group is 0, then no records in that group met those conditions, meaning that every record had 'Cancelled' and 'F'.

 If I understand what you're trying to do, that should work.  The basic problem with group selection is that it's really suppression.  CR still reads the records for all of the groups, but only shows you the groups that meet those conditions.  If you have a lot of data, that could make the report take longer than you'd like.  And since CR is still reading those other records, any CR summaries will include the groups that aren't shown, so if you need something like grand totals, you'll have to generate those "manually" (eg. using formulas and variables).

 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
eyeisystemsAuthor Commented:
Cheers James

That worked like a charm!
James0628Commented:
Glad to hear it.

  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.