Subreport Only Of Records Not In Main Report

[dleewms]

I'm sure I'm just drawing a blank here, but any help would be appreciated.

I'm using Crystal XI with SQL Server. I have created a report of all of my employees who have clocked in during the week. Let's say this week there were 18 employees who clocked in. I have a view of all of my active employees. I need to see all of the active employees who don't have any time for this week included on the same report.

I tried a subreport. I placed it in the report footer section of the main report. The emp_id field is in the view for both the main report and the subreport. I used it as a linking field for the two reports and then went to the record expert of the subreport and changed it from equal to TO not equal to. Thinking that this would only include the records from the subreport which were not already in the main report. It didn't work.

I then created a shared variable in the main report and placed it in the report header which is not suppressed. I created a similar shared variable in the subreport and placed it in the report header. However, I discovered the shared variables were not available in the select records expert.

Now, I'm stuck....Any suggestions.

Below is an example of what I need. I have four employees: Bob Jones, Carmen Smalls, Ted Smith and Dan Cooke. Bob worked a total of 32 hrs this weeek and Carmen worked 35 hrs. I am grouping on the Employee ID and have sum of hours for each employee in the group footer. Neither Ted nor Dan worked this week. Currently the report displays:

GF1 Employee Name Sum(Hours Worked)
Bob Jones 32
Carmen Smalls 35

RF SUBREPORT
Ted Smith
Dan Cooke

Thanks in advance.

 

[briangriffin]

We could probably get this working in Crystal, but life would be a lot easier for you if you used a command object (or more likely save it as a view, and base the report on the view:

select
a.empid,
a.empname,
case when b.hours is null then 0 else b.hours end as hours

from employeetable a left outer join

(select empid, payperiod, sum(hours) as hours
from hourstable
group by empid, payperiod) b

on a.empid = b.empid

You may incorporate some logic to only pull the latest pay period, but you get the idea. If you get this working then the report writes itself.

 

[lbass]

I would not link the subreports. Instead have the subreport show all employees initially. Then in the main report, create a shared variable formula like this:

//{@accum} to be placed in the main report group footer section:
whileprintingrecords;
shared stringvar id := id + {table.emplid}+",";

In the sub, go into the section expert->group section (assuming you group on the id in the sub, also)->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar id;
{table.emplid} in id

This should leave only those employees not shown in the main report.

-LB

 

[dleewms]

I am back in the office on Thursday when I will try both solutions. Thank you both very much!