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. .. figure:: https://i.tpsdb.com/2017-03-29_09-17-06.png :target: # 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. .. seealso:: :doc:`FiveYearGiving` 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 :ref:`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 ---------------------- .. _ThreeYearGivingCode: .. code-block:: python #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