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.

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.

© 2020 TouchPoint Software, LLC.  All rights reserved.