Totals by Fund with Fees

This report mimics the main Totals by Fund report with the addition of columns for Base (the amount given before the fee is added) and Fee. Use this report if you simply need to see fee totals for each fund. If you need to see each individual contribution entry for which fees were covered, see the report Details with Fees.

The report is accessed from the Other Reports menu on the Totals by Fund page.

Create the Totals by Fund with Fees Report

Step 1
Create a new SQL Report named TotalsByFundWithFees.
Step 2

Copy the code below and paste it into the new SQL file you created. Save the script.

For more detailed instructions on creating SQL Report click the link below.

Run the Totals by Fund with Fees Report

Step 1
Go to Administration > Contributions > Totals by Fund Report.
Step 2
Select the Date Range and any other filters you want for the report.
Step 3
Select Totals By Fund With Fees from the Other Reports drop down menu. The report opens on-screen. If you want it in Excel just select the Download to Excel button.

Code for the Report

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--roles=Finance
--class=TotalsByFund

;with totals as (
    select
        c.FundId,
        convert(numeric(11,2),isnull(sum(c.ChargedFee), 0)) [Fee],
        sum(c.ContributionAmount) [Total],
        count(c.ContributionAmount) [Count]
    from dbo.ContributionSearch(NULL, NULL, NULL, NULL, @StartDate, @EndDate, @CampusId, NULL, @Online, NULL, @TaxNonTax, NULL, NULL, NULL,
                                @IncludeUnclosedBundles, NULL, NULL, @ActiveTagFilter, @fundset, NULL) cs
    join dbo.Contribution c on c.ContributionId = cs.ContributionId
    group by c.FundId
)
select
    f.FundName + ' (' + convert(nvarchar(12),f.FundId) + ')' +
       case isnull(f.FundIncomeAccount, '') when '' then '' else ' GL:' + f.FundIncomeAccount end [Fund Name],
    totals.Total - totals.Fee [Base],
    totals.Fee,
    totals.Total,
    totals.Count
from totals
join dbo.ContributionFund f on f.FundId = totals.FundId


Latest Update 05/14/2021

Added this article.