×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

A distinct count in a group
4

A distinct count in a group

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.

Ken Hamady
http://www.kenhamady.com/
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?

Ken Hamady
http://www.kenhamady.com/
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.

Ken Hamady
http://www.kenhamady.com/
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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close