Crystal Report Query – Customers with Multiple Products

10 pts.
Tags:
Crystal Reports
Crystal Reports 10
I work as MI 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!

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could use a different status for that policies that were not really cancelled by a client’s request, but reassigned to another PID for any reason. Something like ANNULLED, or similar.
Or you could add a new field to the table (if that is an option) to store a cancellation reason, so you can differentiate these cases from the normal cancelled policies.

Then, you would need to filter out these cases from both of your reports (making the sales report to include not the PID 1, but the first PID that is not annulled or has the specific cancellation reason).

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following