Class List¶

This is a SQL Report that will run from the Organization Search page as a Custom Report. You will filter for a Program and Division (or other filters) and select the report from the Toolbar under the code icon. The report will present a list containing everyone enrolled in the orgs included in the search. For each org, the header will be the name of the organization. And, if you choose to print the report, there will be a page break between each org.

For each person, the report will list the following:

Last Name
Goes by Name (or first, if no preferred name)
Member Status (church status)
Home Phone
Cell Phone
Email
Street Address
City
State
Zip

https://i.tpsdb.com/./ClassListSample.png — Sample Class List¶

We have a script for a very similar report that is designed to just be an onscreen report. It does not have page breaks. That report does include the Org Name and Org ID# as part of the report. If you are interested, see Class List 2.

Create the Class List Report¶

Step 1

Create a new SQL Report named ClassList.

Step 2

Copy the code below and paste it into the new SQL file you created. Save the script. For more information on creating SQL Report click the link below.

See also

How to create a SQL Script

Step 3

Add the report to Custom Reports in Special Content > Text Content > Custom Reports. The line of code in the text file will look like this:

<Report name="ClassList" type="OrgSearchSqlReport" />

Now you can run the report from the Organization Search page after selecting a Program and Division (or other filters).

;WITH rows AS (
    SELECT
        p.lastname AS [Last]
        ,p.preferredname AS [GoesBy]
        ,ms.Description MemberStatus
        ,dbo.FmtPhone(p.HomePhone) HomePhone
        ,dbo.FmtPhone(p.CellPhone) CellPhone
        ,p.emailaddress AS [Email]
        ,p.primaryaddress AS [Addr]
        ,p.primarycity AS [City]
        ,p.primarystate AS [State]
        ,p.primaryzip AS [Zip]
        ,cm.OrganizationName
        ,cm.OrganizationId
    FROM dbo.People p
    JOIN dbo.CurrOrgMembers(@orgids) cm ON cm.PeopleId = p.PeopleId
    JOIN lookup.MemberStatus ms ON ms.Id = p.MemberStatusId
),
orgs AS (
    SELECT
        o.OrganizationId
        ,ROW_NUMBER() OVER (ORDER BY o.OrganizationName, o.OrganizationId) AS pagebreak
    FROM splitints(@orgids) v
    JOIN dbo.Organizations o ON o.OrganizationId = v.Value
)
SELECT r.Last ,
       r.GoesBy ,
       r.MemberStatus ,
       r.HomePhone ,
       r.CellPhone ,
       r.Email ,
       r.Addr ,
       r.City ,
       r.State ,
       r.Zip ,
       o.pagebreak,
       r.OrganizationName ,
       r.OrganizationId
FROM rows r
JOIN orgs o ON o.OrganizationId = r.OrganizationId
WHERE 1=1
ORDER BY r.OrganizationName, r.Last

Latest Update

11/13/2020

Modify image link with secure protocol.