Batch Report 2

You have 3 options for Batch Reports. This second option provides a row for each entry in the batch, along with fund subtotals and a grand total.

See also

Batch Report and Batch Report 3

Note

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

Batch Report 2

This report provides the following information for each batch.

Header
  • Church Name
  • Batch ID #
  • Deposit Date
  • Batch Type
Details
  • Contributor Name
  • Fund
  • Date
  • General Ledger
  • Total
  • Base (Total minus Fees)
  • Fees

In the details, the individual contributions in the batch are listed, grouped by Fund with a sub-total per Fund and a grand total at the bottom of the report.

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

Report Icon

https://i.tpsdb.com/2023-06-23_16-02-01.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 BatchReport2 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 BatchReport2sql 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/BatchReport2?p1={bundleid}

Note

Each database can have only one Batch Report. 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
75
76
77
78
79
80
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 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/BatchReport2sql/{{BundleHeaderId}} target="_blank">Export to Excel</a>
                    </button></h4>
                </div
                <br>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Name</th>
            <th>Fund</th>
            <th>Date</th>
            <th>GeneralLedger</th>
            <th class="right">Total</th>
            <th class="right">Base</th>
            <th class="right">Fees</th>
        </tr>
    </thead>
    <tbody>
    {{#each gifts}}
        <tr {{#unless BundleId}}style="font-weight:bold"{{/unless}}>
            <td>{{ContributorName}}</td>
            <td>{{FundName}}</td>
            <td>{{ContributionDate}}</td>
            <td>{{GeneralLedger}}</td>
            <td class="right">{{Fmt Amount 'N2'}}</td>
            <td class="right">{{Fmt Base 'N2'}}</td>
            <td class="right">{{Fmt Fees 'N2'}}</td>
        </tr>
    {{/each}}
    </tbody>
</table>
'''

Data.header = q.QuerySqlTop1(sqlheader)
sqlgifts = model.Content('BatchReport2sql')
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
47
48
49
50
51
52
53
54
55
56
DECLARE @Results1 TABLE (
                ContributorName nvarchar(138),
                FundName nvarchar(256),
                ContributionDate varchar(10),
                BundleId INT,
                GeneralLedger NVARCHAR(25),
                Amount numeric(11,2),
                Base numeric(11,2),
                Fees numeric(11,2)
)
INSERT INTO @Results1 (
                ContributorName,
                FundName,
                ContributionDate,
                BundleId,
                GeneralLedger,
                Amount,
                Base,
                Fees
                )
SELECT      p.Name AS ContributorName
        ,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Description, '') ELSE cf.FundName END
        ,CONVERT(varchar(10), cb.ContributionDate, 101) AS ContributionDate
        ,d.BundleDetailId
        ,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Code, '') ELSE cf.FundIncomeAccount END
        ,cb.ContributionAmount
        ,CASE WHEN cb.DonorCoveredFee = 0 THEN cb.ContributionAmount
              ELSE cb.ContributionAmount - ISNULL(cb.ChargedFee, 0) END
        ,CASE WHEN cb.DonorCoveredFee = 0 THEN 0
              ELSE ISNULL(cb.ChargedFee, 0) END
FROM dbo.Contribution  AS cb
LEFT JOIN dbo.People AS p ON p.PeopleId = cb.PeopleId
LEFT JOIN lookup.AccountCode accnt ON accnt.Id = cb.AccountCodeId
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 (d.BundleHeaderId = @P1)
        AND ISNULL(cb.PledgeFlag, 0) = 0
        AND cb.ContributionStatusId = 0
        AND cb.ContributionTypeId NOT IN (6,7,8)
--
SELECT 
	CASE WHEN FundName IS NULL THEN 'Total'
		 WHEN ContributorName IS NULL THEN
			CASE WHEN BundleId IS NULL THEN 'Fund Total' END
		 ELSE ContributorName END AS ContributorName
	,FundName
	,ContributionDate
	,BundleId
	,GeneralLedger
	,SUM(Amount) AS Amount
	,SUM(Base) AS Base
	,SUM(Fees) AS Fees
FROM @Results1
GROUP BY GROUPING SETS((FundName, GeneralLedger, ContributionDate, BundleId, ContributorName), (FundName, GeneralLedger), ())


Latest Update 06/23/2023

Added this article.