Distinct Count/Count problem

[JCook23]

CR 10.0
SqlServer

We have an Accounts Payable report that list all outgoing checks, the amounts, and what invoices they are paying. I have a distinct count on check number so we see the number of checks per vendor that we write per month. The report is grouped by Vendor. I do the distinct because some checks could cover three invoices so it is listed three times with three different amounts. This all works fine. HOWEVER.....

For some vendors, we pay by wire transfer. So, under Vendor ABC there may be 16 transactions all with a check number of 0. This is actually 16 different checks. Is there a way to do a distinct count on everything unless Check # <> 0 then do a count?

Thanks for any input.....

Jeff

 

[JCook23]

Ooops...

The last statement should read:

Is there a way to do a distinct count on everything unless Check # = 0 then do a count?

 

[kskid]

Use Running totals

1. Create one running total using distinct count with a formula of
//#checkCount
{checkNo}<> 0

2. Create 2nd running total using count by of checks with a formula of

//#WireTransfer
{checkNo = 0

Place both in detail and vendor group sections and suppress detail section

3. Create Running total formula in group footer

//#checkTotal

{#checkcount} + {#wiretransfer}


3. Create formula in group footer