Advanced Reports

Advanced Reporting is a UK Addition that can be purchased separately for use with Applied TAM. In order to use this feature, links must be set up in Utility Manager > System Administrative > Define TAM Links.

1. At Home Base, click Reports.
2. Click Advanced.
3. The SQL Reporting home page opens in your default web browser. Folders represent the Report Bundles or Bespoke Reports that are installed on your system. Click the Management Info Bundle folder to view the report options; this includes a report heading and a brief report summary of each report.
4. Click on a report heading to access a specific report.
5. Once you have selected the report you wish to run, choose your report parameters and then click the View Report button to generate your report.
6. You have the option to print your report (click the Printer/Print icon) or to save/export the report in different formats. To view/select the Export/Save options, click the Disk/Export icon.
7. Select the appropriate file type to which you wish to export: PDF, Excel, or Word. Depending on the version of SQL installed, you may also see XML, CSV, MHTML and TIFF options.
8. Some SQL reports have dynamic columns/rows/cells, which allow you to drill down to display a detailed view. When you export these reports to Excel, the drill down functions also apply. Click the plus sign (+) to display a detailed view.

Individual Reports

Claims Analysis Report

The purpose of this report is to provide a summary comparison of Claims Paid and Outstanding for current year to date versus 1 year, 3 years and 5 years previous for the Agencies, Branches, Departments, Current Year, Comparison Year - 1, 3 or 5 years and Policy Types specified in the report parameters and grouped by either Claim Coverage Code, CSR (A/H), Introducer 1, Agent, Insurer or Policy Type.

Report Requirements/Prerequisites:

• Claim Reserves must be entered in the Claim Reserve Amount field.
• Claim Payments must be entered in the Claim Paid Amount field, either by updating this field or by adding Claim Payments.
• The Claim Date Reported field must be accurate, as comparison years are based on this date.

Report Parameters

• Agency – Select Agencies you wish to run this report for.
• Branch – Select Branches you wish to run this report for.
• Department – Select Departments you wish to run this report for.
• Current Year – Enter the Year (YYYY) you wish to compare against.
• Comparison Year – Select 1, 3 or 5 years previous.
• Policy Type – Select Policy Types you wish to run this report for.
• Group By – Select how you wish to group/analyse results.

Report Output

• Previous Year
• • Paid Amount
  • Reserve Amount
  • Total Claim Amount
  • Total No. of Claims
  • Total No. of Open Claims
• Current Year
• • Paid Amount
  • Reserve Amount
  • Total Claim Amount
  • Total No. of Claims
  • Total No. of Open Claims

This report is grouped/sorted by Group By selection chosen.

Production Analysis Report

The purpose of this report is to provide a summary comparison of debited gross premiums and commissions for current year to date versus 1 year, 3 years and 5 years previous for the Agencies, Branches, Departments, Current Year, Comparison Year - 1, 3 or 5 years and Policy Types specified in the report parameters and grouped by either Transaction Type, CSR (A/H), Introducer 1, Agent, Insurer or Policy Type.

Report Requirements/Prerequisites

• Miscellaneous Transactions (TAX, MEM, AFE & Other Misc. User Defined Transactions) are excluded from this report.
• Comparison years are based on Transaction Date.
• Transactions are assumed to have been entered correctly against the correct Client, Policy Type, Insurer, etc. and for the correct dates and amounts.

Report Parameters

• Agency – Select Agencies you wish to run this report for.
• Branch – Select Branches you wish to run this report for.
• Department – Select Departments you wish to run this report for.
• Current Year – Enter Year (YYYY) you wish to compare against.
• Comparison Year – Select 1, 3 or 5 years previous.
• Policy Type – Select Policy Types you wish to run this report for.
• Group By – Select how you wish group/analyse results.

Report Output

• Previous Year
• • Premium Amount
  • Commission Amount
  • Average Premium Amount
  • Average Commission Amount
• Current Year
• • Premium Amount
  • Commission Amount
  • Average Premium Amount
  • Average Commission Amount

This report is grouped/sorted by Group By selection chosen.

Renewal Retention Report

The purpose of this report is to provide a summary comparison of policies retained at renewal for Live/Current policies to those available for renewal based on policies incepted or renewed 1 year ago for the Agencies, Branches, Departments, Date period, Policy Statuses and Policy Types specified in the report parameters and grouped by either CSR (A/H), Introducer 1, Agent, Insurer or Policy Type.

Report Requirements/Prerequisites

• Policy Effective Date and Transaction Effective Dates must be accurate as comparison is based on Transaction Effective Dates.
• You should run this report retrospectively to ensure all possible Renewals for the period have been either renewed or lapsed.
• Only Live/Current Policy Status codes should be selected in report parameters.
• Only annual Policy Types should be selected in report parameters.
• Transaction Types included in this report are NEW, REN and REW only.
• Transactions are assumed to have been entered correctly against the correct Client, Policy Type, Insurer, etc. and for the correct dates and amounts.

Report Parameters

• Agency – Select Agencies you wish to run this report for.
• Branch – Select Branches you wish to run this report for.
• Department – Select Departments you wish to run this report for.
• Date From – Select Transaction Effective Date period from.
• Date To - Select Transaction Effective Date period to.
• Policy Status – Select Live Policy Status codes you wish to run this report for.
• Policy Type – Select Policy Types you wish to run this report for.
• Group By - Select how you wish group/analyse results.

Report Output

• Last Year Policy Count
• Last Year Premium
• Last Year Commission
• This Year Policy Count
• This Year Premium
• This Year Commission
• Renewal Retention % (Based on Number of Policies)

This report is grouped/sorted by Group By selection chosen.

Renewal Review

The purpose of this report is to provide a detailed renewal review summary for each policy to aid broking staff in assessing the risk and placing the renewal appropriately including policy information, invoiced premium, commission, fees and claims reported and paid within the expiring policy period for the Agencies, Branches, Departments, Date period, Policy Types, Insurers, CSR (A/H)s, Introducers and Agents specified in the report parameters.

Report Requirements/Prerequisites

• Policy Effective and Expiry date fields must be accurate, as all information included in this report is based on comparison to these dates.
• Live/Current Policy Status codes included in this report are NEW, REN, REW and LIV only.
• The Transaction Effective Date field must be accurate as used as comparative date.
• Invoiced Premium/Commission based on Production Transactions excluding TAX and MEM.
• Invoiced Fees/Discounts based on AFE, DIS and STF Transactions only.
• Transactions are assumed to have been entered correctly against the correct Client, Policy Type, Insurer, etc. and for the correct dates and amounts.
• Claim Reserves must be entered in the Claim Reserve Amount field.
• Claim Payments must be entered in the Claim Paid Amount field, either by updating this field or by adding Claim Payments.
• The Claim Date Reported field must be accurate as used as comparative date.

Report Parameters

• Agency – Select Agencies you wish to run this report for.
• Branch – Select Branches you wish to run this report for.
• Department – Select Departments you wish to run this report for.
• Date From – Select Policy Expiry Date period from you wish to run this report for.
• Date To - Select Policy Expiry Date period to you wish to run this report for.
• Policy Type – Select Policy Types you wish to run this report for.
• Insurer – Select Insurers you wish to run this report for.
• CSR (A/H) – Select CSR (A/H)’s you wish to run this report for.
• Introducer – Select Introducers you wish to run this report for.
• Agent – Select Agents you wish to run this report for.

Report Output

• Agency
• Branch
• Department
• Client Ref
• Client Name
• Policy Number
• Policy Type
• Renewal Date - Policy Expiry Date
• Issuing Insurer
• Billing Insurer
• CSR (A/H)
• Introducer 1 Name
• Agent Name
• Bill Mode
• Pay Mode
• Premium
• Commission
• Outstanding Balance
• Invoiced Premium - for expiring policy period
• Invoiced Commission - for expiring policy period
• Invoiced Fees/Discounts - for expiring policy period
• Number of Claims - for expiring policy period
• Claim Paid - for expiring policy period
• Claim Reserve

This report is sorted by:

• Agency
• Branch
• Department
• Client Ref (A – Z)

User Productivity Revenue (KPIs)

The purpose of this report is to measure user productivity using the following Key Performance Indicators (KPI’s): Number of associated Clients, Number of associated Policies, Amount of debited Gross Written Premium and Commission, Number of Policies added, Number of Transactions added, Number of Activities added and Number of Claims added for the Agencies, Branches, Departments and Date period specified in the report parameters.

Report Requirements/Prerequisites

• Live/Current Policy Status codes included in this report are NEW, REN, REW and LIV only.
• Employee File record must exist for each User/Operator and System Code fields for Operator, CSR (A/H) and Introducer must be completed with the relevant related code. See Appendix A for details of how to set this up.

Report Parameters

• Agency – Select Agencies you wish to run this report for.
• Branch – Select Branches you wish to run this report for.
• Department – Select Departments you wish to run this report for.
• Date From – Select date period from you wish to run this report for.
• Date To - Select date period to you wish to run this report for.

Report Output

• Employee Ref
• Employee Name
• Client Count
• Policy Count
• Gross Written Premium
• Gross Written Commission
• User Productivity for Date Range
• • Policies Added
  • Transactions Added
  • Activities Added
  • Claims Added

This Report is grouped/sorted by Employee Ref (A-Z).

NOTE

An Employee can be associated to his or her related system codes by assigning the relevant values in the Employee file record as shown below.