Class List 2

This SQL script is very similar to the one in this help article. Class List. It is a SQL Custom Report, that you add to the Custom Reports in Special Content and you can run it from Organization Search. The difference between this report and the Class List is that this report includes the name of the class in the report itself and sorts by the name of the org. However, it does not have separate pages for each org. So, this one is not suitable for printing.

The report does give a list with the People ID# as an active link to the person’s people record and the org ID# as an active link to the organization.

It also contains the following information:

  • Organization ID # (this is a link to the org)

  • Organization Name

  • People ID# (this is a link to the person’s record)

  • Last Name

  • Goes By Name (or first, if no Goes By name)

  • Email Address

  • Home Phone

  • Cell Phone

  • Street Address

  • City

  • State

  • Zip

Create Class List 2 SQL Report

Step 1

Create a new SQL Report named ClassList2.

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=”ClassList2” type=”OrgSearchSqlReport” />

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

https://i.tpsdb.com/./2017-11-14_15-13-47.png

Sample Class List 2

--This report does not have a page break. It is an onscreen report.
SELECT
    cm.OrganizationId
    ,cm.OrganizationName
    ,p.PeopleId
    ,p.lastname AS [Last]
    ,p.preferredname AS [GoesBy]
    ,p.emailaddress AS [Email]
    ,p.homephone AS [Home]
    ,p.cellphone AS [Cell]
    ,p.primaryaddress AS [Street]
    ,p.primarycity AS [City]
    ,p.primarystate AS [State]
    ,p.primaryzip AS [Zip]
FROM dbo.People p
JOIN dbo.CurrOrgMembers(@orgids) cm ON cm.PeopleId = p.PeopleId
ORDER BY cm.OrganizationName, p.Name2


Latest Update

11/13/2020

Modify image link with secure protocol.