Contribution Basics Report¶

This report will display as an option on the Totals by Fund report if you add this to your database. It will use the data range specified on the Totals by Fund page and will present all the contributions during that time period, sorted by the Fund. There is a sub-total for each Fund and a grand total at the bottom of the report, along with the date range and the number of rows in the report. This report lists the donor’s name as well.

Sample Report

https://i.tpsdb.com/./2018-01-25_16-17-18.png

Note

You might also be interested in the Custom Transaction Report, which is also run from the Totals by Fund page. This looks at all online contributions and registrations.

Add the Contribution Basics Report to your Database¶

Step 1: Go to Administration > Setup > Special Content and select the SQL Scripts tab.
Step 2: Select the green +New Sql Script File button, enter the name of the report ContributionBasicsReport and click Submit.
Step 3: Copy the code below and paste it into the new file and click Save SQL Script.

Run the Contribution Basics Report¶

Go to Administration > Contributions > Totals by Fund and enter a date range, select a Campus (if you want) and click Run. Then select Other Reports > Contribution Basics Report. This will always include both Online and Not Online Contributions.

This report displays on-screen and you have the option to Download as Excel.

--roles=Finance
--class=TotalsByFund
--DECLARE @StartDate DATETIME = CONVERT(DATETIME, '9/1/2017')
--DECLARE @EndDate datetime = CONVERT(DATETIME, '9/30/2017 23:59:59.999')

DECLARE @Results1 TABLE (
                ContributorName nvarchar(138),
                FundName nvarchar(256),
                ContributionDate DATETIME,
                BundleId INT,
                BundleType NVARCHAR(50),
                DepositDate DATETIME,
                GeneralLedger NVARCHAR(25),
                Amount numeric(11,2)
)

INSERT INTO @Results1 (
                ContributorName,
                FundName,
                ContributionDate,
                BundleId,
                BundleType,
                DepositDate,
                GeneralLedger,
                Amount
                )
SELECT      CASE WHEN cf.FundName IS NULL THEN CONCAT('<strong>', CONVERT(VARCHAR(50), @StartDate, 107), ' - ', CONVERT(VARCHAR(50), @EndDate, 107), '</strong>')
            WHEN p.Name IS NULL THEN '<strong>Fund Total</strong>'
            ELSE p.Name END AS ContributorName
        ,cf.FundName
        ,cb.ContributionDate
        ,d.BundleHeaderId
        ,bt.Description
        ,h.DepositDate
        ,cf.FundIncomeAccount
        ,SUM(cb.ContributionAmount) AS ContributionAmount
FROM dbo.Contribution  AS cb
JOIN dbo.People AS p ON p.PeopleId = cb.PeopleId
JOIN dbo.BundleDetail d ON d.ContributionId = cb.ContributionId
JOIN dbo.BundleHeader h ON h.BundleHeaderId = d.BundleHeaderId
RIGHT JOIN lookup.BundleHeaderTypes bt ON bt.Id = h.BundleHeaderTypeId
INNER JOIN dbo.ContributionFund AS cf ON cf.FundId = cb.FundId
WHERE (cb.ContributionDate between @StartDate AND @EndDate)
        AND ISNULL(cb.PledgeFlag, 0) = 0
        AND cb.ContributionStatusId = 0
        AND cb.ContributionTypeId NOT IN (6,7,8)
GROUP BY GROUPING SETS((cf.FundName, cf.FundIncomeAccount, h.DepositDate, cb.ContributionDate, d.BundleHeaderId, bt.Description, p.Name), (cf.FundName), ())


SELECT *
FROM @Results1

Latest Update

05/04/2021

Correct Bundle Id column in script.