SubReport Side by Side not staying in line

Question

Building a report that contains two sub reports. SubReport1 may contain 5 lines While SubReport2 may contain 3 lines.

My issue is when printing the SubReport2 does not stay in line regardless of its group. If i set the group on the Main Report page or on a per sub report.

This is how the report is appearing

This is how i want the report to appear

Here is an example of my main report page with grouping on the ClerkNo The header i.e. "CLERK 1 Scott" Is just on the Sub Report, this was added to each sub report so i could identify if it was aligning up correctly The subreports are a MasterData to a query, Each sub report is using a different query (Although the column responses are the same, just querying the data using different values)

Is it possible to do this within FastReport? Or should i look at constructing this report differently

Main SQL Table querying, the Join in statements is only for fetching the

ID	SiteNo	TermNo	ClerkNo	ActionDateTime	ActionType	ForExport	ExtraInfo	SupervisorNo
304	1	1	1	2022-12-15 16:52:14.233	0	1	0	0
303	1	1	1	2022-12-15 16:45:01.813	1	1	0	0
302	1	1	8	2022-12-15 15:34:28.093	3	1	0	0
301	1	1	8	2022-12-15 15:33:29.150	0	1	0	0
300	1	1	7	2022-12-15 15:32:31.293	1	1	0	0
299	1	1	5	2022-12-15 15:05:53.187	0	1	0	0
298	1	1	1	2022-12-15 14:43:48.943	3	1	0	0
297	1	1	1	2022-12-15 14:42:19.870	4	1	0	0
296	1	1	3	2022-12-15 14:41:45.793	1	1	0	0
295	1	1	1	2022-12-15 14:39:56.510	3	1	0	0
294	1	1	4	2022-12-15 14:38:31.963	4	1	0	0
293	1	1	4	2022-12-15 14:28:17.300	3	1	0	0
292	1	1	3	2022-12-15 14:19:59.783	3	1	0	0
291	1	1	3	2022-12-15 14:08:26.387	4	1	0	0
290	1	1	4	2022-12-15 14:08:18.153	0	1	0	0
289	1	1	1	2022-12-15 14:08:07.453	0	1	0	0
288	1	1	1	2022-12-15 14:03:52.730	1	1	0	0
287	1	1	3	2022-12-15 12:07:30.337	3	1	0	0
286	1	1	3	2022-12-15 11:58:33.470	0	1	0	0

SubReport 1 Query

select TS.SiteNo,
   TS.ClerkNo,
   ClerkData.Name as ClerkName,
   ActionDateTime as ClockIn,

   (select ISNULL(MIN(ActionDateTime),0)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 1
      and TS2.ActionDateTime >= TS.ActionDateTime
   ) as ClockOut,

  (select (DATEDIFF(HOUR, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 1
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as HoursWorkedRounded,

    (select (DATEDIFF(MINUTE, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 1
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as MinutesWorked

from timestamp TS

left Join ClerkData on TS.ClerkNo = ClerkData.No and ClerkData.SiteNo =
   case
    when (Select [SiteProfileClerk] from CfgSites where No = TS.SiteNo) = -1 then TS.SiteNo
    else (Select [SiteProfileClerk] from CfgSites where No = TS.SiteNo)
   end

where ActionType = 0
and actiondatetime between :StartDate and :EndDate

SubReport 2 Query

select TS.SiteNo,
   TS.ClerkNo,
   ClerkData.Name as ClerkName,
   ActionDateTime as BreakStart,

         (select ISNULL(MIN(ActionDateTime),0)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 4
      and TS2.ActionDateTime >= TS.ActionDateTime
   ) as BreakEnd,

     (select (DATEDIFF(HOUR, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 4
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as BreakHours,

       (select (DATEDIFF(MINUTE, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 4
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as BreakMinutes

from timestamp TS

left Join ClerkData on TS.ClerkNo = ClerkData.No and ClerkData.SiteNo =
   case
    when (Select [SiteProfileClerk] from CfgSites where No = TS.SiteNo) = -1 then TS.SiteNo
    else (Select [SiteProfileClerk] from CfgSites where No = TS.SiteNo)
   end

where ActionType = 3
and actiondatetime between :StartDate and :EndDate

My Original SQL Query to collect the information and handle everything within SQL is as follow, the issue with this, is Break in and out can happen multiple times in a day, And i do not know of a way to display NULL for the Clock In / Out, instead SQL is using a one of the values (Clock in time) and displaying this against the Break Start / End an this makes it hard to display the information in a report. Ideally this query would be perfect if i could somehow show Null against a ClockIn ClockOut if BreakStart / BreakEnd is using

select TS.SiteNo,
   TS.ClerkNo,
   ClerkData.Name as ClerkName,
   ActionDateTime as ClockIn,

   (select ISNULL(MIN(ActionDateTime),0)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 1
      and TS2.ActionDateTime >= TS.ActionDateTime
   ) as ClockOut,

      (select ISNULL(MIN(ActionDateTime),0)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 3
      and TS2.ActionDateTime >= TS.ActionDateTime
   ) as BreakStart,

         (select ISNULL(MIN(ActionDateTime),0)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 4
      and TS2.ActionDateTime >= TS.ActionDateTime
   ) as BreakEnd,

  (select (DATEDIFF(HOUR, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 1
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as HoursWorkedRounded,

    (select (DATEDIFF(MINUTE, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 1
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as MinutesWorked,

     (select (DATEDIFF(HOUR, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 4
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as BreakHours,

       (select (DATEDIFF(MINUTE, ActionDateTime, (select MIN(ActionDateTime)
      from TimeStamp TS2
      where TS2.SiteNo = TS.SiteNo
      and TS2.ClerkNo = TS.ClerkNo
      and TS2.ActionType = 4
      and TS2.ActionDateTime >= TS.ActionDateTime
   )))) as BreakMinutes

from timestamp TS

left Join ClerkData on TS.ClerkNo = ClerkData.No and ClerkData.SiteNo =
   case
    when (Select [SiteProfileClerk] from CfgSites where No = TS.SiteNo) = -1 then TS.SiteNo
    else (Select [SiteProfileClerk] from CfgSites where No = TS.SiteNo)
   end

where ActionType = 0
and actiondatetime between '2022-11-20' and '2022-11-22'
order by ClockIn Desc

Desired output of this would be:

SiteNo	ClerkNo	ClerkName	ClockIn	ClockOut	BreakStart	BreakEnd	HoursWorkedRounded	MinutesWorked	BreakHours	BreakMinutes
1	1	Scott	2022-10-13 09:00:24.043	2022-10-13 17:00:24.043	2022-10-13 10:30:37.057	2022-10-13 10:35:37.057	7	0	0	5
1	1	Scott	NULL	NULL	2022-10-13 12:30:00.043	2022-10-13 13:00:00.043	0	0	0	30
1	1	Scott	NULL	NULL	2022-10-13 15:00:00.043	2022-10-13 15:10:00.043	0	0	0	10

Answer 1

Using a modified Sql Query you can do this with a single data band. You do not need to select any tables while adding this new data source. The query uses a cursor to iterate over the rows of a query, then logic to determine which data to add to a table variable for output.

(Below updated per comment)

DECLARE @MaxDate DATETIME = cast('99991231' AS DATETIME);

-- Vars to keep track of the current state
DECLARE @LastClerkNo BIGINT = -1;
DECLARE @ShiftEnd DATETIME = 0;

DECLARE @thisClockIn DATETIME;
DECLARE @nextClockIn DATETIME;
DECLARE @thisClockOut DATETIME;

-- Vars to hold the current row of the cursor
DECLARE @ID BIGINT;
DECLARE @SiteNo BIGINT;
DECLARE @TermNo BIGINT;
DECLARE @ClerkNo BIGINT;
DECLARE @ActionDateTime DATETIME;
DECLARE @ActionType INT;
DECLARE @ForExport INT;
DECLARE @ExtraInfo INT;
DECLARE @SupervisorNo BIGINT;

-- Table to output rows to
DECLARE @OutTableLastRowId BIGINT;
DECLARE @OutTable TABLE (ID BIGINT IDENTITY(1,1), SiteNo BIGINT, ClerkNo BIGINT, ActionType INT, ClockIn DATETIME, ClockOut DATETIME, BreakStart DATETIME, BreakEnd DATETIME);


-- Create the cursor that we will loop through
DECLARE TS_Cursor CURSOR FAST_FORWARD FOR 
    SELECT *
    FROM TimeStamp
    -- WHERE ActionDateTime>@ReportStartDate AND ActionDateTime<@ReportEndDate
    ORDER BY ClerkNo,ActionDateTime ASC
OPEN TS_Cursor

-- Fill the variables from the cursor
FETCH NEXT FROM TS_Cursor 
INTO @ID,@SiteNo,@TermNo,@ClerkNo,@ActionDateTime,@ActionType,@ForExport,@ExtraInfo,@SupervisorNo
 

-- Loop throwgh the rows of all TimeStamp events
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Start a new shift when appropiate
    IF(@ActionDateTime>ISNULL(@ShiftEnd,@ActionDateTime) OR @LastClerkNo<>@ClerkNo)
    BEGIN
        SET @thisClockIn = IIF(@ActionType=0,@ActionDateTime,NULL);
        SET @LastClerkNo=@ClerkNo

        -- Find when this shift ends, earliest of @thisClockOut and @nextClockIn, or it does not end
        SET @nextClockIn = (
            SELECT TOP(1) ActionDateTime 
            FROM TimeStamp TS
            WHERE TS.ClerkNo = @ClerkNo
                AND TS.ActionType = 0
                AND TS.ActionDateTime > ISNULL(@thisClockIn,@ActionDateTime)
            ORDER BY ActionDateTime ASC)

        SET @thisClockOut = (
            SELECT TOP(1) ActionDateTime 
            FROM TimeStamp TS
            WHERE TS.ClerkNo = @ClerkNo
                AND TS.ActionType = 1
                AND TS.ActionDateTime >= @ActionDateTime
                AND TS.ActionDateTime < ISNULL(@nextClockIn,@MaxDate)
            ORDER BY ActionDateTime ASC);

        SET @ShiftEnd = ISNULL(@thisClockOut,ISNULL(@nextClockIn,@MaxDate))

        INSERT INTO @OutTable(SiteNo,ClerkNo,ActionType,ClockIn,ClockOut,BreakStart,BreakEnd)
        VALUES(@SiteNo,@ClerkNo,@ActionType,@thisClockIn,@thisClockOut,NULL,NULL);
        SET @OutTableLastRowId=SCOPE_IDENTITY();
    END


    -- Get BreakStart
    IF(@ActionType=3)
    BEGIN
        IF(EXISTS (SELECT * FROM @OutTable WHERE ID=@OutTableLastRowID) AND (SELECT BreakStart FROM @OutTable WHERE ID=@OutTableLastRowId) IS NULL)
            UPDATE @OutTable SET BreakStart=@ActionDateTime WHERE ID=@OutTableLastRowId;
        ELSE
            INSERT INTO @OutTable(SiteNo,ClerkNo,ActionType,ClockIn,ClockOut,BreakStart,BreakEnd)
            VALUES(@SiteNo,@ClerkNo,@ActionType,NULL,NULL,@ActionDateTime,NULL);
            SET @OutTableLastRowId=SCOPE_IDENTITY();
    END 


    -- Get BreakEnd
    IF(@ActionType=4)
    BEGIN
        IF(EXISTS (SELECT * FROM @OutTable WHERE ID=@OutTableLastRowID) AND (SELECT BreakEnd FROM @OutTable WHERE ID=@OutTableLastRowId) IS NULL)
            UPDATE @OutTable SET BreakEnd=@ActionDateTime WHERE ID=@OutTableLastRowId;
        ELSE
            INSERT INTO @OutTable(SiteNo,ClerkNo,ActionType,ClockIn,ClockOut,BreakStart,BreakEnd)
            VALUES(@SiteNo,@ClerkNo,@ActionType,NULL,NULL,NULL,@ActionDateTime);
            SET @OutTableLastRowId=SCOPE_IDENTITY();
    END


    FETCH NEXT FROM TS_Cursor 
    INTO @ID,@SiteNo,@TermNo,@ClerkNo,@ActionDateTime,@ActionType,@ForExport,@ExtraInfo,@SupervisorNo
END



CLOSE TS_Cursor
DEALLOCATE TS_Cursor

-- Select our table to output the data
SELECT 
    OT.SiteNo,OT.ClerkNo,ClerkData.Name AS ClerkName,
    ClockIn, ClockOut, BreakStart, BreakEnd,
    FLOOR(DATEDIFF(MINUTE, ClockIn, ClockOut)/60) AS HoursWorked,
    DATEDIFF(MINUTE, ClockIn, ClockOut) - 60 * FLOOR(DATEDIFF(MINUTE, ClockIn, ClockOut)/60) AS MinutesWorked,
    FLOOR(DATEDIFF(MINUTE, BreakStart, BreakEnd)/60) AS BreakHours,
    DATEDIFF(MINUTE, BreakStart, BreakEnd) - 60 * FLOOR(DATEDIFF(MINUTE, BreakStart, BreakEnd)/60) AS BreakMinutes

FROM @OutTable OT
LEFT JOIN ClerkData on OT.ClerkNo = ClerkData.No 

As you can see from the screenshot of the designer below, you can add a group header to a single data band to provide headers for each Clerk.

To add a group header click 'Configure Bands' above the report title and right click on your data band. The group condition for the header is [detail.ClerkNo]

Also to prevent null dates displaying set the property 'HideZeros' to true on all detail fields.

Using your sample data the above produced the report below.

Note that ClerkNo=7 is missing since there was only a ClockOut timestamp (ActionType=1) and no breaks or ClockIn. If you do need that unrelated? ClockOut please add a comment.

(Updated per comment)