3 Check-In Flash Report Shared by Jeff Richmond, The Well Community Church 5 days ago 16.0 Check-in, Kids, Reporting, Youth Intermediate Description Before we moved to Rock, our kids ministry team had a gnarly Excel spreadsheet where they spent many hours every week, manually compiling and calculating attendance numbers so that they could present our lead team with a nice clean attendance flash report. In my new quest to kill all Excel spreadsheets, that manual process was one of the first things I automated after moving to Rock. The Check-In Flash Report displays an overview of check-in totals for each group and schedule combination. Each group row is totaled up on the right side, each schedule column is totaled up at the bottom of the report, and a grand total for the entire report is displayed at the bottom right. The results can be filtered by check-in configuration, campus location, date range, active schedules, and whether the person has special needs. The schedules can also be grouped by day of the week. Customization Notes We have a special needs ministry that tracks their kids using a boolean person attribute. This flash report includes a filter to only show the numbers of people with special needs based on that attribute. If you would like to use this feature, then you will need to create the person attribute and update the code with the ID. If not, I have also included some comments in the code below to indicate what changes you'll need to make if your organization doesn't want/need the same functionality. How-To Create new page under Internal Homepage > Tools > Reporting or wherever makes most sense for your organization: Basic Settings > Page Title: Check-In Flash Report Basic Settings > Site: Rock RMS Basic Settings > Layout: Full Width Basic Settings > Icon CSS Class: fa fa-newspaper Advanced Settings > Body CSS Class: flash-report Advanced Settings > Page Routes: reporting/attendance/flash-report Add a Page Parameter Filter block to the Main zone Block Settings: Show Block Title: No Filter Button Size: Small Filters Per Row: 3 Filters: Check-In Configuration Name: Check-In Configuration Key: ConfigID Field Type: Single-Select Values: Verify whether the configPurposeID value matches the ID of the "Check-In Template" Group Type Purpose defined value in your system and update if necessary {%- assign configPurposeID = 142 %} //-------------------------------------- {%- capture options %} {%- grouptype where:'GroupTypePurposeValueId == "{{ configPurposeID }}"' sort:'Name' select:'new(Id, Name)' disableattributeprefetch:'true' securityenabled:'false' %} {%- for config in grouptypeItems -%} ,{{- config.Id }}^{{ config.Name -}} {%- endfor %} {%- endgrouptype %} {%- endcapture %} 0^[All Configurations]{{ options }} Default Value: [All Configurations] Location Name: Location Key: Location Field Type: Single-Select Values: Verify whether the campusTypeID value matches the ID of the "Campus" Location Type defined value in your system and update if necessary. {%- assign campusTypeID = 181 %} //-------------------------------------- {%- capture options %} {%- location where:'LocationTypeValueId == "{{ campusTypeID }}" && IsActive == true && ParentLocationId _= ""' sort:'Name' select:'new(Id, Name)' disableattributeprefetch:'true' securityenabled:'false' %} {%- for location in locationItems -%} ,{{- location.Id }}^{{ location.Name -}} {%- endfor %} {%- endlocation %} {%- endcapture %} 0^[All Campuses]{{ options }} Default Value: [All Configurations] Date Range Name: Date Range Key: DateRange Field Type: Date Range Active Schedules Only Name: Active Schedules Only Key: ActiveOnly Field Type: Boolean Control Type: Checkbox Special Needs Only (Optional) Name: Special Needs Only Key: SpecialNeedsOnly Field Type: Boolean Control Type: Checkbox Group By Day Name: Group by Day Key: GroupByDay Field Type: Boolean Control Type: Checkbox Add a Dynamic Data block to the Main zone Block Properties: Name: Flash Report Update Page: No Edit Criteria: Parameters: ActiveOnly=False;SpecialNeedsOnly=False;DateRange=1900-01-01,1900-01-01;ConfigID=0;Location=0;GroupByDay=False Remove SpecialNeedsOnly=False; if not applicable Customize Results with Lava: Yes Advanced > Panel Title Lava: {%- assign dateRange = PageParameter.DateRange | Default:'' | Split:',' -%} {%- assign startDate = dateRange[0] | Default:'' %} {%- assign endDate = dateRange[1] | Default:'' %} {%- assign startYear = startDate | Date:'yyyy' %} {%- assign endYear = endDate | Date:'yyyy' %} {%- assign thisYear = 'Now' | Date:'yyyy' -%} Flash Report ( {%- if startYear != thisYear %}{{ startDate | Date:'MMM d, yyyy' }}{% else %}{{ startDate | Date:'MMM d' }}{% endif %} – {%- if endYear != thisYear %} {{ endDate | Date:'MMM d, yyyy' }}{% else %} {{ endDate | Date:'MMM d' }}{% endif -%} ) Query: Update the variables on the first few lines of the query to match your system: @ConfigPurposeID should match the ID of the "Check-In Template" Group Type Purpose defined value @FilterPurposeID should match the ID of the "Check-In Filter" Group Type Purpose defined value @SpecialNeedsAttrID should either be removed or it should match the attribute ID of a boolean "Has Special Needs" person attribute. There are two other sections lower in the code that may need to be deleted as well if you aren't going to use this feature. DECLARE @ConfigPurposeID AS int = 142 DECLARE @FilterPurposeID AS int = 145 DECLARE @SpecialNeedsAttrID AS int = 10360 --****** Delete if not applicable ****** ---------------------------------------- SET NOCOUNT ON --parse the date range parameter SELECT CAST(Value AS date) AS Date INTO #DATERANGE FROM STRING_SPLIT(@DateRange, ',') DECLARE @StartDate AS date DECLARE @EndDate AS date SELECT @StartDate = MIN(Date) FROM #DATERANGE SELECT @EndDate = MAX(Date) FROM #DATERANGE DROP TABLE #DATERANGE --make sure the start and end dates contain a value DECLARE @RightNow AS datetime2 = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' IF @StartDate = '1900-01-01' BEGIN SET @StartDate = CAST(FORMAT(DATEADD(week, -1, @RightNow), 'yyyy-MM-dd') AS date) END IF @EndDate = '1900-01-01' BEGIN SET @EndDate = CAST(FORMAT(@RightNow, 'yyyy-MM-dd') AS date) END; -- find all of the check-in areas WITH CheckInAreas AS ( SELECT PT.ID AS ConfigID, PT.GroupTypePurposeValueID AS ConfigPurposeID, PT.[Name] AS Config, PT.IconCSSClass, PT.[Order] AS ConfigSort, CT.ID AS AreaID, CT.GroupTypePurposeValueID AS AreaPurposeID, CT.[Name] AS Area, CT.[Order] AS AreaSort, 1 AS Depth FROM GroupTypeAssociation GTA INNER JOIN GroupType PT ON GTA.GroupTypeId = PT.ID INNER JOIN GroupType CT ON GTA.ChildGroupTypeId = CT.ID WHERE PT.GroupTypePurposeValueID = @ConfigPurposeID AND CT.GroupTypePurposeValueID <> @FilterPurposeID AND (PT.ID = @ConfigID OR @ConfigID = 0) UNION ALL SELECT CA.ConfigID, CA.ConfigPurposeID, CA.Config, CA.IconCSSClass, CA.ConfigSort, GTA.ChildGroupTypeID AS AreaID, CT.GroupTypePurposeValueID AS AreaPurposeID, CT.[Name] AS Area, CT.[Order] AS AreaSort, CA.Depth + 1 AS Depth FROM GroupTypeAssociation GTA INNER JOIN CheckInAreas CA on CA.AreaID = GTA.GroupTypeID INNER JOIN GroupType CT ON GTA.ChildGroupTypeID = CT.ID WHERE GTA.GroupTypeID <> GTA.ChildGroupTypeID AND CA.Depth < 10 --don't fall down the rabbit hole ) SELECT * INTO #CHECKINAREAS FROM CheckinAreas GROUP BY ConfigID, ConfigPurposeID, Config, IconCSSClass, ConfigSort, AreaID, AreaPurposeID, Area, AreaSort, Depth -- get all of the attendance records for each specific group SELECT CA.ConfigID, SC.ID AS CategoryID, ISNULL(LC.ID, ISNULL(LB.ID, LR.ID)) AS LocationID, GT.ID AS AreaID, G.ID AS GroupID, CA.Config, CA.IconCSSClass, SC.[Name] AS Category, ISNULL(LC.[Name], ISNULL(LB.[Name], LR.[Name])) AS [Location], GT.[Name] AS Area, CASE WHEN PG.Id IS NULL THEN G.[Name] ELSE PG.[Name] + ' > ' + G.[Name] END AS [Group], CA.ConfigSort, ISNULL(SC.[Order], 0) AS CategorySort, GT.[Order] AS AreaSort, CASE WHEN PG.Id IS NULL THEN G.[Order] ELSE PG.[Order] END AS PGroupSort, G.[Order] AS GroupSort, SD2.[Day], SD2.[Time], CASE WHEN @GroupByDay = 'true' THEN -1 ELSE SUBSTRING(SD2.[Time], 1, 2) END AS [Hour], CASE WHEN @GroupByDay = 'true' THEN -1 ELSE SUBSTRING(SD2.[Time], 3, 2) END AS [Minute], COUNT(*) AS Total INTO #ATTENDANCE FROM Attendance A INNER JOIN AttendanceOccurrence AO ON AO.Id = A.OccurrenceId INNER JOIN [Group] G ON G.Id = AO.GroupId INNER JOIN GroupType GT ON G.GroupTypeId = GT.ID INNER JOIN #CHECKINAREAS CA ON CA.AreaID = G.GroupTypeID INNER JOIN Schedule S ON AO.ScheduleID = S.ID CROSS APPLY ( SELECT DATEPART(dw, AO.OccurrenceDate) AS [Day], CASE @GroupByDay WHEN 'true' THEN '1200' ELSE --attempt to get the most accurate schedule time CASE WHEN AO.OccurrenceDate > S.ModifiedDateTime THEN --if the occurrence happened after the last time the schedule was modified, then we can assume the time of day on the schedule is still accurate CASE WHEN ISNULL(S.iCalendarContent, '') = '' THEN FORMAT(CAST(S.WeeklyTimeOfDay AS datetime),'HHmm') ELSE SUBSTRING(S.iCalendarContent, PATINDEX('%DTSTART:________T______%', S.iCalendarContent) + 17, 4) END ELSE --if the schedule was modified after the occurrence, then assume the schedule time may have changed. --instead, 'estimate' the schedule time by rounding the attendence start time down to the nearest hour. --TODO: find a better way to estimate the schedule time without using the schedule record FORMAT(A.StartDateTime, 'HH00') END END AS [Time] ) AS SD CROSS APPLY ( SELECT CASE SD.[Day] WHEN 1 THEN 8 ELSE SD.[Day] END AS [Day], CASE SD.[Time] WHEN '' THEN '1200' ELSE SD.[Time] END AS [Time] ) AS SD2 --****** Delete section if not applicable ****** CROSS APPLY ( SELECT ISNULL(SNV.Value, 0) AS HasSpecialNeeds FROM PersonAlias PA INNER JOIN Person P ON P.ID = PA.PersonID LEFT JOIN AttributeValue SNV ON SNV.EntityID = P.ID AND SNV.AttributeID = @SpecialNeedsAttrID WHERE PA.ID = A.PersonAliasID ) SN --****** End Delete ****** LEFT JOIN Category SC ON S.CategoryID = SC.ID LEFT JOIN [Group] PG ON PG.ID = G.ParentGroupID LEFT JOIN Location LR ON LR.ID = AO.LocationID LEFT JOIN Location LB ON LR.ParentLocationID = LB.ID LEFT JOIN Location LC ON LB.ParentLocationID = LC.ID WHERE A.DidAttend = 1 AND AO.OccurrenceDate BETWEEN @StartDate AND @EndDate AND (S.IsActive = 1 OR @ActiveOnly = 'false') --****** Delete line if not applicable ****** AND (SN.HasSpecialNeeds = 'true' OR @SpecialNeedsOnly <> 'true') --****** End Delete ****** AND (ISNULL(LC.ID, ISNULL(LB.ID, LR.ID)) = @Location OR @Location = 0) GROUP BY PG.Id, SC.ID, ISNULL(LC.ID, ISNULL(LB.ID, LR.ID)), GT.ID, G.ID, CA.ConfigID, CA.Config, CA.IconCSSClass, CA.ConfigSort, GT.[Name], GT.[Order], PG.[Name], PG.[Order], G.[Name], G.[Order], SC.[Name], SC.[Order], SD2.[Day], SD2.[Time], ISNULL(LC.[Name], ISNULL(LB.[Name], LR.[Name])) -- calculate the total counts for each group, area, location and config -- Group Attendance SELECT ConfigID, ISNULL(CategoryID, 0) AS CategoryID, ISNULL(LocationID, 0) AS LocationID, ISNULL(AreaID, 0) AS AreaID, COUNT(*) AS GroupCount INTO #GROUPCOUNT FROM (SELECT ConfigID, CategoryID, LocationID, AreaID, GroupID FROM #ATTENDANCE GROUP BY ConfigID, CategoryID, LocationID, AreaID, GroupID) A GROUP BY ConfigID, CategoryID, LocationID, AreaID -- Area Attendance SELECT ConfigID, CategoryID, LocationID, SUM(GroupCount) AS AreaCount INTO #AREACOUNT FROM #GROUPCOUNT GROUP BY ConfigID, CategoryID, LocationID -- Location Attendance SELECT ConfigID, CategoryID, SUM(AreaCount) AS LocCount INTO #LOCCOUNT FROM #AREACOUNT GROUP BY ConfigID, CategoryID -- Configuration Attendance SELECT ConfigID, SUM(LocCount) AS CatCount INTO #CATCOUNT FROM #LOCCOUNT GROUP BY ConfigID -- gather all of the distinct schedule days & times SELECT ROW_NUMBER() OVER(ORDER BY [Day], Hour24, [Minute]) AS RowNum, * INTO #SCHEDULES FROM (SELECT [Day], CASE [Day] WHEN 2 THEN 'M' WHEN 3 THEN 'T' WHEN 4 THEN 'W' WHEN 5 THEN 'Th' WHEN 6 THEN 'F' WHEN 7 THEN 'S' WHEN 8 THEN 'Su' ELSE '' END AS [DayName], CASE WHEN @GroupByDay = 'true' THEN -1 ELSE ISNULL(SUBSTRING([Time], 1, 2), 0) END AS Hour24, CASE WHEN @GroupByDay = 'true' THEN -1 ELSE ISNULL(SUBSTRING([Time], 1, 2) % 12, 0) END AS Hour12, CASE WHEN @GroupByDay = 'true' THEN -1 ELSE ISNULL(SUBSTRING([Time], 3, 2), 0) END AS [Minute], SUM(Total) AS Total FROM #ATTENDANCE GROUP BY [Day], [Time]) A ORDER BY [Day], Hour24, [Minute] --select the data --TABLE 1 - Attendance SELECT A.ConfigID, A.CategoryID, A.LocationID, A.AreaID, A.GroupID, A.IconCSSClass, A.Config, ISNULL(A.Category, 'N/A') AS Category, ISNULL(A.[Location],'N/A') AS [Location], A.Area, A.[Group], A.[Day], A.[Hour], A.[Minute], A.Total, S.RowNum AS ColNum, CC.CatCount, LC.LocCount, AC.AreaCount, GC.GroupCount FROM #ATTENDANCE A INNER JOIN #SCHEDULES S ON S.[Day] = A.[Day] AND S.Hour24 = A.[Hour] AND S.[Minute] = A.[Minute] LEFT JOIN #CATCOUNT CC ON CC.ConfigID = A.ConfigID LEFT JOIN #LOCCOUNT LC ON LC.ConfigID = A.ConfigID AND LC.CategoryID = ISNULL(A.CategoryID, 0) LEFT JOIN #AREACOUNT AC ON AC.ConfigID = A.ConfigID AND AC.CategoryID = ISNULL(A.CategoryID, 0) AND AC.LocationID = ISNULL(A.LocationID, 0) LEFT JOIN #GROUPCOUNT GC ON GC.ConfigID = A.ConfigID AND GC.CategoryID = ISNULL(A.CategoryID, 0) AND GC.LocationID = ISNULL(A.LocationID, 0) AND GC.AreaID = ISNULL(A.AreaID, 0) ORDER BY ConfigSort, Config, CategorySort, Category, [Location], AreaSort, Area, PGroupSort, GroupSort, [Group], GroupID, ColNum --TABLE 2 - Schedules SELECT * FROM #SCHEDULES --clean up DROP TABLE IF EXISTS #CHECKINAREAS DROP TABLE IF EXISTS #ATTENDANCE DROP TABLE IF EXISTS #SCHEDULES DROP TABLE IF EXISTS #CATCOUNT DROP TABLE IF EXISTS #LOCCOUNT DROP TABLE IF EXISTS #AREACOUNT DROP TABLE IF EXISTS #GROUPCOUNT Formatted Output: Verify whether the configPurposeID value matches the ID of the "Check-In Template" Group Type Purpose defined value in your system and update if necessary {%- assign configPurposeID = 142 %} //--------------------------------------------------------- //- get filter parameter values {%- assign dateRange = PageParameter.DateRange | Default:'' | Split:',' %} {%- assign startDate = dateRange[0] | Default:'' %} {%- assign endDate = dateRange[1] | Default:'' %} {%- if startDate == '' or endDate == '' %} {%- if startDate == '' %}{% assign startDate = 'Now' | DateAdd:-1,'w' | Date:'MM/dd/yyyy' %}{% endif %} {%- if endDate == '' %}{% assign endDate = 'Now' | Date:'MM/dd/yyyy' %}{% endif %} {%- capture range %}{{ startDate | Date:'yyyy-MM-dd' }},{{ endDate | Date:'yyyy-MM-dd' }}{% endcapture %} {%- assign URL = 'Current' | SetUrlParameter:'DateRange',range %} {{ URL | PageRedirect }} {%- endif %} {%- assign configID = PageParameter.ConfigID | AsString | Default:'0' | AsInteger %} //- get the name of the selected check-in config {%- assign configName = '' %} {%- if configID != 0 %} {%- assign configName = table1.rows[0].Config | Replace:' Config','' | Replace:' Check-In','' | Replace:' Check-in','' %} {%- endif %} //- calculate the total number of check-ins {%- assign grandTotal = 0 %} {%- for row in table1.rows %} {%- assign grandTotal = grandTotal | Plus:row.Total %} {%- endfor %} {%- capture title %}{{ configName | Default:'Total' }} Check-Ins{% endcapture %} {%- assign startYear = startDate | Date:'yyyy' %} {%- assign endYear = endDate | Date:'yyyy' %} {%- assign thisYear = 'Now' | Date:'yyyy' %} {%- capture label %} {%- if startYear != thisYear %}{{ startDate | Date:'MMM d, yyyy' }}{% else %}{{ startDate | Date:'MMM d' }}{% endif %} – {%- if endYear != thisYear %} {{ endDate | Date:'MMM d, yyyy' }}{% else %} {{ endDate | Date:'MMM d' }}{% endif %} {%- endcapture %} <div class="panel panel-block flash-report"> //- total attendance KPI <div class="panel-body"> {[ kpis size:'md' style:'card' columncount:'0' title:'{{ title }}' subtitle:'{{ label }}' ]} [[ kpi icon:'fa-users' value:'{{ grandTotal | Format:'N0' }}' label:'{{ label }}' subvalue:'{{ title }}' color:'#2f3042' ]][[ endkpi ]] {[ endkpis ]} </div> //- flash report grid <div class="grid"> <table class="grid-table table table-bordered table-condensed js-sticky-headers"> <thead> <tr> <th></th> <th scope="col">Configuration</th> <th scope="col" class="text-right">Category</th> <th scope="col" class="text-right">Location</th> <th scope="col" class="text-right">Area</th> <th scope="col" class="text-right">Group</th> {%- assign schedCount = 0 %} {%- assign prevDay = 'x' %} {%- assign prevHour = 'x' %} {%- assign prevMin = 'x' %} {%- assign schedCount = table2.rows | Size %} //- generate the schedule column headers {%- for schedule in table2.rows %} {%- if schedule.DayName != prevDay or schedule.Hour24 != prevHour or schedule.Minute != prevMin %} <th scope="col" class="text-center schedule"> {%- if schedule.DayName != prevDay %} {{- schedule.DayName }} {%- assign prevDay = schedule.DayName %} {%- endif %} {%- if schedule.Hour12 >= 0 %} <span class="text-normal">{% if schedule.Hour12 == '0' %}12{% else %}{{ schedule.Hour12 }}{% endif %}{% if schedule.Minute != '00' %}:{{ schedule.Minute }}{% endif %}{% if schedule.Hour24 < 12 %}a{% else %}p{% endif %}</span> {%- endif %} {%- assign prevHour = schedule.Hour24 %} {%- assign prevMin = schedule.Minute %} </th> {%- endif %} {%- endfor %} <th scope="col" class="text-center">Total</th> </tr> </thead> <tbody> {%- assign prevConfigID = 0 %} {%- assign prevCategoryID = 0 %} {%- assign prevLocationID = 0 %} {%- assign prevAreaID = 0 %} {%- assign prevGroupID = 0 %} {%- assign loopCount = 0 %} {%- assign rowTotal = 0 %} {%- for groupRow in table1.rows %} //- only start a new row if any of the groupings has changed {%- if prevConfigID != groupRow.ConfigID or prevCategoryID != groupRow.CategoryID or prevLocationID != groupRow.LocationID or prevAreaID != groupRow.AreaID or prevGroupID != groupRow.GroupID %} //- reset previous groupings as necessary {%- if prevConfigID != groupRow.ConfigID %}{% assign prevCategoryID = 0 %}{% endif %} {%- if prevCategoryID != groupRow.CategoryID %}{% assign prevLocationID = 0 %}{% endif %} {%- if prevLocationID != groupRow.LocationID %}{% assign prevAreaID = 0 %}{% endif %} {%- if prevAreaID != groupRow.AreaID %}{% assign prevGroupID = 0 %}{% endif %} //- finish the previous row if necessary {%- if rowStarted %} {%- if prevGroupID != groupRow.GroupID and loopCount < schedCount %} //- fill in empty cells for the remaining schedule columns {%- assign loopCount = loopCount | Plus:1 %} {%- for i in (loopCount..schedCount) %} <td class="schedule-total text-center text-muted">–</td> {%- endfor %} {%- endif %} //- add the row total as the last cell and close the row element <td class="row-total text-center">{{ rowTotal | Format:'N0' }}</td> </tr> //- reset counters {%- assign loopCount = 0 %} {%- assign rowTotal = 0 %} {%- assign rowStarted = false %} {%- endif %} //- start a new row <tr> {%- if prevConfigID != groupRow.ConfigID %} //- start a new configuration cell {%- assign rowSpan = groupRow.CatCount %} {%- if groupRow.IconCSSClass != '' %} <td class="icon" scope="row" rowspan="{{ rowSpan }}"><i class="{{ groupRow.IconCSSClass }} fa-fw"></i></td> {%- else %} <td class="no-icon" scope="row" rowspan="{{ rowSpan }}"></td> {%- endif %} <td class="config" scope="row" rowspan="{{ rowSpan }}">{{ groupRow.Config }}</td> {%- assign prevConfigID = groupRow.ConfigID %} {%- endif %} {%- if prevCategoryID != groupRow.CategoryID %} //- start a new category cell {%- assign rowSpan = groupRow.LocCount %} <td class="category text-right" scope="row" rowspan="{{ rowSpan }}">{{ groupRow.Category }}</td> {%- assign prevCategoryID = groupRow.CategoryID %} {%- endif %} {%- if prevLocationID != groupRow.LocationID %} //- start a new location cell {%- assign rowSpan = groupRow.AreaCount %} <td class="location text-right" scope="row" rowspan="{{ rowSpan }}">{{ groupRow.Location }}</td> {%- assign prevLocationID = groupRow.LocationID %} {%- endif %} {%- if prevAreaID != groupRow.AreaID %} //- start a new area cell {%- assign rowSpan = groupRow.GroupCount %} <td class="area text-right" scope="row" rowspan="{{ rowSpan }}">{{ groupRow.Area }}</td> {%- assign prevAreaID = groupRow.AreaID %} {%- endif %} {%- if prevGroupID != groupRow.GroupID %} //- start a new group cell <td class="group text-right" scope="row">{{ groupRow.Group }}</td> {%- assign prevGroupID = groupRow.GroupID %} {%- endif %} //- remember that a row has been started {%- assign rowStarted = true %} {%- endif %} //- add the schedule value cells {%- for schedule in table2.rows offset:loopCount %} {%- assign loopCount = loopCount | Plus:1 %} //- look for schedule values {%- if schedule.RowNum == groupRow.ColNum %} //- schedule match found, add a value cell <td class="schedule-total text-center">{{ groupRow.Total | Format:'N0' }}</td> {%- break %} {%- else %} //- no schedule match, add an empty cell <td class="schedule-total text-center text-muted">–</td> {%- endif %} {%- endfor %} //- add to the row's running total {%- assign rowTotal = rowTotal | Plus:groupRow.Total %} {%- endfor %} //- finish up the very last row in the table {%- if loopCount < schedCount %} //- fill in any remaining empty schedule cells {%- assign loopCount = loopCount | Plus:1 %} {%- for i in (loopCount..schedCount) %} <td class="schedule-total text-center text-muted">–</td> {%- endfor %} {%- endif %} //- add the row total cell <td class="row-total text-center">{{ rowTotal | Format:'N0' }}</td> </tr> </tbody> <tfoot> <tr> <td colspan="5"></td> <th scope="row" class="text-right">Totals: </th> //- add the schedule column total cells {%- for schedule in table2.rows %} <td class="text-center text-bold"> {{ schedule.Total | Format:'N0' }} </td> {%- endfor %} <td class="text-center text-bold">{{ grandTotal | Format:'N0' }}</td> </tr> </tfoot> </table> </div> </div> <style> .flash-report #zone-main { margin: -35px; margin-bottom: 0; } .flash-report .grid-table>thead>tr>th, .flash-report .grid-table>tbody>tr>td { padding: 16px 12px } .flash-report thead th.schedule { vertical-align: bottom } .flash-report .table .text-muted { color: #b2b2b2 !important } .kpi-title, .kpi-subtitle { display: none; } </style> All done! On initial page load, it should show check-in totals for all check-in configs and locations for the last 7 days. Use the filters at the top to narrow the report down to exactly what you want to see. The KPI value will show the total number of check-ins for the currently selected filter values. Follow Up Please don't hesitate to leave a comment below or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe. If you come up with better or more efficient ways of doing anything in this recipe, please let me know. Thanks! Change Log 2024-10-18 - Initial Version