Org Percent Giving¶

This script runs from the OrgSearch BlueToolbar. It shows each organization in the search, with the percentage of each organization’s members where someone in the family gives. This way, if either a husband or a wife gives (regardless of Joint giving option) That family unit will be counted as a giver. The script looks back 365 days and considers all funds.

The counts are the number of family units, not individuals.

The comment at the top of the script ensures that this report can only be run by a Finance user.

The recommended name is OrgPercentGiving

https://i.tpsdb.com/2016-04-10_19-17-17.png — Sample Report¶

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

--Roles=Finance
DECLARE @td DATETIME = GETDATE()
DECLARE @fd DATETIME = DATEADD(DAY, -365, @td)
;WITH orgfamilies AS (
    SELECT p.FamilyId, om.OrganizationId
    FROM dbo.OrganizationMembers om
    JOIN dbo.People p ON p.PeopleId = om.PeopleId
    JOIN dbo.SplitInts(@OrgIds) i ON i.Value = om.OrganizationId
    WHERE ISNULL(om.Pending, 0) = 0
    AND om.MemberTypeId NOT IN (311)
    GROUP BY p.FamilyId, om.OrganizationId
), givers AS (
    SELECT DISTINCT fo.FamilyId
          ,fo.OrganizationId
          ,CAST(g.FamGive AS INT) isgiver
    FROM orgfamilies fo
    LEFT JOIN dbo.FamilyGiver(@fd, @td, NULL) g ON g.FamilyId = fo.FamilyId
), summary AS (
    SELECT o.OrganizationId
        ,o.OrganizationName
        ,SUM(g.isgiver) Givers
        ,SUM(1-g.isgiver) NonGivers
        ,COUNT(*) Units
    FROM givers g
    JOIN dbo.Organizations o ON o.OrganizationId = g.OrganizationId
    GROUP BY o.OrganizationId, o.OrganizationName
)
SELECT s.OrganizationId
    ,s.OrganizationName
    ,s.Units
    ,CONVERT(FLOAT, s.Givers) / s.Units * 100 GivePct
FROM summary s
ORDER BY s.OrganizationName

Latest Update

11/13/2020

Modify image link with secure protocol.