Admin: Corporate Admin
Page: Reports > Custom Reports
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 + New 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 X 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.