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. .. seealso:: :doc:`CustomTransactionReport` 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. .. seealso:: :doc:`../../../Finance/OnlineProcessing` .. 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. .. figure:: https://i.tpsdb.com/./2018-01-18_15-45-42.png :target: # Sample Report - 1st half .. figure:: https://i.tpsdb.com/./2018-01-18_15-46-08.png :target: # Sample Report - 2nd half Create a Custom Transaction Report ---------------------------------- Step 1 Create a new SQL Report named **CustomTransactionReport**. Step 2 Copy the :ref:`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. .. seealso:: :doc:`../CreateSqlScript` 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. .. _CustomTransactionReportCode: .. code-block:: sql --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.