Batch Report by Type¶
Currently there are three standard options for the single batch report and three specialized options. Below is a summary of each:
Batch Report - a legacy report that shows totals for cash and checks, so may be suitable as a deposit report
Batch Report 2 - each batch entry with fund totals
Batch Report 3 - fund totals
Batch Report by Campus - fund totals by Campus
Batch Report with Fee Rows - fund totals by day with rows for fees
Batch Report by Type - fund totals by type (Contribution or Registration)
Note
When configured, the report can be run by clicking the report icon for the desired batch. See image below.
- Batch Report by Type¶
This report provides the following information for each Batch.
- Header
Church Name
Batch ID #
Deposit Date
Batch Type
- Details
Fund
General Ledger
Total
Base (Total minus Fees)
Fees
In the details, the contributions are grouped by Fund with a sub-total per Fund and by Type (Contribution or Registration) and a grand total at the bottom of the 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
BatchReportT
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
BatchReportTsql
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 categoryPaste this as the value -
/PyScript/BatchReportT?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 75 76 77 | 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/BatchReportTsql/{{BundleHeaderId}} target="_blank">Export to Excel</a> </button></h4> </div <br> </td> </tr> {{/with}} <tr> <th>Fund</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 Fund}}style="font-weight:bold"{{/unless}}> <td>{{#if Fund}}{{Fund}} {{else}}{{#if Type}}{{Type}}{{else}}Grand{{/if}} Total{{/if}}</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('BatchReportTsql') 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 | DECLARE @Results1 TABLE ( Type nvarchar(12), FundName nvarchar(256), GeneralLedger NVARCHAR(100), Amount numeric(11,2), Base numeric(11,2), Fees numeric(11,2) ) INSERT INTO @Results1 ( Type, FundName, GeneralLedger, Amount, Base, Fees ) SELECT CASE WHEN cb.ContributionTypeId = 99 THEN 'Registration' ELSE 'Contribution' END ,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Description, '--no account code--') 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 = @P1) AND ISNULL(cb.PledgeFlag, 0) = 0 AND cb.ContributionStatusId = 0 AND cb.ContributionTypeId NOT IN (6,7,8) -- SELECT Type ,FundName Fund ,GeneralLedger ,SUM(Amount) AS Amount ,SUM(Base) AS Base ,SUM(Fees) AS Fees FROM @Results1 GROUP BY GROUPING SETS ( (Type, FundName, GeneralLedger), (Type), () ) |
Latest Update |
10/06/2023 |
Added this article.