Average Monthly Attendance for an Org¶
This script will run for a single organization, the Current Org, which is the last org viewed. This is similar to the script that you can run for a group of orgs, and for which you specify the date range.
The report sorts from most current year/month and will present data from the inception of the org.
As in the sample above, you see the year in the first column, followed by the month (integer), Total Attended, the Number of Meetings that month, and then the Average Attendance.
Each year has a total number attended, the total meetings, and the yearly average.
Create the Script¶
- Step 1
- Go to Administration > Set-Up > Special Content, select SQL Scripts, and then click the green + New Sql Script File.
- Step 2
- Enter the name as
AverageMonthlyAttendanceForOrgand click Submit.
- Step 3
Copy the code below and paste it into the new SQL file you just created.
Then click the blue Save SQL Script button at the bottom of the file.
- Step 4
Go to an organization (so that there is a Current Org), then return to the script you just created.
Now click the button to Run Script. The report will run for the Current Org.
Scroll to the bottom of the report and click the link Add Report to Menu in order for the report to display under Reports in the main menu.
To run for an actual org, go to that org and then select from the Main Menu Reports > Other Reports > Average Monthly Attendance for Org.
;WITH meets AS ( SELECT Attended = ISNULL(SUM(HeadCount), SUM(NumPresent)) ,[Year] = DATEPART(YEAR, MeetingDate) ,[Month] = DATEPART(MONTH, MeetingDate) FROM Meetings WHERE OrganizationId = @CurrentOrgId GROUP BY MeetingDate HAVING ISNULL(SUM(HeadCount), SUM(NumPresent)) > 0 ) SELECT [Year] = ISNULL(CONVERT(VARCHAR, [Year]), IIF(GROUPING([Year]) = 0, '', 'Total')) ,[Month] = ISNULL(CONVERT(VARCHAR, [Month]), IIF(GROUPING([Month]) = 0, '', 'Total')) ,[Total Attended] = SUM(Attended) ,[Num Meetings] = COUNT(*) ,[Average Attendance] = AVG(Attended) FROM meets GROUP BY [Year], [Month] WITH ROLLUP ORDER BY GROUPING([Year]), [Year] DESC, CONVERT(INT, GROUPING([Month])), CONVERT(INT, [Month]) DESC
Modify image link with secure protocol.