Custom Transaction Report Using Batch Dates

This report, unlike our original Custom Transaction Report, uses the Batch Dates instead of the Contribution/Transaction Dates. This allows you to compare the Reconcile Report with this report.

You decide which report you prefer.

See also

Custom Transaction Report for the original report

This Custom Transaction Report using Batch Dates is accessed from the Totals by Fund report, and will include all monetary online transactions for the date range selected. This includes all online giving and online registrations. The column named People will list the person or persons included in the registration. The Full Name column will be the name of the person making the payment or donation.

See article below for more information about managing online payments.

Note

The Date Range is the only filter on the Totals by Fund page that is used for this report.

The headers for the first columns include the following in formation about the transaction:

  • ID#

  • Transaction ID#

  • Transaction Date

  • Batch Date

  • Batch Reference #

  • People

  • Total Amount of Transaction

  • Description

  • Message

  • Full Name

  • People ID#

The next column headers will be the names of the online Funds with the amount for each in the appropriate column. The last column is the Registration column and displays the total amount of the online registration (i.e. anything that is not giving). The Description column is the name of the registration. For giving, that column will display either Online Giving or Recurring Giving.

Due to the wide width of the report, the sample is in two parts. The first displays the first half of the report from the left side, and the next displays the ride side of the same report.

https://i.tpsdb.com/./2018-01-18_15-45-42.png

Sample Report - 1st half

https://i.tpsdb.com/./2018-01-18_15-46-08.png

Sample Report - 2nd half

Create a Custom Transaction Report

Step 1

Create a new SQL Report named CustomTransactionReport.

Step 2

Copy the code below and paste it into the new SQL file you created. Save the script.

For more detailed instructions on creating SQL Report click the link below.

Run the Custom Transaction Report

Step 1

Go to Administration > Contributions > Totals by Fund Report.

Step 2

Select the Date Range you want for the report and click Run.

Step 3

Select Custom Transaction Report from Other Reports. The report opens on-screen. If you want it in Excel just select the Download to Excel button.

--class=TotalsByFund

SET NOCOUNT ON

DROP TABLE IF EXISTS #t
SELECT Id INTO #t
FROM dbo.TransactionSearch( NULL, NULL, NULL, @StartDate, @EndDate, NULL, NULL, NULL, NULL, 1, 1)
ORDER BY Id

DROP TABLE IF EXISTS #cols
SELECT DISTINCT c.FundId, f.FundName
INTO #cols
FROM dbo.Contribution c
JOIN dbo.ContributionFund f ON f.FundId = c.FundId
JOIN #t t ON t.Id = c.TranId

DROP TABLE IF EXISTS #tc
SELECT DISTINCT TranId, PeopleId INTO #tc
FROM dbo.Contribution
WHERE TranId IN (SELECT Id FROM #t)

DROP TABLE IF EXISTS #tp
SELECT DISTINCT tp.Id, PeopleId INTO #tp
FROM dbo.TransactionPeople tp
JOIN #t ON #t.Id = tp.Id

DROP TABLE IF EXISTS #rpt
;WITH trans AS (
    SELECT
        t.Id ,
        t.TransactionId ,
        t.Approved ,
        TranDate = t.TransactionDate ,
        BatchDate = t.batch,
        BatchRef = t.batchref,
        t.People,
        TotalAmt = t.amt ,
        t.Description ,
        t.Message,
        FullName = COALESCE((SELECT TOP 1 p.Name
                        FROM #tc c
                        JOIN dbo.People p ON p.PeopleId = c.PeopleId
                        WHERE c.TranId = ts.Id),
                        (SELECT TOP 1 p.Name
                        FROM #tp tp
                        JOIN dbo.People p ON p.PeopleId = tp.PeopleId
                        WHERE tp.Id = ts.Id),
                        t.Name),
        PeopleId = COALESCE((SELECT TOP 1 c.PeopleId
                        FROM #tc c
                        WHERE c.TranId = ts.Id),
                        (SELECT TOP 1 tp.PeopleId
                        FROM #tp tp
                        WHERE tp.Id = ts.Id),
                        t.LoginPeopleId),
        IsGiving = IIF(EXISTS(SELECT NULL FROM #tc tc WHERE tc.TranId = t.Id), 1, 0)
    FROM #t ts
    JOIN dbo.TransactionList t ON t.Id = ts.Id
    WHERE t.amt > 0 AND t.Approved = 1
), reportdata AS (
    SELECT t.Id ,
           t.TransactionId ,
           t.Approved ,
           t.TranDate ,
           t.BatchDate ,
           t.BatchRef ,
           t.People ,
           t.TotalAmt ,
           t.[Description] ,
           t.[Message] ,
           t.FullName ,
           t.PeopleId,
           t.IsGiving
    FROM trans t
)
SELECT * INTO #rpt
FROM reportdata
WHERE reportdata.BatchRef IS NOT NULL
ORDER BY reportdata.BatchRef, reportdata.Id

DROP TABLE IF EXISTS #colamts
SELECT t.Id, c.FundId, Amt = SUM(c.ContributionAmount)
INTO #colamts
FROM dbo.Contribution c
JOIN #t t ON t.Id = c.TranId
GROUP BY t.Id, c.FundId

DROP TABLE #t
DROP TABLE #tc
DROP TABLE #tp

DECLARE @sql NVARCHAR(MAX) = 'SELECT r.Id
,r.TransactionId
,r.TranDate
,r.BatchDate
,r.BatchRef
,r.People
,r.TotalAmt
,r.[Description]
,r.[Message]
,r.FullName
,r.PeopleId
'
SELECT @sql = @sql + ',(SELECT Amt FROM #colamts WHERE Id = r.Id AND FundId = '
+ CONVERT(VARCHAR, FundId) + ') AS [' + FundName + ']
'
FROM #cols
DROP TABLE #cols
SELECT @sql = @sql + ',IIF(r.IsGiving = 0, r.TotalAmt, NULL) AS Registration
FROM #rpt r
ORDER BY BatchRef, Id'
EXEC (@sql)


Latest Update

11/13/2020

Modify image link with secure protocol.