Following feedback from the groups panel discussion during vRX-2020 I wanted to share our current report that allows our groups directors to first filter out our small groups tree to only display their groups, then display the groups attendance percentage for the whatever duration they would like.  We also had the percentage appear in bold if the group leaders entered any notes that the groups director can hover over and read at a glance as well.

First I need to give a shout out to Pillars for helping this idea come to fruition in it's complete form.  Second to Thomas Stephens at Northview Church for helping make this recipe.

First you will need to download the zip file and extract to your lava file folder to be accessed on the page:

Download Here

Create your report page and add the block and configure it to the following settings:

Next add the Dyanamic Data Block and inside of the edit criteria enter the Query:

--DECLARE @ParentGroup varchar(max) = '8d429351-10a1-45fc-810f-d79ec88223cd' 
--DECLARE @Weeks INT = 5
DECLARE @Today datetime = GETDATE()
DECLARE @LastSaturday datetime = CASE WHEN dbo.ufnUtility_GetSundayDate(@Today) = @Today 
				THEN DATEADD(DAY, -1, @Today) --Just do the day before because today is sunday 
				ELSE DATEADD(DAY, -1, DATEADD( WEEK, -1, dbo.ufnUtility_GetSundayDate( @Today ) )) 
				END
DECLARE @FirstSunday datetime = DATEADD( week, (0-@Weeks), DATEADD(DAY, 1, @LastSaturday ))
DECLARE @GroupIds TABLE ( [GroupId] int )

SET @Weeks = CASE WHEN @Weeks is null THEN 4 ELSE @Weeks END
IF @ParentGroup IS NOT NULL AND @ParentGroup <> ''
BEGIN
	;WITH CTE AS (

		SELECT [ParentGroupId], [Id] AS [GroupId] 
		FROM [Group] 
		WHERE [GuId] = @ParentGroup
	
		UNION ALL
	
		SELECT G.[ParentGroupId], G.[Id]
		FROM [Group] G
		INNER JOIN CTE ON CTE.[GroupId] = G.[ParentGroupId]
	)

	INSERT INTO @GroupIds
	SELECT [GroupId] FROM CTE
END

SELECT 
	[GroupId],
	[GroupName],
	[GroupPath],
	CASE WHEN [OccurrenceDate] = [SundayDate] THEN [OccurrenceDate] ELSE DATEADD(WEEK, -1, [SundayDate]) END AS [SundayDate],
	CASE WHEN ISNULL([ActiveMembers],0) > 0 
		THEN CASE WHEN [Notes] IS NOT NULL AND [Notes] <> ''
				THEN CONCAT('
', FORMAT(CAST([Attendance] as decimal(9,2))/CAST([ActiveMembers] as decimal(9,2)), 'P'), '
')
				ELSE CONCAT('
 ', FORMAT(CAST([Attendance] as decimal(9,2))/CAST([ActiveMembers] as decimal(9,2)), 'P'), '
') END   
		ELSE NULL
		END AS [Value]
INTO #SummaryTbl
FROM (
	SELECT 
		GT.*,
		O.[SundayDate],
		O.OccurrenceDate,
		O.[DidNotOccur],
		ISNULL(O.[Notes],'') AS [Notes],
		( 
			SELECT COUNT(*) 
			FROM [Attendance] A
			WHERE A.[OccurrenceId] = O.[Id]
			AND A.[DidAttend] = 1
		) AS [Attendance]
	FROM (
		SELECT 
			G.[Id] AS [GroupId],
			G.[Name] AS [GroupName],
			dbo._rocks_pillars_ufnGroup_GetGroupPath( G.[Id] ) AS [GroupPath],
			COUNT(M.Id) AS [ActiveMembers]
		FROM [Group] G
			INNER JOIN GroupType T ON T.[Id] = G.[GroupTypeId]
			INNER JOIN GroupMember M ON M.[GroupId] = G.[Id] AND M.[GroupMemberStatus] = 1 AND M.[IsArchived] = 0 
			INNER JOIN GroupTypeRole R ON R.[Id] = M.[GroupRoleId]
		WHERE ( @ParentGroup IS NULL OR @ParentGroup = '' OR G.[ID] IN ( SELECT [GroupId] FROM @GroupIds ) )
			AND G.[IsActive] = 1
			AND G.[IsArchived] = 0
			AND T.TakesAttendance = 1 AND T.SendAttendanceReminder = 1 AND T.AllowedScheduleTypes & 4 <> 0
		GROUP BY G.[Id], G.[Name]
		HAVING COUNT(R.[IsLeader]) > 0
	) GT
	LEFT JOIN [AttendanceOccurrence] O ON O.[GroupId] = GT.[GroupId] AND O.OccurrenceDate >= @FirstSunday AND O.OccurrenceDate <= @LastSaturday
) AT

DECLARE @Columns nvarchar(max) = (
	STUFF((
		SELECT ',' + QUOTENAME([SundayDate])
		FROM #SummaryTbl
		GROUP BY [SundayDate]
		ORDER BY [SundayDate]
	FOR XML PATH(''), TYPE ).value('.','nvarchar(max)')
	,1,1,''))

DECLARE @ColumnNames nvarchar(max) = (
	STUFF((
		SELECT ',' + CONCAT( 'ISNULL(', QUOTENAME([SundayDate]), ',''
'') AS [', CONVERT(varchar(10), [SundayDate], 1), ']')
		FROM #SummaryTbl
		WHERE [SundayDate] IS NOT NULL
		GROUP BY [SundayDate]
		ORDER BY [SundayDate]
	FOR XML PATH(''), TYPE ).value('.','nvarchar(max)')
	,1,1,''))

DECLARE @Qry nvarchar(max) = '
	SELECT 
		[GroupId],
		[GroupName],
		[GroupPath],' + 
		@ColumnNames + '
	FROM #SummaryTbl
	PIVOT (
		MAX([Value])
		FOR [SundayDate] IN (' + @Columns + ')
	) P 
	ORDER BY [GroupPath]'

EXECUTE(@Qry)

DROP TABLE #SummaryTbl

Under "Hide Columns"

    GroupId;GroupName

Under "Selection URL"

    /page/113?GroupId={GroupId}

Under "Parameters"

    ParentGroup=;Weeks=

Show Grid Filter

    Check "Yes"

Timeout 

    30 seconds

Under Block Properties

Set the following: