CR9 Trying to get same type of data to appear twice in details and tot

[VickieT]

I am using Crystal 9 on a SQL database pulling data from our Eden Financial software. Each month employees who are retired pay an insurance premium for health care which, in the database, is called a deduction amount {ESYEMBDD.DED_AMT}. This amount is for their medical plan which is called the Benefit Deduction Code {ESYEMBDD.BENDED_CODE}, (hmo, highhmo and ppo). They also have to pay an admin fee {ESYEMBDD.DED_AMT} which is considered the same type of deduction and the plan is {ESYEMBDD.BENDED_CODE} (cabas, cahigh, cacomp). I’m trying to do a form letter to each retiree which is considered their bill for payment of their premiums. It looks good except for the fact that it puts each type of deduction on a separate letter so it creates two letters for each retiree.

I have a Page Header which contains our letterhead and return address.

I have grouped on the {ESYEMBDD.BENDED_CODE} in specified order which is
hmo is one of hmo or cabas
high is one of high or cahigh
pos is one of pos or cacomp

My selection criteria is as follows because I only want it to report on retirees who have no {ESYEMBDD.BEN_END_DATE} or a {ESYEMBDD.BEN_END_DATE} which is greater than the current date. I only want to report on retirees with a {ESYAPPTR.COMPANY_CODE} of cobra or “cbr” and with a {ESYEMBDD.BEN_START_DATE} which is less than or equal to the current date.

SELECTION CRITERIA:
(
isnull({ESYEMBDD.BEN_END_DATE}) or
{ESYEMBDD.BEN_END_DATE} > CurrentDate
) and
{ESYAPPTR.COMPANY_CODE} = "cbr" and
{ESYEMBDD.BENDED_CODE} in ["cahigh", "cacomp", "cabas", "high", "hmo", "pos"] and
{ESYEMBDD.BEN_START_DATE}<=CurrentDate

In the details section I have formulas for their name, address and citystatezip

In the details b section I
Text obj. COBRA HEALTH INSURANCE INVOICE

Description Month Monthly Premium


ESYEMBDD.BENDED_CODE Data Date ESYEMBDD.DED_AMT


Total Premium Due: ESYEMBDD.DED_AMT

Then there is a text box at the bottom which tells where to send payment and when it is due.

It works great except for the fact that it reports each one separately for example:

Joan Smith
Address
CityStateZip


COBRA HEALTH INSURANCE INVOICE

Description Month Monthly Premium

HMO March 1, 2005 $533.32



Total Premium Due: $533.32

Then on the next page it will report the admin fee just like a complete new letter except it will read

CABAS March 1, 2005 $10.67

I tried to do a subreport for the admin fee but that wasn’t successful.


I would like the data to read as follows:

Joan Smith
Address
CityStateZip


COBRA HEALTH INSURANCE INVOICE

Description Month Monthly Premium

HMO March 1, 2005 $533.32
CABAS March 1, 2005 $ 10.67



Total Premium Due: $543.99

Any help would be greatly appreciated. If you need more info. please let me know.

Vickie

 

[mrees]

Hi
I would try adding a group for your employee above your {ESYEMBDD.BENDED_CODE} group. And have the letter header and return address in this new group also.

mrees

 

[VickieT]

Hi mrees - thanks for your post and I tried this but it prints each letter header and retiree name and address on one page then each {ESYEMBDD.BENDED_CODE} on a separate page. Thanks though.

Vickie

 

[mrees]

Vickie,

I am sure this should work, i've just tried it myself. Have you got "new page after" selected in group header 1 + 2? If so, turn that off and just have it on group footer 1.

Let me know how you get on.

Martin

 

[VickieT]

Hi Martin

Thanks again for the help. I didn't have "new page after" turned on and it still will only give me one of the plans. I guess I need to know how you would get it to give two separate codes and their specific data when the codes and data come from the same field, in this case {ESYEMBDD.BENDED_CODE} and {ESYEMBDD.DED_AMT}

I just can't figure out how to do it. Any other ideas? Thanks again.

Vickie

 

[lbass]

Try creating a formula like {@code}:

if {ESYEMBDD.BENDED_CODE} in ["hmo","cabas"] then "HMO" else
if {ESYEMBDD.BENDED_CODE} in ["high","cahigh"] then "High" else
if {ESYEMBDD.BENDED_CODE} in ["pos","cacom"] then "POS"

Then insert a group on this formula and remove the group on {ESYEMBDD.BENDED_CODE}.

-LB

 

[VickieT]

Hi LB

Thanks for the response, I'll give it a try.

Vickie