This script will display a list of everyone who was a Member of any of the Organizations that match the filters on the Organization Search/ Manage page and who was absent at the last scheduled meeting. When you run the report for the first time from Special Content, you can choose to Add to Menu. If you select that option, it will add the new report to the blue Toolbar on the Organization Search page. The report will display under the code icon in the Custom SQL Reports section.
The following information is contained on the report (which is also available to be downloaded to Excel):
- Name (link to the people record)
- Family Position
- Home Phone
- Cell Phone
- Email Address
- Absents (number)
- Last Attended Date and Time
- Organization (link to the org)
The script below is using the sort order of Organization, number of absences, and then name. You can revise it to suit your needs.
Notice the sample below. The names of the people and the organizations are links. On your report, to the left of the data, there will be a button to Download as Excel
Create to SQL Report¶
Follow the instructions in this help document to create the report. See How to create a SQL Script, using the code below for the script. Name the report AbsentsReport.
Be sure that run the report after saving it and then click Add to Menu if you want it available on the blue Toolbar on the Organization > Search/Manage page. The first time you run the report from Special Content, there will be no data. You run it here so that you can make it available on the menu/toolbar.
WITH LastAttend AS ( SELECT a.OrganizationId, a.PeopleId, MAX(MeetingDate) LastMeeting FROM Attend a JOIN dbo.OrganizationMembers om ON om.OrganizationId = a.OrganizationId AND om.PeopleId = a.PeopleId WHERE EXISTS(SELECT NULL FROM dbo.SplitInts(@orgids) WHERE Value = a.OrganizationId) AND AttendanceFlag = 1 AND om.MemberTypeId NOT IN (230, 311, 415, 500) GROUP BY a.OrganizationId, a.PeopleId ), Consecutive AS ( SELECT a.OrganizationId, a.PeopleId, COUNT(*) AS consecutive, la.LastMeeting lastattend FROM dbo.Attend a JOIN LastAttend la ON la.OrganizationId = a.OrganizationId AND la.PeopleId = a.PeopleId AND a.MeetingDate > la.LastMeeting GROUP BY a.OrganizationId, a.PeopleId, la.LastMeeting ) SELECT '/Person2/' + convert(varchar, p.PeopleId) LinkForNext ,p.Name ,p.Age ,fp.Description FamilyPosition ,dbo.FmtPhone(p.HomePhone) Home ,dbo.FmtPhone(p.CellPhone) Cell ,p.EmailAddress ,c.consecutive Absents ,c.lastattend LastAttendDt ,'/Org/' + convert(varchar, o.OrganizationId) LinkForNext ,o.OrganizationName Organization --,p.FamilyId FROM Consecutive c JOIN dbo.People p ON p.PeopleId = c.PeopleId JOIN dbo.Organizations o ON o.OrganizationId = c.OrganizationId JOIN lookup.FamilyPosition fp ON fp.Id = p.PositionInFamilyId ORDER BY OrganizationName, Absents, Name2
Modify image link with secure protocol.