Finding difference between subreports

169 Views

I have a report with users who have completed a check against their assigned assets within some location and date parameters. I've got a users table, a checks table and an asset table. I have the list of checks and users for the parameters in the report detail.

In the header, I have a subreport with the names of users in the location who completed an action within that period and another subreport with all users in that location.

I'm trying to add a third subreport to show the users within that location who did NOT complete an action within that period. I've tried to just duplicate and reverse the first subreport, changing the date checked parameters, but then I'd get users who checked outside the parameters. I only have a list of checks records with dates.

I've read about shared variables and have tried sharing the users from the current two subreports, then deduping into a third subreport, but I'm going in circles. I'm at a loss on this one.

Main report

 SELECT "equip_check"."description", "equip_check"."date_checked", "equip_check"."result", "tdrinv_rec"."operator", "vehinv_rec"."driver_2", "vehinv_rec"."v_e_p", "equip_check"."check_type_2", "vehinv_rec"."service_number", "tdrinv_rec"."operator_2", "equip_check"."chkd_by_srv_no"
 FROM   ("tranman_live"."dbo"."equip_check" "equip_check" INNER JOIN "tranman_live"."dbo"."tdrinv_rec" "tdrinv_rec" ON "equip_check"."chkd_by_srv_no"="tdrinv_rec"."service_number") INNER JOIN "tranman_live"."dbo"."vehinv_rec" "vehinv_rec" ON "equip_check"."fleet_no"="vehinv_rec"."fleet_no"
 WHERE  "tdrinv_rec"."operator" LIKE 'S12%' AND ("equip_check"."date_checked">={ts '2022-03-01 00:00:00'} AND "equip_check"."date_checked"<{ts '2022-04-01 00:00:00'}) AND "vehinv_rec"."v_e_p" LIKE 'P%'
 ORDER BY "tdrinv_rec"."operator", "vehinv_rec"."service_number"

Users at that location

 SELECT "tdrinv_rec"."surname", "tdrinv_rec"."service_number", "tdrinv_rec"."date_left", "tdrinv_rec"."operator", "tdrinv_rec"."operator_2"
 FROM   "tranman_live"."dbo"."tdrinv_rec" "tdrinv_rec"
 WHERE  "tdrinv_rec"."date_left" IS  NULL  AND "tdrinv_rec"."operator" LIKE 'S12%'
 ORDER BY "tdrinv_rec"."operator", "tdrinv_rec"."service_number"

Staff who made a check

 SELECT DISTINCT "equip_check"."date_checked", "tdrinv_rec"."operator", "vehinv_rec"."driver_2", "vehinv_rec"."v_e_p", "vehinv_rec"."service_number", "tdrinv_rec"."service_number", "tdrinv_rec"."operator_2", "tdrinv_rec"."surname"
 FROM   ("tranman_live"."dbo"."equip_check" "equip_check" INNER JOIN "tranman_live"."dbo"."tdrinv_rec" "tdrinv_rec" ON "equip_check"."chkd_by_srv_no"="tdrinv_rec"."service_number") INNER JOIN "tranman_live"."dbo"."vehinv_rec" "vehinv_rec" ON "equip_check"."fleet_no"="vehinv_rec"."fleet_no"
 WHERE  "tdrinv_rec"."operator" LIKE 'S12%' AND "vehinv_rec"."v_e_p" LIKE 'P%' AND ("equip_check"."date_checked">={ts '2022-03-01 00:00:00'} AND "equip_check"."date_checked"<{ts '2022-04-01 00:00:00'})
 ORDER BY "tdrinv_rec"."operator", "tdrinv_rec"."service_number", "tdrinv_rec"."surname"

I thought I'd be able to just compare the users in the location with the users who made a check, shade the checkers in green and those who didn't check in red, but from what I can tell that isn't possible between subreports. As basic as "if this staff number is not in the "users who checked" subreport, shade in red.

At this point, I'd even make another report entirely if I could only isolate those who didn't make a check in the period. Tried uploading an image with the layout to help demonstrate but getting "unable to authorize file" error.

Thanks