Finding difference between subreports

[former_member789747]

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

[DellSC]

Unfortunately, you can only pass along single values in a subreport. I think the only way you're going to be able to do this is by using a Command (SQL Select statement) instead of joining tables together. (Thank you for posting all the SQL!)

You could avoid subreports by using the following SQL in a command in the main report:

SELECT 
  tr.surname, 
  tr.service_number, 
  tr.date_left, 
  tr.operator, 
  tr.operator_2,
  vr.driver_2,
  vr.v_e_p,
  ec.date_checked,
  Case 
    when ec.chkd_by_srv_no is null then 'No'
	else 'Yes'
  end case as checked
FROM tdrinv_rec tr
  LEFT OUTER JOIN equip_check ec
    ON tr.service_number = ec.chkd_by_srv_no
      AND ec.date_checked >= {?Check Date Start} 
      AND ec.date_checked < {?Check Date End}
  LEFT OUTER JOIN vehinv_rec vr
   ON equip_check.fleet_no=vehinv_rec.fleet_no
     AND vehinv_rec.v_e_p LIKE 'P%'    
WHERE tr.date_left IS NULL  
  AND tr.operator LIKE 'S12%'
ORDER BY 
  tr.operator, 
  tr.service_number

The case statement will return 'Yes' if the check has been completed and 'No' if it hasn't.

The left outer joins give all of the records in the tdrinv_rec table regardless of whether there's a corresponding record in the equip_check table. By putting the conditions into for the equip_check and vehinv_rec tables in the joins, you don't have to check for nulls or do anything special to get the data you're looking for.

I configured this with parameters for the start and end dates. You can add other parameters as needed if you use the joins for conditions on equip_check (ec) and vehinv_rec (vr) and the where clause for conditions on tdrinv_rec (tr).

For more information about working with commands and how to use parameters with them, see https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/.

If you keep the sort from the query and there are multiple service numbers for an operator, you can group by operator and easily show which of that operator's equipment has been checked and which hasn't. Or you can change the order by to:

ORDER BY 9 desc, 4, 2

and group by checked in descending order to show all of the equipment that has been checked and then all of it that hasn't been checked.

-Dell