how to show summary when there are nulls

[jameys26174]

Hi all..
I have 2 simple tables:
Table1 - referral information
Table2 - employee information

I am trying to provide a simple rpt showing summary of referrals for each employee. Problem is - how to show summary when employee does NOT produce a referral (there is an employee name in table2 but nothing in table1)? Another words, I need to show that employee X did 0 referrals?

Thanks,
J

 

[Ngolem]

Again...no real details as to how you are doing the summary OR the structure of the report

[Guess mode ON]

If it is a simple count of referals (I'll ASSUME there is a left Outter Join to bring back the employee name) then a manual count of referals can be done.

I'll ASSUME there is only one Group (Table.employeename}

In the group header put an init formula

//@init (suppressed)
WhilePrintingRecords;
NumberVar Rcount := 0;


In the details place this formula

//@countReferal
WhilePrintingRecords;
NumberVar Rcount ;

if not isnull({Table.referal}) then
Rcount := Recount + 1;

then the result is displayed in the group footer

//@Displayreferal
WhilePrintingRecords;
NumberVar Rcount ;

"Number of referals: " + Totext(Recount,0);

[Guess mode OFF]

that should work


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[jameys26174]

Thank you so much for help.. sorry for data incomplete.

Structure looks like this:
//1 summary - based on employee name

Employee x = number of referrals

Not for sure if this is resulting in results I need - I need to show employee names summary (# of referrals)even if they didn't produce any referrals.

Rpt should look like this:
Jon 12 referrals
Terry 6 referrals
Sam 0 referrals

This might have to do w/ my dbase - access 2k. But I did make sure that Crystal has my link via Leftouter join.

Thanks,
J

 

[Ngolem]

based on your info...my method should work

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

I think the left join should be from the employee table to the referrals table. You would group on {table.employeename} and then create a formula {@referrals}:

if isnull({table.referral}) then 0 else 1

Then insert a summary (SUM, not count) on {@referrals}. I think this would show a zero for employees without referrals.

-LB

 

[jameys26174]

ahhh.. thank you all for your help.. It worked..

However, when I ran this formula it displayed as 1 even though they didn't produce a referral. I overcame this by writting a if statement -

if isnull({Referral.RefNo}) then "No Referrals"

... that way it would tell me who didn't produce referrals.

Again, than you all for your help.

J

 

[lbass]

I'm guessing you may have inserted a count on the formula, instead of the SUM that I suggested. A count of a formula will give you the number of times the formula appears, not the number of times that the result meets the criterion.

But, glad you got it working.

-LB