Stumped

[TEM3]

I am using Crystal Reports 8.5 and Oracle 9i tables.

I have a table called ANALYST that contains the information about employees doing lab work. I have a table called LABREPT that list information about lab assignments including when they were completed and by whom. I have produced many reports listing who did what when. However the boss wants to include those who produced zero during a given time period.

How can I force the report to list all appropriate section analysts from the ANALYST table with a total of what they have done for a given time period ({LABREPT.Analyst Assigned}.{LABREPT.Date Completed}) and also include in the listing those analyst who did not complete any assignments with a zero in the total column?

The only thing I can think of is to create a subreport that counts the completed assignments and link it to the main report with the date range and the analyst and put the output of the subreport in the total assignment completed column. But even if that might work, perhaps there is a more efficient method.......

 

[Madawc]

Do a left-outer link from ANALYST to LABREPT. Do a summary count of some field in LABREPT, which should give zero or null when there are none. Make groups on the basis of this.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Is the date range hard coded or is it based on a parameter?

The subreport approach would work, or you could use a left join as Madawc suggests, but with no selection criteria based on the right hand table (containing the date). Instead create a formula like this:

if isnull({table.compldate}) or
not({table.compldate} in {?daterange}) then
0 else
1

Then insert a sum on this formula at the employee group level. There is another approach that could be implemented, but it wouldn't work with a date parameter.

-LB

 

[TEM3]

I linked as you suggested and then filtered the analysts I am interested in and grouped them. Then I calculated each months production within the group (analyst) by the following formula:

shared numbervar ac01;
shared numbervar ac02;
shared numbervar ac03;
shared numbervar ac04;
shared numbervar ac05;
shared numbervar ac06;
shared numbervar ac07;
shared numbervar ac08;
shared numbervar ac09;
shared numbervar ac10;
shared numbervar ac11;
shared numbervar ac12;

if year({LABREPT.Date Completed}) = 2007 then
(
if month({LABREPT.Date Completed}) = 1 then ac01 := ac01 + 1 else
if month({LABREPT.Date Completed}) = 2 then ac02 := ac02 + 1 else
if month({LABREPT.Date Completed}) = 3 then ac03 := ac03 + 1 else
if month({LABREPT.Date Completed}) = 4 then ac04 := ac04 + 1 else
if month({LABREPT.Date Completed}) = 5 then ac05 := ac05 + 1 else
if month({LABREPT.Date Completed}) = 6 then ac06 := ac06 + 1 else
if month({LABREPT.Date Completed}) = 7 then ac07 := ac07 + 1 else
if month({LABREPT.Date Completed}) = 8 then ac08 := ac08 + 1 else
if month({LABREPT.Date Completed}) = 9 then ac09 := ac09 + 1 else
if month({LABREPT.Date Completed}) = 10 then ac10 := ac10 + 1 else
if month({LABREPT.Date Completed}) = 11 then ac11 := ac11 + 1 else
if month({LABREPT.Date Completed}) = 12 then ac12 := ac12 + 1;
);

Worked. Thanks for pointing me in the right direction.