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



.. figure:: https://i.tpsdb.com/./ClassListSample.png
    :target: #

    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 :doc:`ClassList2`.

Create the Class List Report
----------------------------
Step 1
    Create a new SQL Report named ClassList.

Step 2
    Copy the :ref:`code below <ClassListCode>` and paste it into the new SQL file you created.
    Save the script.
    For more information on creating SQL Report click the link below.

    .. seealso::
        :doc:`../CreateSqlScript`

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).

.. _ClassListCode:

.. code-block:: sql

    ;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.