A distinct count in a group 4

[AyJayEL]

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

 

[kenhamady]

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.

 

[AyJayEL]

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

 

[kenhamady]

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.

 

[AyJayEL]

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

 

[AyJayEL]

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

 

[kenhamady]

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.

 

[AyJayEL]

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