Org Giving Totals¶

This script runs from the OrgSearch BlueToolbar. It shows each organization in the search, with the total Giving for a date range.

The Givers counts will include both husband and wife if they give jointly.

The first comment at the top of the script ensures that this report can only be run by a Finance user. The second comment causes the script to ask for a start and end date. The end date is inclusive for the whole 24 hours of that date.

The recommended name is OrgGivingTotals

https://i.tpsdb.com/2016-11-29-124740.png — Sample Report¶

Use the following code to Create the SQL Script. See How to create a SQL Script.

--Roles=Finance
--class=StartEndReport
;WITH amts AS (
    SELECT
        om.OrganizationId,
        Total = SUM(c.Amount)
    FROM dbo.Contributions2(@MeetingDate1, @MeetingDate2, 0, 0, 0, 1, NULL) c
    JOIN dbo.CurrOrgMembers(@orgids) om ON om.PeopleId = c.PeopleId
    GROUP BY om.OrganizationId
),
membercounts AS (
    SELECT m.OrganizationId, Members = COUNT(*)
    FROM dbo.CurrOrgMembers(@orgids) m
    GROUP BY m.OrganizationId
),
givers AS (
    SELECT m.OrganizationId, Givers = COUNT(*)
    FROM dbo.CurrOrgMembers(@orgids) m
    JOIN dbo.FamilyGiver(@MeetingDate1, @MeetingDate2, NULL) g ON g.PeopleId = m.PeopleId
    WHERE g.FamGive = 1
    GROUP BY m.OrganizationId
)
SELECT
    o.OrganizationId,
    o.OrganizationName,
    mc.Members,
    g.Givers,
    a.Total
FROM dbo.Organizations o
JOIN membercounts mc ON mc.OrganizationId = o.OrganizationId
JOIN givers g ON g.OrganizationId = o.OrganizationId
JOIN amts a ON a.OrganizationId = o.OrganizationId

Latest Update

11/13/2020

Modify image link with secure protocol.