Creating Statements, Reports, and Extracts

Objective

After completing this lesson, you will be able to differentiate between Statements, Reports, and Extracts.

Statements and Reports

Statements

Statements are static reports in PDF format that can be delivered to payees once the payouts are finalized. While these are optional, they are an ideal way to communicate payout details.

When statements are published, each payee receives a communication with a PDF document. The document format is fully customizable by the implementation team to keep all documents in compliance with your corporate branding.

An example of a commissions statement for a producer.

APM statements are created using Jasper iReports. If you’d like to learn more about using iReports, visit Jaspersoft.com.

Statements can be viewed in the Manager portal under the Statements menu.

Reports

Reports have a different role in APM than statements. While statements are designed to return payout data for a single payee, reports are optimal for returning historical sales, process, and other high-level data that is useful for administrators, brokers, financial analysts and other non-payee entities. Another difference is the way reports are created. While statements are created during implementation and are static by design, reports can be configured either in Jasper iReports, on an ad hoc basis by the administrator as a QBQuery report.

Process Reports

Process Reports are stock reports that are run after the payout to display an overview of the payout results.

The image below shows a Process Report called Summary for Payout that is configured to run after the payout process. Note the Payout Type is set to CLIENT Payout, which is what we selected when we ran the payout process.

Also note the amount released for payment. This is the total commission payout for both Max and Deborah combined.

An example of a payout summary report for a payee.

QB Query Reports

If the Process Reports do not display the information you need, you can create an ad-hoc report using a QB Query. QB Queries allow you to configure custom queries from the database for operations such as creating Extracts, enhancing Import Formats and Expressions, creating ad-hoc reports and many other configurable processes. A QB Query can also be used to run a report with parameters that a user can select. While SQL statements are the most efficient way to select data for the report, you can also use a drag-and-drop interface or a stored procedure.

The most straightforward way to return the results of a QB Query is to create a Report Form that uses the QB Query as the data source. The resulting report is displayed in a simple table layout as shown in the image below.

An example of a QBQuery report, showing broker information.

Create a QB Query to Use in a Report Form

Steps

From the Manager portal, select Reporting → Reporting Configuration → QB Query Search.
Select Create (+).
Enter the name and description.
Select the Record Type. The most common record type is Hard Coded SQL.
Select the Query Context. This tells the system how the QB Query will be used: as a data extract, bonus, letter, report, and so on. In the image below, we are creating a QB Query that will be used in a report form, so we have select the Report Form context.
Select Next to create the query. For example, if the record type is Hard Coded SQL, enter the SQL query.
Select Next, then Complete.

Create a Report Form using a QB Query

Steps

From the Manager portal, select Reporting→Reporting Configuration→Report Forms.
Select New (+).
Enter a name for the report form.
Under Description Message Format Id, select Add.
Enter a description for the report and select Create.
Under Tooltip Message Format Id, select Add.
Enter a tooltip in the English field and select Create.
Note
The tooltip is the help text that appears if you hover over the report name.
Set the report type to Query.
Set the QB Query ID to the name of the QB Query.
Optionally, use the following steps to add the report form to a menu:
- Under Menu Locations, select Add (+).
- Select the portal and menu under which the new report form should appear.
- Hover over the list of submenus until the blue line is located where you’d like to insert the new report form. Click to insert the form in the menu.
- For example, in the image below, the "Broker Report" will appear under the Reporting Configuration submenu, above QBQuery Search.
- Select Save, then Activate.
The report can now be opened from the Reporting Configuration menu.

AP/Payroll Extracts

The final type of extract is the AP/Payroll Extract, which is used to retrieve payment information for each payee from the vouchers.

In the previous lesson, we saw that the primary purpose of vouchers is to summarize payments for each payee, which the carrier can use to send commission payments from any Accounts Payable (AP) or payroll system.

In our example, SafeCare Insurance is the carrier, while Max and Deborah are the payees. Now that we have run the Payout process, SafeCare can retrieve the information on the voucher and send it to their AP system to process the payments. Because this process moves data to another system, we need a way to customize the format of extracted data to meet the needs of the target system. This is done by creating an Extract. An Extract is similar to an Import File, but it is used to extract data from the system rather than import data into the system.

Before we create an extract, we need to create a SQL query that selects only the payouts that are appropriate for the carrier. For example, a carrier may want to generate only vouchers for commissions that are greater than 0, while another may want to generate vouchers for any commission amount.

Queries can be created in the Integration portal under Configuration→QB Query Search. The image below shows a very simple query that retrieves the Product Code, Broker Name, and Amount of the Payout if the commission amount is greater than 0.

Create an Extract

Steps

From the Integration portal, select Configuration→Extracts Search.
Select New (+).
Enter a unique ID, such as EXT_Payout.
Select a format. Formats can be delimited or use a standard such as MS Excel/.xlsx.
Select the query.
Set the Process Type to Manual, Automatic, or Both.
Select Save.
Select Generate Extracts Fields. This will populate the Extracts Display section with the default fields.
Select Activate.

Result

These extracts should be created as part of the Payout Finalization Process to ensure accuracy of the extract. Once Payout is finalized, the payment details in BrokerHistory and Voucher are locked in.

Summary

Statements are static reports in PDF format that can be delivered to payees once the payouts are finalized.
When statements are published, each payee receives a communication with a PDF document.
Reports have a different role in APM than statements. While statements are designed to return payout data for a single payee, reports are optimal for returning historical sales, process, and other high-level data.
QB Queries allow you to configure custom queries from the database for operations such as creating Extracts, enhancing Import Formats and Expressions, and creating ad-hoc reports.
AP/Payroll Extracts are used to retrieve payment information for each payee from the vouchers.

Continue to quiz