Multi-Batch Report¶
The Multi-Batch Report functions similarly to the Batch Report, except that it can report on multiple batches and is activated differently. Currently there are three “recipe” options for this report. This first option gives subtotals for funds by day, with a subtotal for the day.
See also
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
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
- Date
- Fund
- General Ledger
- Count
- Total
- Base (Total minus Fees)
- Fees
In the details, fund subtotals are given, grouped by date, subtotals for each date, and a grand total at the bottom of the 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
MultiBatchReport
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
MultiBatchReportsql
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/MultiBatchReport?p1={batchids}
- Find the Setting
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 | 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/MultiBatchReportsql?batchids={{cleanlist}} target="_blank">Export to Excel</a>
</button>
</h4>
</div>
<table class="table notwide centered">
<thead>
<tr>
<th>Date</th>
<th>Fund</th>
<th>GeneralLedger</th>
<th class="right">Count</th>
<th class="right">Total</th>
<th class="right">Base</th>
<th class="right">Fees</th>
</tr>
</thead>
<tbody>
{{#each gifts}}
<tr {{IfEqual Fund "Total"}}style="font-weight:bold"{{/IfEqual}}{{IfEqual Fund ""}}style="font-weight:bold"{{/IfEqual}}>
<td>{{FmtDate Date}}</td>
<td>{{Fund}}</td>
<td>{{GeneralLedger}}</td>
<td class="right">{{Count}}</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('MultiBatchReportsql')
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 | DECLARE @Results1 TABLE (
Date date,
FundName nvarchar(256),
GeneralLedger NVARCHAR(100),
Amount numeric(11,2),
Base numeric(11,2),
Fees numeric(11,2)
)
INSERT INTO @Results1 (
Date,
FundName,
GeneralLedger,
Amount,
Base,
Fees
)
SELECT
CONVERT(DATE, cb.ContributionDate)
,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Description, '')
ELSE cf.FundName + ' (' + CONVERT(NVARCHAR(12), cb.FundId) + ')' END
,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 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
Date
,CASE WHEN Date IS NULL THEN 'Total'
ELSE CASE WHEN FundName IS NULL THEN '' ELSE FundName END END AS Fund
,GeneralLedger
,COUNT(*) AS Count
,SUM(Amount) AS Amount
,SUM(Base) AS Base
,SUM(Fees) AS Fees
FROM @Results1
GROUP BY GROUPING SETS ( (Date, FundName, GeneralLedger), (Date), () )
|
Latest Update | 07/11/2023 |
Add expanded header information.