Count Same Record Only Once in Group

[ramseyoma]

Here is my problem:

My report is grouped by Unit, Staff ID, Client Name.

I have several fields, but two of them indicate by "Y" or "N" if the client meets a certain criteria.

I used a formula that turns the "N" to a null.

The detailed section has been suppressed, so although a client might have several to 30 or more records, only one recorded is shown under the Group Staff ID.

What I need to do is to count the "Y" criteria only once per each client, not each client record. When I use the Count function, it counts each and every client record giving me a far greater count number then I want.

I would appreciate any assistance in this.

 

[Tease]

HAve you tried used a Distinct Count rather than a Count?

 

[ramseyoma]

Tease,

Thanks for the help. But the problem is that a distinct count will only count the "Y" once within the client grouping, that is will count only 1. I am working on using a varible "whileprintingrecords" formula that seems to be doing the job, but requires a varible formula for each grouping i.e., a sum of "Y" for staff, for unit, and grand total. If you have a better method please let me know. I have tried using running totals, but these get close but don't count the way I need to.

 

[IdoMillet]

As far as I know, Crystal doesn't allow you to do what you claim you are doing: "I used a formula that turns the "N" to a null".

Hence, what you are really getting there is a "" which does get counted (because it's not a real Null).

Solution:

Create a formula that returns a 1 or 0
Then SUM (not count) it. To count only ince for each group, use a Running Total with an "Evaluate" option set to "each Group".

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com