BI Solution For Property and Casualty Insurance Domain

A BI solution will help an insurance company to get a holistic 360 degree view of customers, issues faced. Helical has got immense experience in developing BI solution in Property and casulaty insurance domain. Having built more than 50 reports, 20 dashboards, geographical dashboard, adhoc reports and OLAP cubes, these BI solution can be used across many different departments like underwriting, claims, billing, reinsurance, insurance etc.

Below present are snapshots of some of the reports dashboard developed by us for client.

Loss Stratification

Description

This report includes stratification of total incurred with claim count within each stratum. This report includes the ability to define the bands ($0-$100K, $100K-$250K, etc.) to meet your needs.

Purpose

This information is useful in reviewing severity patterns. For example, we see in this report that approximately 60-65% of claim count is generated by losses valued at $1,000 or less. Loss stratification provides information that can be used in several ways. It is an important consideration in insurance program design – retentions, limits, etc. And, this report is also useful in setting severity reduction goals and monitoring performance.

loss stratification report

loss stratification report

Loss Triangle

Description

A table of loss experience showing total losses for a certain period at various, regular valuation dates, reflecting the change in amounts as claims mature. Older periods in the table will have one more entry than the next youngest period, leading to the triangle shape of the data in the table. Loss triangles can be used to determine loss development for a given risk.

Loss Triangle Report

Loss Triangle Report

Large Loss Report

Description

This report provides detailed information on individual claims. It allows selection of claims over a certain threshold.

The amount of detail shown on this type of report can be completely customized to meet each client’s needs.

Purpose

By isolating claims over a chosen dollar amount, clients are able to focus upon claims making the greatest contribution to total incurred. Uses include claim reviews with administrators, insurance submissions, actuarial analysis and focusing operations staff on opportunities such as lost time reduction.

large loss report

large loss report

 

Open Claims

Description

These reports can provide a simple listing with relatively little detail or much more complete information for specific claims.

Purpose

It is often valuable to focus specifically on open claims. We have worked with clients to develop a number of approaches and reports to address this need. Often the focus is to mitigate lost time and close claims. Most effective claim management programs include reinforcement of this objective to both operations staff and claim administrators.

Examples provided here show all open claims, open claims with total incurred

Open Claims Report

Open Claims Report

 

Average Claims by Severity

Description

The Report displays business KPIs like Incurred Loss, Paid Amt, Claim Frequency and Avg Incurred Loss summarized by Adjusting Office.

average claims severity by adjuster

average claims severity by adjuster

 

Claims Cause

Description:This report helps identify specific causes of loss.

Claims Cause Report

Claims Cause Report

 

 

 

Claims Registered

Description: The report provides detailed information of all the claims which are registered. An end user will have the option to filter the data according to dates (month and year), company, line of business, dealer group etc. Based on those input parameters which are selected, the report gets populated up

 

Claims Registered Report

Claims Registered Report

Claims Schedule – By Company

Description: The report provides detailed information regarding the Payments, Loss Adjusting Expense, Outstanding Loss Reserves, Incurred Loss and Salvage Amount by claims.

Claims Schedule

Claims Schedule

 

Claims Recovery Summary

Description: The report displays summary of recovery amount by Country, Company, Agent, Line of Business, Claims, Coverage and Date of Loss.

claims recovery summary

claims recovery summary

 

General Premium Summary Report

Description: The report displays summary of premium amounts by Country, Company, LOB, Dealer group and Coverage.

general premium summary report

general premium summary report

 

 

Inforce & Unearned Premium Summary Report

Description: The report displays summary of new/renewal Inforce amount and new/renewal Unearned amounts premiums by Dealer Group and coverage

Premium summary report

Premium summary report

 

 

Loss Paid and Reserve by LOB

Description: The report displays summary of Loss Paid (MTD), Loss Paid (YTD), Loss Reserve, Expense Paid (MTD), Expense Paid (YTD) and Incurred Amount by LOB.

loss paid report

loss paid report

 

 

Policy Transaction Report

Description: The report gives details of policy transactions in given period.

policy transaction report

policy transaction report

 

 

Premium Bordereaux

premium bordereaux

premium bordereaux

 

 

Written & Earned Premium Comparison

Description: The report display comparison between Policy count, Written and Earned Premium for current period and previous period.

premium comparison report

premium comparison report

 

 

YTD Loss Listing

Description: The report displays YTD summary of Loss Payments, Recoveries and Incurred Loss by Company, Coverage, Dealer Group, Dealer, Claim Number and Date of Loss.

YTD loss listing

YTD loss listing

 

 

Premium production by agent

Description: The report allows user to view Written Premium, Average written Premium, and Policy Count for New and Renewal premiums by Agent

premium production by agent

premium production by agent

 

 

Profitability – Top 10 Agents

For the selected duration this report will show the amount earner from different companies.

Profitability top 10 agents

Profitability top 10 agents

 

 

Policy Register

Description: Description: The report provides detailed information of all policies registered for s

executive dashboard

executive dashboard

elected period.

policy register

policy register

 

 

Executive Dashboard

There could be various dashboards. For example the below dashboard shows information like loss ratio, incurred losses amount and change percentage, written and earned premium, money earner in new versus the renewals etc.

executive dashboard

executive dashboard

 

Underwriting Dashboard

The below dashboard, for the selected product, shows things like written versus earner premium, revenue growth over a period of time from new business / renewal / retention. It is also showing the loss ratio.

underwriting dashboard

underwriting dashboard

 

 

What – If Analysis

A what if analysis to in which there will be input parameter to select the product. Once the product is selected, end user can select different parameters like policy premium, policies per month, renewal retention, claim severity etc and accordingly he can see what how it will affect the loss ration, earner premium, written premium etc.

 

For having a demo of the same please get in touch at [email protected]

Nikhilesh Tiwari

Helical IT Solutions

D3 Bubble Chart Integration with Jaspersoft

In this blog we will be discussing about D3 Bubble Chart Integration with Jaspersoft using HTML method of integration.

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page(jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type="text/javascript" language="JavaScript"
src="${pageContext.request.contextPath}/scripts/d3.v3.min.js"></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Bubble Chart

Bubble Charts Integration with Jaspersoft

Bubble Charts Integration with Jaspersoft

    Bubble Chart:-


For this chart we need to include one more js script file in the decorator page as described in the start of the document.
The js file is sankey.js and can be downloaded from

http://dimplejs.org/dist/dimple.v1.1.5.min.js

Sample Code is shown below:

var svg = dimple.newSvg("#chartContainer", 1090, 500);
var data = [
{
"Date":"1/1/2011",
"Analyst":"Cidalina Rivera",
"Category":"Application",
"Total Minutes":2114200,
"Minutes Per Item":1100,
"% SLA":80
},
{
"Date":"1/1/2011",
"Analyst":"Kiran Parvathala",
"Category":"Application",
"Total Minutes":391800,
"Minutes Per Item":1959,
"% SLA":21
},
{
"Date":"1/1/2011",
"Analyst":"KrishnaReddy Mavuru",
"Category":"Application",
"Total Minutes":1056125,
"Minutes Per Item":1207,
"% SLA":26
},
{
"Date":"1/1/2011",
"Analyst":"Narasimha Dara",
"Category":"Application",
"Total Minutes":386740,
"Minutes Per Item":610,
"% SLA":30
},
{
"Date":"1/1/2011",
"Analyst":"AslamJavid Shaik",
"Category":"Application",
"Total Minutes":1573856,
"Minutes Per Item":1096,
"% SLA":94
},
{
"Date":"1/1/2011",
"Analyst":"Harini Vemulapalli",
"Category":"Application",
"Total Minutes":2846340,
"Minutes Per Item":1890,
"% SLA":100
},
{
"Date":"1/1/2011",
"Analyst":"William Nelson",
"Category":"Application",
"Total Minutes":1205502,
"Minutes Per Item":662,
"% SLA":40
},
{
"Date":"1/1/2011",
"Analyst":"Janaki Govindarajan",
"Category":"Batch Job",
"Total Minutes":72684,
"Minutes Per Item":673,
"% SLA":79
},
{
"Date":"1/1/2011",
"Analyst":"Chiranjeevi Krishna Karne",
"Category":"Education",
"Total Minutes":542348,
"Minutes Per Item":3307,
"% SLA":52
},
{
"Date":"1/1/2011",
"Analyst":"Masaru Hirata",
"Category":"Fileshare",
"Total Minutes":129090,
"Minutes Per Item":662,
"% SLA":12
},
{
"Date":"1/1/2011",
"Analyst":"Naveen Kodali",
"Category":"Phone",
"Total Minutes":75076,
"Minutes Per Item":548,
"% SLA":84
},
{
"Date":"1/1/2011",
"Analyst":"JoshyPeter Joseph",
"Category":"Phone",
"Total Minutes":574860,
"Minutes Per Item":2948,
"% SLA":27
},
{
"Date":"1/1/2011",
"Analyst":"Maheshwar Malkapuram",
"Category":"Phone",
"Total Minutes":19845,
"Minutes Per Item":105,
"% SLA":92
},
{
"Date":"1/1/2011",
"Analyst":"Sunil Bhalerao",
"Category":"Procurement",
"Total Minutes":353000,
"Minutes Per Item":1765,
"% SLA":20
},
{
"Date":"1/1/2011",
"Analyst":"Tina Chan-Browne",
"Category":"Report",
"Total Minutes":51121,
"Minutes Per Item":469,
"% SLA":95
},
{
"Date":"1/1/2011",
"Analyst":"Sirajuddin Mohammad",
"Category":"Report",
"Total Minutes":323363,
"Minutes Per Item":1693,
"% SLA":47
},
{
"Date":"1/1/2011",
"Analyst":"Nishanth Nadam",
"Category":"Enterprise",
"Total Minutes":620000,
"Minutes Per Item":10000,
"% SLA":22
},
{
"Date":"1/1/2011",
"Analyst":"Santoshkumar Shinde",
"Category":"Enterprise",
"Total Minutes":715000,
"Minutes Per Item":11000,
"% SLA":90
},
{
"Date":"1/1/2011",
"Analyst":"Keith Moller",
"Category":"Enterprise",
"Total Minutes":948000,
"Minutes Per Item":12000,
"% SLA":100
},
{
"Date":"1/1/2011",
"Analyst":"AshokKumar Sangeetham",
"Category":"Server",
"Total Minutes":1166550,
"Minutes Per Item":7070,
"% SLA":88
}
];
data = dimple.filterData(data, "Date", "1/1/2011");
var myChart = new dimple.chart(svg, data);
myChart.setBounds(400, 60, 500, 330);
myChart.addMeasureAxis("x", "Minutes Per Item");
myChart.addMeasureAxis("y", "Total Minutes");
myChart.addMeasureAxis("z", "% SLA");
myChart.addSeries(["Analyst","Category"], dimple.plot.bubble);
myChart.addLegend(600, 10, 360, 30, "right");
myChart.draw();

    Integration with JasperServer:


The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.
Generally for these type of charts we pass a variable which contains required data containing date, hour and a value associated with that particular date and hour. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.
Any variable/parameter can be accessed as shown below:
” var arr =”+$V{variable1}+” ”
Parameter in query:
Select * from table_name
where date between $P{parameter1} and $P{parameter2}

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).