Custom Transaction Report¶
This Custom Transaction Report 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.
TouchPoint has other reports that are used for posting to the General Ledger and for balancing your bank account. 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.
Contribution/Transaction Dates vs Batch Dates
This report uses Contribution/Transaction Dates. However, we have another report that uses Batch Dates instead. If you are interested in that report we have a recipe for it, too.
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#
Processing Fee ((Rate+Fee) - Total) - only available to churches using TouchPoint Giving
Deposit (Total - Processing Fee) - only available to churches using TouchPoint Giving
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.
Sample Report - 1st half¶
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.
See also
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, NULL)
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 |
**2/7/2023 ** |
Added Processing Fee and Deposit.