Totals by Fund Payment Type

This script will run from the Totals by Fund report after you select the filters you want. Run the report and then select Totals by Fund Payment Type from the Other Reports drop down menu.

The report will list have 4 columns – Fund Name, Fund Income Account, Type (ACH or Card), and Amount – and will have a total number of rows and a total dollar amount at the bottom of the report.

Note

The Fund Income Account number comes from that field in the fund details for each fund.

See also

Fund Management

https://i.tpsdb.com/2017-09-21_08-29-20.png

Sample Report

Note

You will probably want to select Online as the Source as the Type will refer only to online giving. Cash/check bundles will not have a type displayed.

Create Script for TotalsByFundPaymentType

Step 1

Go to Administration > Setup > Special Content > Sql Scripts and select the green + New Sql Script File button.

Enter the name TotalsByFundPaymentType and press Submit.

Step 2

Select and copy the entire code below and paste it into your new file.

See also

Note

The code includes the line to restrict it to only those with Finance role.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
--roles=Finance
--class=TotalsByFund
;WITH rows AS (
    SELECT
        f.FundName,
        f.FundIncomeAccount,
        CASE WHEN t.PaymentType IS NULL THEN ''
             WHEN t.PaymentType = 'B' THEN 'ACH'
             WHEN t.PaymentType = 'C' THEN 'Card'
             ELSE ''
        END AS Type,
        Amount = SUM(c.ContributionAmount),
        ord = 0
    FROM dbo.Contribution c
    JOIN dbo.ContributionFund f ON f.FundId = c.FundId
    JOIN dbo.ContributionSearch(NULL, NULL, NULL, NULL, @StartDate, @EndDate, @CampusId, NULL, @Online, NULL, @TaxNonTax, NULL, NULL, NULL, @IncludeUnclosedBundles, NULL, NULL, @ActiveTagFilter, NULL, NULL) cs ON cs.ContributionId = c.ContributionId
    LEFT JOIN dbo.[Transaction] t ON t.Id = c.TranId
    GROUP BY c.FundId, f.FundName, f.FundIncomeAccount, t.PaymentType
),
fullset AS (
    SELECT FundName ,
           FundIncomeAccount,
           Type ,
           Amount,
           ord
    FROM rows r
    UNION ALL
    SELECT 'Total', '', '', SUM(c.ContributionAmount), ord = 1
    FROM dbo.Contribution c
    JOIN dbo.ContributionFund f ON f.FundId = c.FundId
    JOIN dbo.ContributionSearch(NULL, NULL, NULL, NULL, @StartDate, @EndDate, @CampusId, NULL, @Online, NULL, @TaxNonTax, NULL, NULL, NULL, @IncludeUnclosedBundles, NULL, NULL, @ActiveTagFilter, NULL, NULL) cs ON cs.ContributionId = c.ContributionId
)
SELECT FundName, FundIncomeAccount, Type, Amount FROM fullset
ORDER BY ord, Type, FundName


Latest Update

11/13/2020

Modify image link with secure protocol.