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.

https://i.tpsdb.com/2016-10-14-100830..png

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.

© 2020 TouchPoint Software, LLC.  All rights reserved.