Create report showing who has NOT worked

[Sandra456]

I need to create a report in our time-labor system that shows who has no time in a given time period. I have two tables with the pertinent data: PERSON table that has one record per employee with static data about an employee (emplid, name, wage, etc.) and a TOTALS table that has a record for each shift an employee works (emplid, date/time, duration, etc.). If an employee doesn’t work, then there’s no entry in the TOTALS table for him/her.

I’m able to run a query directly in the db using a minus operator to get the data, but have no idea how to translate the same idea in Crystal. Any ideas?

Sandra.

 

[kskid]

Try this

Create a report with two tables, PERSON and TOTALS

In the Visual linking, Perform a Left Outer Join

PERSON -> LOJ -> TOTALS

Click on Select Expert and place the following formula. If the time fraame is last week, then

{TABLE.DATE} IN LastFullWeek and
ISNULL({TOTALS.EMPLID))

Otherwise, if date parameter is a date range, then substitute

{table.date} in {?DateRange}

or if using start and end date parameters, then substitute

{table.date in {?StartDate} to {?EndDate}

That should give you everyone that has not worked.

-LW

 

[IdoMillet]

One option is to create a VIEW in your database using the query that's working for you. Then use the view as the data source.

Another option is to create a formula such as:

IF {table.date} in {?DateRange} THEN 1 ELSE 0

Group the report on employee and use a Group selection formula that restricts the employee groups to only those where the SUM of that formula is zero.

This assumes that you don't wish to see employees that have absolutely no work records.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com