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.
Visibility
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 CompactFamilyReport
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
Latest Update |
11/13/2020 |
Modify image link with secure protocol.