Grouping Problem

[robinCQ]

Hello there,

I am attempting to create a report on some insurance transactions, which consist of a main policy transaction along with some add-ons (such as breakdown recovery, legal expenses cover, and so on)

The main issue with the data is that per customer, the add-ons are listed as separate policy types but the requirements for the report are that they be listed alongside the policy type to which they relate.

Some sample data and a mock report might help me to explain.

[tt]
Sales Staff Customer Id Policy Type Amount
John 123 Motorbike 300.00
John 123 Legal 10.00
John 456 Car 400.00
John 456 Breakdown 10.00
John 456 Legal 10.00
John 789 Motorbike 300.00
[/tt]

So in the above data, John sold a Motorbike policy to customer 123 with additional Legal Expenses cover. A different customer 456 was sold Car Insurance with extra Breakdown Recovery, as well as Legal Expenses cover. Finally, a third customer bought motorbike insurance with no extras.

This data would be required to be displayed as follows:

[tt]
Staff Policy Count Legal Breakdown Total
John Car 1 1 1 420.00
John Motorbike 2 1 0 610.00
[/tt]

Note that the add-ons (extras) and the policies themselves are counted as well as included in the total for that policy type.

My problem is that if I group by Policy Type, then I lose the association between the policy and its add-ons because they become sorted alphabetically. Sometimes the add-on is listed before its parent policy as well, which scuppered an idea I had, of grouping by a formula that used a global stringVar to remember the policy type.

If I group by the customer THEN the policy type to retain the association, then I end up with multiple instances of the policy type in 'random' order.

If anyone has any ideas how to go about this, I'd be extremely grateful!

We're on Crystal XI 11.0.0.1883 and although the data source is an old version of Informix, it isn't particularly relevant as I can reproduce the problem using data from Excel. (I'm hoping I won't have to start tampering with SQL stuff to achieve this as it needs to be easily maintainable.)

I think that's everything ...

Many thanks in advance,

Robin

 

[Madawc]

I think you'd better group by "Sales Staff", suppress the header and details, do a set of running totals that you show in the group footer.

Since the rules are complex, I'd suggest you develop it with the detail lines visible to you. Also running totals as part of the detail line. Then suppress them when the group footer totals give you the values you want.

If you're not already familiar with Crystal totals, the basics are explained at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

What happens if a customer has both a car and a motorcycle policy? How do you now which other costs are associated with each of these?

-LB

 

[robinCQ]

I asked this myself but didn't receive a satisfactory answer, besides the order in which the transactions appear -- which appears to be unreliable.

In the absence of a data-dictionary, schema guide or any form of support, I'm testing a theory that one of the many strangely-named columns represents a policy Id. i.e. Each customer has a '1' as well as 2, 3 etc. for subsequent additional policies.

Two facts which assist me greatly are:

1. The validation on the front-end system is practically non-existent, so for example dates of birth can be mistyped as, say, 01/01/9187 instead of 1987.

2. Due to item 1, sales staff are able to process policies in different ways, so sometimes an add-on will be a separate policy but other times it will be part of the main policy.

 

[Madawc]

If the data is unfamiliar and unexplained, then I'd suggest using the 'Crystal Wizard' to do a quick report showing what you've got.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[robinCQ]

I've been using Query Analyser for that, but the column names are quite peculiar.

Anyway, I've almost cracked it. After LB's question yesterday I've discovered that each policy for each customer seems to have an Id, so their first policy is 1 and so on. This may sound obvious but there are several columns which *almost* have the right pattern, so it took some trial-and-error.

Following this I'm ordering (rather than grouping) by customer, then [the field which appears to be] policy Id, then a further descending order by 'is it an add-on' pushes each parent policy type name to the top of its sub-order.

Then, I've written a formula using a global stringVar to persist the name of the parent policy type down to its add-ons, which resets when the policy Id changes. Finally, I group by this persisted policy type which ensures a policy stays with its add-ons and I can do all my summing and counting.

The data ends up something like this:
[tt]
Customer Policy Type Persisted Type
123 1 Motorbike Motorbike
123 1 Legal Motorbike
456 1 Car Car
456 1 Breakdown Car
456 1 Legal Car
etc...
[/tt]

One of the add-ons seems to be straying for some reason, but apart from that it appears to be working correctly. If anyone can see anything that would scupper this plan, please let me know!

Otherwise, cheers for your contributions everyone - hopefully I'll crack this this morning (just in time to go to the pub)

Robin