Compact Family Report¶
This script works with the BlueToolbar for People and will present a report of the entire family grouped together for anybody in the query.
If any member of a family is included in the query, the entire family will be in the report. This way, you could select children in a class, and print this report to show each child’s entire family.
The SQL recipe below contains a restriction that will limit visibility to those with Edit role. Because the report will include all family members, you may want to restrict it with that role or another one, so that OrgLeaderOnly users cannot run the report. If they do have access to it, they will be able to see the names, ages, email addresses, and cell phone numbers of not only the people in the class they lead but also their family members. Each church can decide how they wish to protect the information.
The recommended name is
Use the following code to Create the SQL Script. See How to create a SQL Script.
--Roles=Edit WITH family AS ( SELECT p.FamilyId ,MemberTypeId = IIF(p.PeopleId = f.HeadOfHouseholdId, 1, IIF(p.PeopleId = f.HeadOfHouseholdSpouseId, 2, 3)) ,p.PositionInFamilyId ,p.PreferredName ,p.LastName ,p.Age ,p.EmailAddress ,p.CellPhone ,FamilyName = hh.LastName FROM dbo.People p JOIN dbo.Families f ON f.FamilyId = p.FamilyId JOIN dbo.People hh ON hh.PeopleId = f.HeadOfHouseholdId ) SELECT Position = IIF(m.MemberTypeId = 1, 'Head', IIF(m.MemberTypeId = 2, '--Spouse', '----' + fp.Description)) ,m.FamilyName ,FirstName = m.PreferredName ,m.LastName ,m.Age ,CellPhone = dbo.FmtPhone(m.CellPhone) ,m.EmailAddress ,m.FamilyId FROM family m JOIN lookup.FamilyPosition fp ON fp.Id = m.PositionInFamilyId WHERE m.FamilyId IN ( SELECT DISTINCT FamilyId FROM dbo.People p JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @qtagid) ORDER BY m.FamilyName, m.FamilyId, m.MemberTypeId, m.PositionInFamilyId, ISNULL(m.Age, 200) DESC
Modify image link with secure protocol.