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
- Street Address
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
Modify image link with secure protocol.