Totaling mutiple values from two tables with CR8.5 1

[chuchuchui]

I'm using CR8.5, IBM Universe, and ODBC. I am trying to total the premium which is located in two different tables. There are three tables in this report. The POLICIES table which links to HO_COVERAGES via POLICIES_ID and the HO_COVERAGES_ADDLCOV which is linked via HO_COVERAGES_ID. I would like the POLICIES_ID in the same row as the premium total ie:

POLICIES_ID | Total Premium
------------|--------------
45812 | 456.32
98473 | 566.21

The HO_COVERAGES tables contains one portion of the premium under the heading BASICPREMIUM which only shows up one time for each policy. In the HO_COVERAGES_ID table I need to total ADDLPREMIUM which shows up mutiple time for each HO_COVERAGES_ID and I need this to be added to BASICPREMIUM. I cannot figure out how to get these to add together in a column that puts the total in the same row as the policy id.

 

[lbass]

First group on {policies.policiesID} and then create a formula:

maximum({HO_Coverages.basicpremium},{policies.policiesID}) +
sum({HO_Coverages_Addlcov.Addlpremium},{policies.policiesID})

Place this formula in the group header next to the group name.

-LB

 

[chuchuchui]

Hey that works great lbass, just one other question as something arose I had not anticipated but what would one do to make BASICPREMIUM equal to zero if it's null so the AddlPremium still showed up?

 

[lbass]

I would handle nulls in separate formulas, as in:

//{@basicprem}:
if isnull({HO_Coverages.basicpremium}) then 0 else {HO_Coverages.basicpremium}

//{@addlprem}:
if isnull({HO_Coverages_Addlcov.Addlpremium}) then 0 else
{HO_Coverages_Addlcov.Addlpremium}

Then use these formulas instead of the corresponding fields in the formula in my first post.

Another option is to instead go to file->report options and check "convert nulls to default values."

-LB