I work as an MIS Analyst for an insurance company and have a query regarding a Crystal Report I need to run.
I have been provided with some MI requests from a supplier who we provide products for on a cost per acquisition basis. They require a monthly report containing the total number of sales that have gone on cover originating from this supplier, and the individual client details for each case. This part of the report has been relatively easy to complete, using the select expert to limit the customer source to the specific supplier etc. However, I need to run a second report that shows cases that originate from the specific supplier but have cancelled within 45 days of inception. The total number of cases from this report is then taken away from the total in the sales report. This is done because the supplier will not charge us for these cases.
Example:
For September I’ll run the sales report and count the number of cases. Then I’ll run the cancellation report for policies that were incepted in August, but cancelled within 45 days (we run August’s as obviously there will be some that have incepted in September that have not yet exceeded the 45 day criteria). I’ll count the total from this report and subtract it from the total of the sales report. This is then sent to the supplier.
When a client obtains a quote they are allocated a reference number. Therefore, any products they purchase will appear under this reference number. Such products are allocated a policy id number or "PID". If a customer purchases a car policy this is PID 1; any further purchases will become PID 2, 3 etc.
Currently in the sales report we’re limiting the PID to 1 so we don’t return any cases that have come through this supplier but have purchased additional policies (PID 2, 3 etc.) for which we do not get charged but still contain the relevant supplier code. This report will return cases that have gone on cover regardless of their current status (e.g. NEW, CANCELLED). The problem we’re having is that from time to time we need to change clients from their original policy (PID 1) to a new policy (PID 2+) at inception as they may have been put with the wrong insurer etc. The original policy’s status will now change to CANCELLLED, meaning it will be counted as a sale in the first report but will also appear in the following month’s cancellation report (when obviously it is still live on another PID).
Essentially, I need to filter out these cases which is difficult as I’m using a <Cancellation Date> meaning any files that haven’t been cancelled won’t appear!
Do you have any suggestions as to how I could get around this problem? Any advice you have regarding this would be much appreciated!
I have been provided with some MI requests from a supplier who we provide products for on a cost per acquisition basis. They require a monthly report containing the total number of sales that have gone on cover originating from this supplier, and the individual client details for each case. This part of the report has been relatively easy to complete, using the select expert to limit the customer source to the specific supplier etc. However, I need to run a second report that shows cases that originate from the specific supplier but have cancelled within 45 days of inception. The total number of cases from this report is then taken away from the total in the sales report. This is done because the supplier will not charge us for these cases.
Example:
For September I’ll run the sales report and count the number of cases. Then I’ll run the cancellation report for policies that were incepted in August, but cancelled within 45 days (we run August’s as obviously there will be some that have incepted in September that have not yet exceeded the 45 day criteria). I’ll count the total from this report and subtract it from the total of the sales report. This is then sent to the supplier.
When a client obtains a quote they are allocated a reference number. Therefore, any products they purchase will appear under this reference number. Such products are allocated a policy id number or "PID". If a customer purchases a car policy this is PID 1; any further purchases will become PID 2, 3 etc.
Currently in the sales report we’re limiting the PID to 1 so we don’t return any cases that have come through this supplier but have purchased additional policies (PID 2, 3 etc.) for which we do not get charged but still contain the relevant supplier code. This report will return cases that have gone on cover regardless of their current status (e.g. NEW, CANCELLED). The problem we’re having is that from time to time we need to change clients from their original policy (PID 1) to a new policy (PID 2+) at inception as they may have been put with the wrong insurer etc. The original policy’s status will now change to CANCELLLED, meaning it will be counted as a sale in the first report but will also appear in the following month’s cancellation report (when obviously it is still live on another PID).
Essentially, I need to filter out these cases which is difficult as I’m using a <Cancellation Date> meaning any files that haven’t been cancelled won’t appear!
Do you have any suggestions as to how I could get around this problem? Any advice you have regarding this would be much appreciated!
![[yinyang]](/data/assets/smilies/yinyang.gif "[yinyang] [yinyang]")
Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10