Sum of Grouped Distinct Counts
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22819
Printed Date: 15 Apr 2024 at 8:03pm
Topic: Sum of Grouped Distinct Counts
Posted By: tpreston
Subject: Sum of Grouped Distinct Counts
Date Posted: 04 Sep 2019 at 12:39pm
This report uses a TopN sort grouped by Company. It gathers a distinct count of unique units rented to that company.
So...
- If Company A rents Unit 100, the distinct unit count is 1.
- If Company A rents Unit 100, returns it, and later rents it again, the distinct unit count remains 1.
- If Company A rents Unit 100, returns it (or doesn't), and later rents unit 101, the distinct unit count becomes 2.
These scenarios are all correct and working fine. The problem I'm having is in totaling the distinct count. I'm attempting to total the distinct count results using a Running Total Field in the report summary.
An example of the problem occurs when Company A rents Unit 100, returns it, and Company B later rents the same unit.
Using that scenario, the group counts for each company are correct but the total for all companies (the report summary) is 1 when it should be 2.
I know this is because the Running Total in the report summary is also using distinct count...but I'm not sure how to get the correct results.
|
Replies:
Posted By: DBlank
Date Posted: 05 Sep 2019 at 2:27am
|
concatenate company and unit and do a distinct count of that
|
Posted By: tpreston
Date Posted: 05 Sep 2019 at 10:06am
|
Never thought of doing that. Simple, graceful and works perfectly. Thank you!
|
|