Sub Report Question

[SamLN]

Hi Tek-heads

I have a crystal report based on Time sheet data, but I also need to include in the report the names of employee's who have not yet submitted a time sheet. I was thinking of using a sub report to show all employees without a time sheet.

Is it possible to create the sub report where it references the initial report and only displays employees that are not listed in the primary report?

If so, can you provide some instruction on how to achieve the above in terms of formulas or selection criteria?

Sam

 

[SamLN]

I think what I need is a variable to collect all of the employee numbers in the main report, and then the sub report to only display employee numbers that are NOT present in the variable from the main report.......
Not sure how to achieve this or if this is the best method.

 

[BobSuruncle]

Hi Sam,
You generally want to avoid subreports whenever possible, and handle things in the main report.

How is the data stored?
Is there a field in an employee table that lets you know if there's a timesheet or not?
Is it a date field that's NULL for no timesheet?

If you could describe the database somewhat, then we could offer some help.


Bob Suruncle

 

[BettyJ]

I believe this is almost similar to a report I created. Please check the link below.

http://tek-tips.com/viewthread.cfm?qid=1720758

But as BobSuruncle wrote, please provide more details on which tables your data is coming from.

 

[SamLN]

Hi Bob
thanks for your reply
A time sheet record is only created when a time sheet is submitted, so there are not any NULL records.

The data required for the report is the employee table "EMP" (emp no, name, pay location, department, etc.). The time sheet data has been collected with a command:
SELECT
T.EMPLOYEE_NO, T.TS_DATE, T.TS_CODE, T.TS_NET, A.DESCRIPTION AS CODE_DESCRIPTION, T.TS_STATUS, T.PROJECT_ID, T.TASK_ID,
PROJECT.DESCRIPTION AS PROJECT_DESCRIPTION, TASK.DESCRIPTION AS TASK_DESCRIPTION
FROM
TIMESHEET T
LEFT OUTER JOIN ALLDED A ON T.TS_CODE = A.CODE
LEFT OUTER JOIN PROJECT ON T.PROJECT_ID = PROJECT.PROJECT_ID
LEFT OUTER JOIN TASK ON T.TASK_ID = TASK.TASK_ID
WHERE T.TS_STATUS IN (0, 3)
UNION ALL
SELECT
T1.EMPLOYEE_NO, T1.TS_DATE, T1.CODE, T1.NET, A1.DESCRIPTION AS CODE_DESCRIPTION, 3, CAST('' AS VARCHAR(12)), CAST('' AS VARCHAR(12)),
CAST('' AS VARCHAR(50)), CAST('' AS VARCHAR(50))
FROM
TSNP T1
LEFT OUTER JOIN ALLDED A1 ON T1.CODE = A1.CODE
WHERE T1.CODE IN (SELECT LEAVE_CODE FROM LEAVESET)

The report is structured:
GH#1: Emp.Pay_Location
GH#2: T.Employee_No
GH#3: T.Project_Id & T.Task_ID & T.TSCode Combination

Sam