5 Connection Request Workload Shared by Jeff Richmond, The Well Community Church 3 months ago 15.0 Connection, Operations Beginner Description This recipe provides a way to see your staff's current connection request workload at a glance. All staff members are displayed with counts for each of the various connection request states and statuses. Some adjustment may be needed for how your organization uses connection requests, but out of the box these are the request counts that are displayed: Active: Currently active requests Future: Requests with a future follow up date that hasn't arrived yet In Progress: Active requests that have had at least one attempt at making contact No Contact: Active requests with no attempted contact yet Idle: Active requests with no activity of any kind in 3 days Critical: Active requests that are idle and have no contact attempts Connected: Requests that have been connected There is also a column that indicates if the person is still a staff member, and a "Severity" column that gives an overall indication of how concerning each staff member's counts are. Prerequisites This recipe uses some of our own specific Rock customizations that may or may not match your situation. You may have to either make some extra changes to your Rock instance or modify the code in this recipe to work with your situation. Staff Person Tag We identify staff with an person tag: Name: Staff Scope: Organizational Entity Type: Person Connection Type Statuses We generally use two main statuses for our connection requests, No Contact and In Progress. If you use any statuses other than those two, this recipe will still work, but two of the columns in the report will always display zeros unless you modify it to work with your connection statuses. How-To Create new page under Rock RMS > People > Engagement > Connection Requests: Page Title: Connection Request Workload Site: Rock RMS Layout: Full Width Icon CSS Class: fa fa-link Page Routes: people/connections/workload Add a Page Parameter Filter block to the Main zone Block Settings: Show Block Title: No Show Filter Button: No Show Reset Filters Button: No Selection Action: Update Page Filters: Connection Type Name: Connection Type Key: ConnectionType Field Type: Single-Select Values: SELECT 0 AS Value, '[All Types]' AS Text, 0 AS [Order] UNION ALL SELECT ID AS Value, Name AS Text, [Order] FROM ConnectionType WHERE IsActive = 1 ORDER BY [Order] Add a Dynamic Data block to the Main zone Block Properties: Name: Connection Request Workload Report Update Page: No Edit Criteria: Parameters: ConnectionType= Hide Columns: ID,SeveritySort Selection URL: ~/person/{ID} Person Report: Yes Show Grid Actions: Communicate (all others unchecked) Show Grid Filter: No Enable Sticky Header on Grid: Yes Wrap in Panel: Yes Panel Title: Staff Connection Request Counts Panel Icon CSS Class: fas fa-inbox Customize Results with Lava: No Query: Be sure to update the variables on the first few lines of the query below: Replace the Staff Tag ID value on the first line with the ID of your staff tag, or update the query to work with whatever method your organization uses for keeping track of staff in Rock. Update the No Contact and In Progress Status ID values with comma separated lists of the corresponding statuses for each of your connection types. It may just be a single number if you only have one connection type. If any of your connection types don't have statuses that correspond to "No Contact" or "In Progress", most of the report will still work perfectly. Those two columns just won't include any requests of that type. You can get a list of all possible statuses by running this SQL query: SELECT * FROM ConnectionStatus Optional: Change the Idle Days value to adjust how many days without activity are required before a request is considered idle. DECLARE @StaffTagID AS int = 1 DECLARE @NoContactStatusIDs AS varchar(50) = '1,4' DECLARE @InProgressStatusIDs AS varchar(50) = '2,5,6,9' DECLARE @IdleDays AS int = 3 ---------------------------------------- SET @ConnectionType = ISNULL(@ConnectionType, 0) DECLARE @TimeNow AS datetime2 = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' DECLARE @AlertIcon AS varchar(50) = '<i class="fa fa-sm fa-exclamation-triangle"></i> ' DROP TABLE IF EXISTS #NOCONTACTIDS DROP TABLE IF EXISTS #INPROGRESSIDS SELECT * INTO #NOCONTACTIDS FROM STRING_SPLIT(@NoContactStatusIDs, ',') SELECT * INTO #INPROGRESSIDS FROM STRING_SPLIT(@InProgressStatusIDs, ',') SELECT ID, ISNULL(NickName + ' ' + LastName, '[UNASSIGNED]') AS Connector, IsStaff, '<span data-sort="' + X.ActiveSort + '" class="badge badge-' + X.ActiveClass + '">' + X.ActiveAlert + CAST(Active AS varchar) + '</span>' AS Active, '<span data-sort="' + X.FutureSort + '" class="badge badge-' + X.FutureClass + '">' + X.FutureAlert + CAST(Future AS varchar) + '</span>' AS Future, '<span data-sort="' + X.InProgressSort + '" class="badge badge-' + X.InProgressClass + '">' + X.InProgressAlert + CAST(InProgress AS varchar) + '</span>' AS InProgress, '<span data-sort="' + X.NoContactSort + '" class="badge badge-' + X.NoContactClass + '">' + X.NoContactAlert + CAST(NoContact AS varchar) + '</span>' AS NoContact, '<span data-sort="' + X.IdleSort + '" class="badge badge-' + X.IdleClass + '">' + X.IdleAlert + CAST(Idle AS varchar) + '</span>' AS Idle, '<span data-sort="' + X.CriticalSort + '" class="badge badge-' + X.CriticalClass + '">' + X.CriticalAlert + CAST(Critical AS varchar) + '</span>' AS Critical, '<span data-sort="' + X.ConnectedSort + '" class="badge badge-' + X.ConnectedClass + '">' + CAST(Connected AS varchar) + '</span>' AS Connected, '<i data-sort="' + CAST(SeveritySort AS varchar) + '" class="fa fa-lg fa-circle text-' + CASE SeveritySort WHEN 0 THEN 'danger' WHEN 1 THEN 'danger' WHEN 2 THEN 'critical' WHEN 3 THEN 'warning' WHEN 6 THEN 'gray-300' ELSE 'info' END + '"> </span>' AS Severity FROM (SELECT P.ID, P.LastName, P.NickName, CAST(CASE WHEN T.ID IS NULL THEN 0 ELSE 1 END AS bit) AS IsStaff, SUM(IIF(R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow), 1, 0)) AS Active, SUM(IIF(R.ConnectionState = 2 AND R.FollowUpDate > @TimeNow, 1, 0)) AS Future, SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND X2.IsNoContact = 1, 1, 0)) AS NoContact, SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND X2.IsInProgress = 1, 1, 0)) AS InProgress, SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND DATEDIFF(minute, X2.RecentDate, @TimeNow) / 1440.0 > @IdleDays, 1, 0)) AS Idle, SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND (T.ID IS NULL OR (X2.IsNoContact = 1 AND DATEDIFF(minute, X2.RecentDate, @TimeNow) / 1440.0 > @IdleDays)), 1, 0)) AS Critical, SUM(IIF(R.ConnectionState = 3, 1, 0)) AS Connected FROM ConnectionRequest R INNER JOIN ConnectionOpportunity O ON O.ID = R.ConnectionOpportunityID LEFT JOIN PersonAlias PA ON PA.ID = R.ConnectorPersonAliasID LEFT JOIN Person P ON P.ID = PA.PersonID LEFT JOIN TaggedItem T ON T.EntityGUID = P.GUID AND T.TagID = @StaffTagID OUTER APPLY (SELECT MAX(CreatedDateTime) AS RecentDate, ConnectionRequestID FROM ConnectionRequestActivity WHERE ConnectionRequestID = R.ID GROUP BY ConnectionRequestID) X1 CROSS APPLY ( SELECT ISNULL(X1.RecentDate, R.CreatedDateTime) AS RecentDate, CAST(CASE WHEN R.ConnectionStatusID IN (SELECT * FROM #NOCONTACTIDS) THEN 1 ELSE 0 END AS bit) AS IsNoContact, CAST(CASE WHEN R.ConnectionStatusID IN (SELECT * FROM #INPROGRESSIDS) THEN 1 ELSE 0 END AS bit) AS IsInProgress ) X2 WHERE O.ConnectionTypeID = @ConnectionType OR @ConnectionType = 0 GROUP BY P.ID, P.NickName, P.LastName, T.ID) AS A CROSS APPLY ( SELECT RIGHT('00000' + CAST(Active AS varchar), 5) AS ActiveSort, CASE WHEN Active > 0 AND IsStaff = 0 THEN 'danger' WHEN Active > 0 THEN 'info' ELSE 'default bg-gray-300' END AS ActiveClass, IIF(Active > 0 AND IsStaff = 0, @AlertIcon, '') AS ActiveAlert, RIGHT('00000' + CAST(Future AS varchar), 5) AS FutureSort, CASE WHEN Future > 0 AND IsStaff = 0 THEN 'warning' WHEN Future > 0 THEN 'default' ELSE 'default bg-gray-300' END AS FutureClass, IIF(Future > 0 AND IsStaff = 0, @AlertIcon, '') AS FutureAlert, RIGHT('00000' + CAST(InProgress AS varchar), 5) AS InProgressSort, CASE WHEN InProgress > 0 AND IsStaff = 0 THEN 'danger' WHEN InProgress > 0 THEN 'default' ELSE 'default bg-gray-300' END AS InProgressClass, IIF(InProgress > 0 AND IsStaff = 0, @AlertIcon, '') AS InProgressAlert, RIGHT('00000' + CAST(NoContact AS varchar), 5) AS NoContactSort, CASE WHEN NoContact > 0 THEN 'danger' ELSE 'default bg-gray-300' END AS NoContactClass, IIF(NoContact > 0 AND IsStaff = 0, @AlertIcon, '') AS NoContactAlert, RIGHT('00000' + CAST(Idle AS varchar), 5) AS IdleSort, CASE WHEN Idle > 0 AND IsStaff = 0 THEN 'danger' WHEN Idle > 0 THEN 'critical' ELSE 'default bg-gray-300' END AS IdleClass, IIF(Idle > 0 AND IsStaff = 0, @AlertIcon, '') AS IdleAlert, RIGHT('00000' + CAST(Critical AS varchar), 5) AS CriticalSort, CASE WHEN Critical > 0 THEN 'danger' ELSE 'default bg-gray-300' END AS CriticalClass, IIF(Critical > 0, @AlertIcon, '') AS CriticalAlert, RIGHT('00000' + CAST(Connected AS varchar), 5) AS ConnectedSort, CASE WHEN Connected > 0 THEN 'success' ELSE 'default bg-gray-300' END AS ConnectedClass, CASE WHEN IsStaff = 0 AND (Active > 0 OR Future > 0 OR NoContact > 0 OR InProgress > 0 OR Idle > 0) THEN 0 WHEN Critical > 0 THEN 1 WHEN NoContact > 0 THEN 2 WHEN Idle > 0 THEN 3 WHEN Future > 0 OR InProgress > 0 THEN 4 WHEN Active > 0 THEN 5 ELSE 6 END AS SeveritySort ) X ORDER BY SeveritySort, IsStaff DESC, LastName, NickName DROP TABLE IF EXISTS #NOCONTACTIDS DROP TABLE IF EXISTS #INPROGRESSIDS That's it! On initial page load, it should show each staff member's counts for all connection request types. Use the filter at the top to look at just a single type, and click any of the column headers to sort by that column. 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-05-24 - Initial Version 2024-06-10 - Fixed typo in the page parameter filters 2024-06-24 - Fixed a SQL issue that excluded requests without any activities