Three Year Giving¶

This is a report of individuals with a total of each of the past 3 years’ giving. If a couple gives jointly, the spouse will be included in each total and the spouse name will appear in a separate column. The code is specifying a calendar year date range and looks back for the most recent 3 complete years.

You can add this to the blue Toolbar so it can be run anywhere you see the Toolbar - for an individual, a Tag, an Involvement, or Search Builder results. It will display under the code icon.

https://i.tpsdb.com/2017-03-29_09-17-06.png — Sample Three Year Giving Report¶

We also have a recipe for a Five Year Giving Report that is that same as this one, but just looks at the past 5 complete years.

Create Three Year Giving Report¶

Step 1

Go to Admin > Advanced > Special Content and select the Python Scripts tab.

Step 2

Select the green + New Python Script File button and enter the name as ThreeYearGiving and press Submit.

Step 3

Select and copy all of the code below and paste it into the new file you created.

Press the blue Save Python Script button at the bottom of the file.

Step 4

Now press Run Script. It will run on the last search results your were viewing. To add this report to the Toolbar, press the Add Report to Menu button at the bottom of the report.

Note

For joint donors, it does not matter whether both spouses are in your search result list or not. It will present the HOH name as well as the spouse name and will combine the giving.

Three Year Giving Code¶

#Roles=Finance

template = """
;WITH givingunits AS (
    SELECT p.PeopleId FROM dbo.People p JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @BlueToolbarTagId
    UNION
    SELECT p.SpouseId FROM dbo.People p JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @BlueToolbarTagId
    WHERE ISNULL(p.ContributionOptionsId, IIF(p.MaritalStatusId = 2, 2, 1)) = 2
)
SELECT  CreditGiverId,
        SpouseId,
        Amount,
        DATEPART(YEAR, Date) Y,
        c.PeopleId,
        Date,
        c.FundId
        INTO #t
    FROM dbo.Contributions2('1/1/{0}','12/31/{2}',0,0,NULL,1,NULL) c
    WHERE EXISTS(SELECT NULL FROM givingunits WHERE PeopleId IN (c.CreditGiverId, c.CreditGiverId2))
    AND Amount > 0

;WITH giving AS (
    SELECT
        CreditGiverId, SpouseId
        , ISNULL((SELECT SUM(Amount)
            FROM #t
            WHERE CreditGiverId = tt.CreditGiverId
            AND Y = {0}), 0) Tot{0}
        , ISNULL((SELECT SUM(Amount)
            FROM #t
            WHERE CreditGiverId = tt.CreditGiverId
            AND Y = {1}), 0) Tot{1}
        , ISNULL((SELECT SUM(Amount)
            FROM #t
            WHERE CreditGiverId = tt.CreditGiverId
            AND Y = {2}), 0) Tot{2}
    FROM #t tt
    GROUP BY tt.CreditGiverId, tt.SpouseId
)
SELECT
    p.PeopleId,
    Head = p.Name2,
    Spouse = sp.PreferredName,
    g.Tot{0},
    g.Tot{1},
    g.Tot{2}
FROM giving g
JOIN dbo.People p ON p.PeopleId = g.CreditGiverId
LEFT JOIN dbo.People sp ON sp.PeopleId = g.SpouseId
ORDER BY p.Name2

DROP TABLE #t
"""
year = model.DateTime.Year - 3
sql = template.format(year, year + 1, year + 2)

print model.SqlGrid(sql)

Latest Update

3/5/2024

Updated menu location and code to include unclosed bundles