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. .. seealso:: :doc:`../../../Finance/Fund_Index` .. figure:: https://i.tpsdb.com/2017-09-21_08-29-20.png :target: # 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 :ref:`code below ` and paste it into your new file. .. seealso:: - :doc:`../../../Finance/FinanceReports_TotalsByFund` - :doc:`../CreateSqlScript` .. note:: The code includes the line to restrict it to only those with Finance role. .. _TotalsFundPmtType: Code ---- .. code-block:: sql :linenos: --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.