Multi-Batch Report 2

The Multi-Batch Reports function similarly to the Batch Reports, except that they can report on multiple batches and are activated differently. Currently there are three standard options for this report and three specialized options. Below is a summary of each:

Note

When configured, the report can be run by the Report option on the Caret (downward-facing arrow) drop down menu just to the left of the Batch ID column header.

See image below.

Multi-Batch Report 2

This report provides the following information for the selected batches

Header, showing the list of selected batches

  • Batch ID

  • Deposit Date

  • Batch Type

  • Estimated Amount

  • Estimated Count

  • Actual Amount

  • Actual Count

  • Bankcard Amount

  • ACH Amount

  • Cash Amount

  • Check Amount

Details of the contents of the selected batches

  • Name

  • Fund

  • Batch ID

  • Date

  • General Ledger

  • Total

  • Base (Total minus Fees)

  • Fees

In the details, the individual contributions in the batch are listed, grouped by funds, with subtotals for each fund and a grand total at the bottom of the report.

https://i.tpsdb.com/2023-07-05_15-22-33.png

Report Menu Option

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

Sample Report

Create the Multi-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 MultiBatchReport2 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 MultiBatchReport2sql 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/MultiBatchReport2?p1={batchids}

Note

Each database can have only one Multi-Batch Report. So, the Setting is just named Custom Multi Batch Report and the Value determines which is used.

Generate the Multi-Batch Report

To generate the report, select the desired batches to include in the report, then select the Report option from the Caret (downward-facing arrow) drop down menu just to the left of the Batch ID column header.

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
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
import re

model.Title = 'Batch Report'

def Disinfect(str):
    idlist = str.split(',')
    idstr = ''
    for id in idlist:
        idInt = re.sub('[^\d]', '', id)
        idstr = idstr + ',' + idInt
    return idstr[1:]

cleanlist = Disinfect(Data.p1)
Data.cleanlist = cleanlist


sqlheader = '''
    SELECT Setting 
    FROM dbo.Setting 
    WHERE Id = 'NameOfChurch'
'''

sqlbatches = '''
;WITH gifts AS (
	SELECT 
		bh.BundleHeaderId,
		c.ContributionAmount,
		c.ContributionSourceId,
		c.CheckNo
	FROM dbo.BundleHeader bh
	JOIN dbo.BundleDetail bd ON bd.BundleHeaderId = bh.BundleHeaderId
	JOIN dbo.Contribution c ON c.ContributionId = bd.ContributionId
	WHERE bh.BundleHeaderId IN (SELECT Value FROM dbo.SplitInts('{0}'))
	AND c.ContributionStatusId = 0
	AND c.ContributionTypeId NOT IN (6,7,8)
)
SELECT
	bh.BundleHeaderId,
	CONVERT(VARCHAR(10), bh.DepositDate, 101) AS DepositDate,
	bt.Description,
	ISNULL(bh.BundleTotal, 0) EstTotal,
	ISNULL(bh.BundleCount, 0) EstCount,
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId) AS Amount,
	(SELECT COUNT(*) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId) AS [Count],
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ContributionSourceId = 2) AS [CARD],
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ContributionSourceId = 1) AS ACH,
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ISNULL(ContributionSourceId, 0) NOT IN (1,2)
															AND ISNULL(CheckNo, '') = '') AS CASH,
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ISNULL(ContributionSourceId, 0) NOT IN (1,2)
															AND ISNULL(CheckNo, '') <> '') AS CHECKS
FROM dbo.BundleHeader bh
JOIN lookup.BundleHeaderTypes bt ON bt.Id = bh.BundleHeaderTypeId
WHERE bh.BundleHeaderId IN (SELECT Value FROM dbo.SplitInts('{0}'))
'''

template = '''
<style>
    button > a:visited,
    button > a:hover {
        text-decoration: none;
    }
    
    .header, .export {
    text-align: center;
    }
    
    @media print {
        .export {display: none}
    }
    
    table.table.no-border td,
    table.table.no-border th {
        border-top: none;
    }
    
    .strong {
        font-weight: bold;
    }
    
    .box p {display: none;}
</style>
<div class="header">
<h2>{{Church}}</h2>
<h3>Batch List</h3>
</div>
<table class="table notwide centered">
    <thead>
        <tr>
            <th>Batch</th>
            <th>Deposit Date</th>
            <th>Batch Type</th>
            <th>Est Amount</th>
            <th>Est Count</th>
            <th>Amount</th>
            <th>Count</th>
            <th>Card</th>
            <th>ACH</th>
            <th>Cash</th>
            <th>Checks</th>
        </tr>
    </thead>
    <tbody>
        {{#each batches}}
        <tr>
            <td>{{BundleHeaderId}}</td>
            <td>{{DepositDate}}</td>
            <td>{{Description}}</td>
            <td class="right">{{EstTotal}}</td>
            <td class="right">{{EstCount}}</td>
            <td class="right">{{Amount}}</td>
            <td class="right">{{Count}}</td>
            <td class="right">{{CARD}}</td>
            <td class="right">{{ACH}}</td>
            <td class="right">{{CASH}}</td>
            <td class="right">{{CHECKS}}</td>
        </tr>
        {{/each}}
        <tr>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td class="right strong">{{AmountTotal}}</td>
            <td class="right strong">{{CountTotal}}</td>
            <td class="right strong">{{CardTotal}}</td>
            <td class="right strong">{{AchTotal}}</td>
            <td class="right strong">{{CashTotal}}</td>
            <td class="right strong">{{ChecksTotal}}</td>
        </tr>
    </tbody>
</table>
<div class="header">
    <h3>Batch Contents</h3>
</div>
<div class="export">
    <h4>
        <button>
            <a href=/RunScriptExcel/MultiBatchReport2sql?batchids={{cleanlist}} target="_blank">Export to Excel</a>
        </button>
    </h4>
</div>
<table class="table centered">
    <thead>
        <tr>
            <th>Name</th>
            <th>Fund</th>
            <th>Batch</th>
            <th>ContributionDate</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 BundleHeaderId}}style="font-weight:bold"{{/unless}}>
            <td>{{ContributorName}}</td>
            <td>{{FundName}}</td>
            <td><a href="/Batches/Detail/{{BundleHeaderId}}" target="_blank">{{BundleHeaderId}}</a></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.Church = q.QuerySqlScalar(sqlheader)
Data.batches = q.QuerySql(sqlbatches.format(cleanlist))
Data.AmountTotal = 0
Data.CountTotal = 0
Data.CardTotal = 0
Data.AchTotal = 0
Data.CashTotal = 0
Data.ChecksTotal = 0
for b in Data.batches:
    Data.AmountTotal += b.Amount
    Data.CountTotal += b.Count
    Data.CardTotal += b.CARD
    Data.AchTotal += b.ACH
    Data.CashTotal += b.CASH
    Data.ChecksTotal += b.CHECKS
sqlgifts = model.Content('MultiBatchReport2sql')
params = { 'batchids' : cleanlist }
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
57
58
DECLARE @Results1 TABLE (
                ContributorName nvarchar(138),
                FundName nvarchar(256),
                ContributionDate varchar(10),
                BundleHeaderId INT,
                BundleDetailId INT,
                GeneralLedger NVARCHAR(100),
                Amount numeric(11,2),
                Base numeric(11,2),
                Fees numeric(11,2)
)
INSERT INTO @Results1 (
                ContributorName,
                FundName,
                ContributionDate,
                BundleHeaderId,
                BundleDetailId,
                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.BundleHeaderId
        ,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
    INNER JOIN dbo.ContributionFund AS cf ON cf.FundId = cb.FundId
WHERE (d.BundleHeaderId in (SELECT Value FROM dbo.SplitInts(@batchids)))
    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 BundleDetailId IS NULL THEN 'Fund Total' END
		 ELSE ContributorName END AS ContributorName
	,FundName
	,ContributionDate
    ,BundleHeaderId
	,BundleDetailId
	,GeneralLedger
	,SUM(Amount) AS Amount
	,SUM(Base) AS Base
	,SUM(Fees) AS Fees
FROM @Results1
GROUP BY GROUPING SETS((FundName, GeneralLedger, BundleHeaderId, BundleDetailId, ContributionDate, ContributorName), (FundName, GeneralLedger), ())


Latest Update

10/06/2023

Update links to related articles and report image.