Summary of a Distinct Count

[SHoude]

Hello All!
I've hit a wall and I'm hoping that someone out there can provide some assistance.

Here's what I'm trying to do,
I have a report showing Nbr of Vendors per Invoice, I do this by grouping by Invoice and having a Distinct Count of Vendors and then supressing the detail section.
However I have other Groups above Invoice and I want to take this distinctcount in Group 3 and Sum it up to groups 1 and 2.
Confused yet? Here's an example of what I want

Invoices Vendors
Group1 - User: Jsmith 4 8
Group2 - Date 5/12 4 8
Group3 - Invoice 123456 1 2
Group3 - Invoice 123457 1 2
Group3 - Invoice 123458 1 2
Group3 - Invoice 123459 1 2

The problem is I can't Summarize on a Distinctcount field.
A distinct count would only give me 2 in Groups 1 and 2 and a count could be much higher than what I want, each invoice having potentially many lines.

Is there a way to do this? Or do I need to take a completely different approach?

Thanks in advance.

 

[synapsevampire]

Right click the fields in the details section and select format section->Insert Summary->Distinct Count and select insert summary for all groups.

Crystal will build them for you.

Now you can eliminate the fields from the details if need be.

-k

 

[SHoude]

Thanks for the help but that doesn't work. That just evaluates the group for distinct vendors. I want a summary of the distinctcount field, not another distinct count.

I'm running Crystal Reports 8.0 so for me it was a right-click on the field, Insert, then Summary, DistinctCount, insert Summary for all fields.
When I do that I get
Invoices Vendors
Group1 - User: Jsmith 4 2
Group2 - Date 5/12 4 2
Group3 - Invoice 123456 1 2
Group3 - Invoice 123457 1 2
Group3 - Invoice 123458 1 2
Group3 - Invoice 123459 1 2

Which is not what I'm looking for.

I hope that explains things a bit better

 

[Digitalcandy]

Create a Running Total Formula based on the "VendorID" or something that uniquely identifies the Vendor. Make this a "Count" running formula. For the group you want to sum reset the count on that group and make the change on every invoice.

Hope you understand.

 

[lbass]

The problem is, I'm guessing, that you might have the same vendors appearing in different invoices, but if they are from a different invoice you want them to contribute to the sum, like this:
DistinctCount
Inv#1 2
VendorA
VendorA
VendorB

Inv#2 3
VendorA
VendorC
VendorC
VendorD
Sum 5

--even though there are only 4 unique vendors. If this is what you are looking for, then you could concatenate two fields as follows to create {@Inv-Vendor}:

{Inv#}+{VendorID}

Then insert summaries on this for all group levels (This idea is compliments of Ken Hamady in a different thread).

If you really want only to count unique vendors across invoices, then running totals will work: Select vendorID, distinctcount, evaluate for every record, reset on change of Group 2. Create a separate one and have it reset on change of Group 3. Running totals must be placed in the group footer, however.

-LB

 

[SHoude]

That's it exactly. I'll try that suggestion now

 

[SHoude]

Thank you lbass! That did it.
I didn't think of merging the fields like that.

Thank you also Digital Candy, and vampire.