×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# A distinct count in a group4

## A distinct count in a group

(OP)
I have to create a report to show the percentage of pupils excluded from schools in their ethnic groups. The first group is on the school with a distinct count to show how many are registered at the school. The next group is Ethnic Origin and there is a distinct count on the pupil Id to show how many pupils in each ethnic group and a formula to show the percentage of pupils in each ethnic group. I have the exclusions table linked and I can count how many exclusions there have been in each ethnic group. What I need to do is show the percentage of pupils excluded in this group. This is where I am hitting the problem. If for instance there are 2 Chinese pupils in the school and one has been excluded 6 times and the other none the percentage displays as 300%. This is because I am using the exclusion Id for the distinct count. If I try to use the pupil Id I simply get the count of the students in the group. Help. Can anyone work out a formula so that it only counts the pupil once and could you also tell me where it goes? I wonder if I need another group?

Learn something new every day

### RE: A distinct count in a group

3
This workaround, discovered by DataPrincess, fools CR into putting a null value into the else line.  I will be posting it as a FAQ today.

1) Create a second formula called {@null} and give it the value "".
(This assumes that the field you are distinct counting is a character.      If it is a numeric, use 0 as the temporary null formula.)

2) Create the if-then-else formula:

if {field} = "X"
then {pupil.ID}
else {@null}

3) Save the if then else formula and go back to the @null formula.  Delete the contents of the null formula and save it empty.

4) Do distinct counts of the If-Then-Else formula

5) The report may generate an error on the first attemtp to run, but ignore this error, and the report should run fine from then on.  If you get a different behavior in your environment, let me know.

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

### RE: A distinct count in a group

(OP)
Ken

I have tried what you suggested. I used this formula;-

if{EXCLUSIONS.EXCL_ID} =0 (our id's are all numbers)
then {STUDENT.STUD_ID}
else{@null}

I wasn't quite sure what your {field} referred to. And I suppose I guessed wrong as it returned only 0's. It didn't like the "X" so I also guessed that I needed to put in a 0. Was I barking completely up the wrong tree?

Woof.

Learn something new every day

### RE: A distinct count in a group

How do you know from the record if the student has been excluded?

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

### RE: A distinct count in a group

(OP)
If there is an excl_id then they have been excluded. So I have tried to write a formula that says if stud_id has one excl_id then count it or if stud_id has more than one excl_id then only count it once. Does that make sense to you?

Learn something new every day

### RE: A distinct count in a group

(OP)
More thoughts ---- looking at my last reply it sounds as though I just need to count the stud_id but of course that's not so. I don't want to count it if there is no excl_id.

Learn something new every day

### RE: A distinct count in a group

You want to count the student if they HAVE an Exclusion, so you want Greater Than 0 I think.  Try this:

if {EXCLUSIONS.EXCL_ID} > 0
then {STUDENT.STUD_ID}
else{@null}

Then do a distinct count of this formula.

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

### RE: A distinct count in a group

(OP)
Ken

You star, my hero and any other flattering remark I can think of. That works!

Thank you so much.

Andrea

Learn something new every day

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!