Problems with totalling in cr8.5

[chuchuchui]

using cr8.5, IBM Universe, and ODBC. Okay I work for an insurance company and I'm trying to total premiums on out auto policies. This is sort of a continuation of this thread I wasn't sure if I should continue it or post a new thread as this is sort of a different problem. Anyway, I have two tables that I am totaling in the first table, PA_COVERAGES_VEHICLENO, lists the premium info for each vehicle ins several fields e.g. COLLPREM, COMPPREM, LIABPDPREM. The other table is PA_COVERAGES_COVCD which I just have a SUM({PA_COVERAGES_COVCD.ENDTPREM}, {Policies.Policies_ID}) which works and get the correct numbers what I having trouble with is the first one. I have it grouped on Policies.Policies_ID. There are four tables linked like so: (table) POLICIES linked to PA_COVERAGES via field Policies.Policies_ID, PA_COVERAGES is linked to PA_COVERAGES_VEHICLENO via field pa_coverages_id, PA_COVERAGES_VEHICLENO is linked to PA_COVERAGES_COVCD via pa_coverages_id. I have each premium field changing any null value to a 0 and then have it look at the maximum:

maximum({@COLLPREM},{POLICIES.POLICIES_ID}) * .01 +

The .01 is due to the fact that all the numbers for some reason are multiplied by 100 in the system. Now the maximum is bringing me only the vehicle with the highest premium but I need to add all. I have this formula in the Group Header. I tried SUM but it seems to total everything more than once. Though I'm not sure what to do at this point to get it to total all the vehicles there is a VECHIELNO field in PA_COVERAGES_VEHICLENO and there is never more than 6 cars on any policy.

 

[chuchuchui]

I'm not sure if this exsists but is there a way to do a distinct sum?

 

[lbass]

Your description is a bit confusing. I think it would help if you showed some sample data at the detail level so we can see how fields are repeating.

-LB

 

[chuchuchui]

Here is an example of one client. They have four vehicles with a total premium of 8618. Veh#1 has a prem of 1204, veh#2 has a prem of 2508, veh#3 has a prem of 3734, veh#4 has a prem of 1172

 

[lbass]

That isn't really what I meant. You need to show the fields you are using on your report and how the results look in the detail section. This way you will be able to detect what fields are repeating. Using multiple tables, you will get duplicate records that you have to account for. We need to SEE how they duplicate.

-LB

 

[chuchuchui]

Here are the fields I have selected:

Heres the design view of my report(click for a larger image):


I'm not sure if this is what you are looking for. I'm still a bit of a novice on Crystal Reports at this point.

 

[lbass]

Please show a sample after you have previewed the report, e.g., it might look something like this:

PolNo Product Clientcode ClientName Premium Insurer
1 101 3 Walmart 500 Commerce
1 102 3 Walmart 500 Commerce
1 103 3 Walmart 500 Commerce
2 101 3 CVS 200 Commerce
2 102 3 CVS 200 Commerce
2 103 3 CVS 200 Commerce

I also think you should explain what your overall goal is in terms of what you want the report to do. You might need to add additional groups.

-LB

 

[chuchuchui]

Okay so this is what I get in the Preview part of Crystal.

What I'm trying to do is what happens on the front end of our system which automatically totals the premium for all the coverages, like this. The system breaks up the coverages for auto policies like this. The top half of this page is what is located in the table PA_COVERAGES_VEHICLENO. The lower half contains additional coverages/discounts for the client which is stored in the PA_COVERAGES_COVCD.

 

[lbass]

I'm sorry, but I really can't follow this. Since the issue appears to be that you are trying to sum subsets of data, you should take a look at the running total expert, which allows you to specify what records you want evaluated. For example, you can specify that you only want to sum a value on change of a group.

You could also use variables if you need to summarize formulas that are already summaries, e.g., the sum of a sum.

-LB

 

[chuchuchui]

Well I was sort of able to do what I need it to do. Here is what I ended up doing.

I created a running total with
field to summarize:
//#vehicleno
PA_COVERAGES_VEHICLENO.VEHICLENO
Evaluate:
For each Record
Reset:
On change of group Policies.Policies_ID

Then for each field I needed to convert to zero I did the formula:
//@collprem1
IF ISNULL({PA_COVERAGES_VEHICLENO.COLLPREM}) then 0 else {PA_COVERAGES_VEHICLENO.COLLPREM} *.01


To total the records for that field I did the formula:
//@collprem2
WhilePrintingRecords;
NumberVar COL1;
NumberVar COL;
If {#vehicleno} = COL1 + 1
then COL := COL + {@collprem1};
COL1 := {#vehicleno};
COL

I then added together all the fields I had created formulas for into @Total Premium which I placed into the Details section, went into Details >> Format Field >> Common and checked Supress If Duplicated. I also did that to the other fields I had in the report.

I then realized I needed to reset the formulas so I added another formula in the Group Header section:
//@reset
WhilePrintingRecords;
NumberVar COL1 := 0;

So far the only problem I have run into is that it shows each part of the total eg. if the person has 4 vehicles I get:
$2508
$3712
$7446
$8618 <- this is the only one I need to see

Do you know of a way to display only the maximum value or only display the last process?

I have tried doing:
maximum(@total premium}, {policies.policies_id}
but it tells me "The summary/runnging field could not be create.d"

 

[Madawc]

You could probably have done it more quickly with running totals. If you use Crystal regularly, it would be worth writing a few test reports to get to know what they can do.

Madawc Williams (East Anglia, UK)