Batch Report with Fee Rows

Currently there are three standard options for the single batch report and three specialized options. Below is a summary of each:

Note

When configured, the report can be run by clicking the report icon for the desired batch. See image below.

Batch Report with Fee Rows

This report provides the following information for each Batch.

Header

  • Church Name

  • Batch ID #

  • Deposit Date

  • Batch Type

Details

  • Contribution Date

  • Fund

  • General Ledger

  • Count

  • Total

In the details, the contributions are grouped by Date and Fund with sub-totals and a grand total at the bottom of the report. The Total give the amount minus any fees, which have their own row.

https://i.tpsdb.com/2023-06-22_16-05-02.png

Report Icon

https://i.tpsdb.com/BatchReportF.png

Sample Report

Create the Batch Report

Step 1

Go to Administration > Setup > Special Content > Python Scripts.

Step 2

Click the green + New Python Script File, enter the file name BatchReportF and click Submit.

Step 3

Copy all the Python code below and paste it into the new file.

Click Save Script.

Step 4

Go to Administration > Setup > Special Content > SQL Scripts.

Step 5

Click the green + New SQL Script File, enter the file name BatchReportFsql and click Submit.

Step 6

Copy all the SQL code below and paste it into the new file.

Click Save Script.

Step 7

  • Find the Setting Custom Batch Report at Administration > Setup > Settings on the Finance tab and in the Batches category

  • Paste this as the value - /PyScript/BatchReportF?p1={bundleid}

Note

Each database can have only one BatchReport. So, the Setting is just named CustomBatchReport and the Value determines which is used.

Generate the Batch Report

To generate the report, click the report icon for the desired batch.

Use the print feature of your browser to print the report. Click the Export to Excel button to export.

Python 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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
model.Title = 'Batch Report'

sqlheader = '''
    SELECT
        h.BundleHeaderId,
        CONVERT(varchar(10), h.DepositDate, 101) AS DepositDate,
        Church = (SELECT Setting FROM dbo.Setting WHERE Id = 'NameOfChurch'),
        bt.Description
    FROM dbo.BundleHeader h
    RIGHT JOIN lookup.BundleHeaderTypes bt ON bt.Id = h.BundleHeaderTypeId
    WHERE BundleHeaderId = @P1
'''

template = '''
<style>
    button > a:visited,
    button > a:hover {
        text-decoration: none;
    }
    
    @media print {
        .export {display: none}
    }
    
    table.table.no-border td,
    table.table.no-border th {
        border-top: none;
    }
    
    .box p {display: none;}
</style>
<table class="table notwide centered">
    <thead>
        {{#with header}}
        <tr>
            <td colspan="7">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h4>BatchId: {{BundleHeaderId}} - DepositDate: {{DepositDate}}</h4>
                    <h4>BatchType: {{Description}}</h4>
                    <h4 class="export"><button>
                        <a href=/RunScriptExcel/BatchReportFsql/{{BundleHeaderId}} target="_blank">Export to Excel</a>
                    </button></h4>
                </div
                <br>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Fund</th>
            <th>GeneralLedger</th>
            <th class="right">Count</th>
            <th class="right">Total</th>
        </tr>
    </thead>
    <tbody>
    {{#each gifts}}
        <tr {{#unless Fund}}style="font-weight:bold"{{/unless}}>
            <td>{{Fund}}</td>
            <td>{{GeneralLedger}}</td>
            <td class="right">{{Count}}</td>
            <td class="right">{{Fmt Amount 'N2'}}</td>
        </tr>
    {{/each}}
    </tbody>
</table>
'''

Data.header = q.QuerySqlTop1(sqlheader)
sqlgifts = model.Content('BatchReportFsql')
params = { 'P1' : Data.p1 }
Data.gifts = q.QuerySql(sqlgifts, params)

print model.RenderTemplate(template)

SQL 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
35
36
37
38
39
40
41
42
43
44
45
46
DROP TABLE IF EXISTS #base
SELECT
	c.FundId [FundId],
	CASE WHEN c.ContributionTypeId = 99 THEN isnull(accnt.Description, '')
		ELSE CONCAT(f.FundName, ' (', CONVERT(VARCHAR(12), c.FundId), ')') END FundName,
	CASE WHEN c.ContributionTypeId = 99 THEN isnull(accnt.Code, '') ELSE f.FundIncomeAccount END GeneralLedger,
	c.ContributionAmount - isnull(c.ChargedFee, 0) Amount
INTO #base
FROM dbo.Contribution c
	JOIN dbo.ContributionFund f ON f.FundId = c.FundId
	JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
	LEFT JOIN lookup.AccountCode accnt ON accnt.Id = c.AccountCodeId
WHERE bd.BundleHeaderId = @P1
	AND isnull(c.PledgeFlag, 0) = 0
	AND c.ContributionStatusId = 0
	AND c.ContributionTypeId NOT IN (6,7,8)

DROP TABLE IF EXISTS #fees
SELECT
	999999 [FundId],
	'Fees' FundName,
	'' GeneralLedger,
	c.ChargedFee Amount
INTO #fees
FROM dbo.Contribution c
	JOIN dbo.ContributionFund f ON f.FundId = c.FundId
	JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
	LEFT JOIN lookup.AccountCode accnt ON accnt.Id = c.AccountCodeId
WHERE bd.BundleHeaderId = @P1
	AND isnull(c.PledgeFlag, 0) = 0
	AND c.ContributionStatusId = 0
	AND c.ContributionTypeId NOT IN (6,7,8)
	AND c.ChargedFee > 0

;WITH data AS (
	SELECT * FROM #base
	UNION ALL
	SELECT * FROM #fees
)
SELECT
	FundName Fund,
	GeneralLedger,
	count(*) Count,
	sum(Amount) Amount
FROM data
GROUP BY GROUPING SETS ( (FundId, FundName, GeneralLedger), () )


Latest Update

10/06/2023

Added this article.