Creating a Custom Payment Recovery Performance Report

Learning Experience Team

September 15, 2021 17:52

Admin: Corporate Admin
Page: Reports > Custom
URL: [CLIENTID].corpadmin.directscale.com/Corporate/Reports/Custom
Permission: ManageCustomReports() or ViewCustomReports()

After setting up your FlexPay payment recovery integration, you can create a custom report that provides a glimpse into how FlexPay performs while filtering out the refunds that don’t count.

While initially, this report will not have much data to display, over time, as FlexPay does its magic, you’ll see the benefit. With the report, you can evaluate your payment recovery integration and determine if it provides the expected benefits.

To create this report, use the SQL Report Builder:

Click the Add Report button and select SQL Report Builder.

The SQL Report Builder page opens. This page features the same functionality as the SQL Manager.
Enter the SQL query in the Query Box.

SELECT COUNT(R1.recordnumber) AS OrdersRecovered,

SUM(P1.Amount) AS TotalAmountRecovered,

AVG(P1.Amount) AS AverageRecoveryValue

FROM CRM_PaymentRecovery_RecommendationInfo R1

JOIN ORD_Payments P1 ON P1.OrderNumber = R1.OrderNumber

AND P1.PayType = 'Charge'

AND P1.PaymentStatus = 'Paid'

WHERE NOT EXISTS

(SELECT R2.OrderNumber

FROM CRM_PaymentRecovery_RecommendationInfo R2

JOIN ORD_Payments P2 ON P2.OrderNumber = R2.OrderNumber

AND P2.PayType = 'Credit'

AND P2.PaymentStatus = 'Paid'

WHERE R2.OrderNumber = R1.OrderNumber )

The queried data will look back to the beginning of time. If you need a more narrow view, you can modify the query to show the time constraint you need (for example, only the current month).
Click Preview. The Preview Report panel allows you to test your query and ensure it pulls in the intended data. Click the to close the panel.
Click Save. The Save Report pop-up opens.
In the pop-up, enter a Report Name--for example, "Overall Numbers for Payment Recovery".
Click Save to confirm.

The report adds to the list of available reports accessible to you and other users.