Way to show if someone waived a medical plan

[FireGeek21]

Well, thanks to health care reform, I have a new challenge on my hands! I have been looking at this on and off for a few days now and can't seem to resolve this. Hoping someone can guide me in the right direction.

Basically, we need to list when someone has not elected a medical plan - therefore, they have in fact "waived" the plan. We have a Benefit table with Type and Code as follows:

HL - CC (medical)
HL - CO (medical)
HL - CT (medical)
HL - VS (vision)
HL - EP (eap)
DN - DN (dental)
EL - LF (basic life)
DL - DL (dependent life)
HS - HA (hsa)
HS - HC (health care flex)
HS - DC (dependent care flex)

We need to show each employee and all the plans they have elected. BUT, when it comes to the 3 medical plans, if they did not elect one, we need to show "Waived". Right now, I get "Waived" for all plans not elected (dental, life, health savings...) or I get only "Waived" and nothing else.

I have tried a variety of IF statements and Case statements with IF. The report pulls in the Benefit table and an employee table at the basic level. It also shows dependents that are covered under the various plans.

Finally, the report has grouping. The two levels of concern are the first which is on the employee, the second is on the benefit code.

It seems I need a formula that will create another "code" for waived medical" which does not exist in the table.

THOUGHTS??? THANKS!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[CoSpringsGuy]

Is the benefit table linked to an employee table?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[FireGeek21]

Yes. EMPLOYEE Left Outer Join to BENEFIT on EMPLOYEE.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[IanWaterman]

Please show a sample of raw data and then how you want it to appear.

Ian

 

[FireGeek21]

EMPLOYEE table has data such as: EMPLOYEE #, NAME

BENEFIT table has data such as: EMPLOYEE #, BEN_TYPE, BEN_CODE, START_DT, STOP_DT

Some of the TYPES - CODES are listed below:

HL - CC (medical)
HL - CO (medical)
HL - CT (medical)
HL - VS (vision)
HL - EP (eap)
DN - DN (dental)
EL - LF (basic life)
DL - DL (dependent life)
HS - HA (hsa)
HS - HC (health care flex)
HS - DC (dependent care flex)

All employees will have BEN_TYPE HL and BEN_CODE EP and all benefit eligible employees with have BEN_TYPE EL and BEN_CODE LF. There shouldn't be an issue with an EMPLOYEE not existing in the BENEFIT table. I am already taking into consideration the start/stop dates so that is not an issue. FYI, someone may never have had medical before or maybe they had medical in the past but not now. Basically looking at today and if they have an active medical plan with no stop date or a stop date in the future.

Sample data might look as follows (it will vary if someone has medical or will need to show they waived medical):

EMPLOYEE BEN_TYPE BEN_CODE

123465789 HL EP
123465789 HL Waived Medical
123465789 EL LF
123465789 HS DC

987654321 HL EP
987654321 HL CO
987654321 HL VS
987654321 DN DN
987654321 EL LF
987654321 HS DC

654321789 HL EP
654321789 HL Waived Medical
654321789 DN DN
654321789 EL LF

456123789 HL EP
456123789 HL CC


I hope this clarifies the output. Thanks for the help!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[CoSpringsGuy]

if you are JUST trying to show employees who waived .. in record selection

Waived Medical

{BENEFIT.BEN_CODE} = "Waived Medical"

or

instr({BENEFIT.BEN_CODE},"Waived") > 0


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[FireGeek21]

CoSpringsGuy, I am trying to shw anyone who waived medical AND still show all the other benefits they have elected. It is a report to show everything and, because of health care reform, we need to show that the staff was offered medical and they waived it by not electing it. This report will be sent to each staff to offer them yet another chance to elect a medical plan (by them seeing the "waived medical") in case they missed electing it the first time around.

I tried applying this formula and it is not working. Thanks for your efforts.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[IanWaterman]

Group by EMPLOYEE and list all benefits

Create formula
@MedWaived
If {BENEFIT.BEN_CODE} = "Waived Medical" then 'Y' else 'N'

In group group select Not record selection enter

Maximum(@MedWaived, EMPLOYEE) = 'Y'

This will suppress employees who have NOT waived medical

Ian

 

[FireGeek21]

Ian,

Notice the BENEFIT table has Types - Codes as follows:

HL - CC (medical)
HL - CO (medical)
HL - CT (medical)
HL - VS (vision)
HL - EP (eap)
DN - DN (dental)
EL - LF (basic life)
DL - DL (dependent life)
HS - HA (hsa)
HS - HC (health care flex)
HS - DC (dependent care flex)

"Waived Medical" isn't included. I'm trying to produce it programmically if someone doesn't have medical, yet still show all the other benefit types - codes they may have.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[IanWaterman]

You posted sample data

123465789 HL EP
123465789 HL Waived Medical
123465789 EL LF
123465789 HS DC

987654321 HL EP
987654321 HL CO
987654321 HL VS
987654321 DN DN
987654321 EL LF
987654321 HS DC

654321789 HL EP
654321789 HL Waived Medical
654321789 DN DN
654321789 EL LF

456123789 HL EP
456123789 HL CC

How does it really look if medical is waived?

Are you saying there is no record? If so how do you differentiate between waived and not offered?

Ian

Ian

 

[FireGeek21]

Ian,

That should be sample output expected.

My data is:
- EMPLOYEE table that has data such as: EMPLOYEE #, NAME
- BENEFIT table has data such as: EMPLOYEE #, BEN_TYPE, BEN_CODE, START_DT, STOP_DT.

Some of the TYPES - CODES found in the BENEFIT TABLE are listed below:

HL - CC (medical)
HL - CO (medical)
HL - CT (medical)
HL - VS (vision)
HL - EP (eap)
DN - DN (dental)
EL - LF (basic life)
DL - DL (dependent life)
HS - HA (hsa)
HS - HC (health care flex)
HS - DC (dependent care flex)

All employees will have BEN_TYPE HL and BEN_CODE EP and all benefit eligible employees with have BEN_TYPE EL and BEN_CODE LF. There shouldn't be an issue with an EMPLOYEE not existing in the BENEFIT table. I am already taking into consideration the start/stop dates so that is not an issue. FYI, someone may never have had medical before or maybe they had medical in the past but not now. Basically looking at today and if they have an active medical plan with no stop date or a stop date in the future.


FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[IanWaterman]

I still have no idea what the data looks like for employee 123465789 before you process it

What raw data would you see in order to output Medical waived.

Ian

 

[fisheromacse]

just taking a running leap of a guess here and doing so without crystal in front of me to check for syntax errors, but.....


create a formula {@HasMedCount} and place it in your details section.
//{@HasMedCount}
numbervar hmc;
IF (
({table}.{type} = 'HL' and {table}.{Codes} = 'CC') OR
({table}.{type} = 'HL' and {table}.{Codes} = 'CO') OR
({table}.{type} = 'HL' and {table}.{Codes} = 'CT')
)
then hmc := hmc + 1 else hmc := hmc;
hmc


create a formula {@HasMedReset} and place it in your employee group header.
//{@HasMedReset}
numbervar hmc := 0;


Create another formula {@HasMedCheck} and place it in your employee group footer
//{@HasMedCheck}
numbervar hmc;
IF hmc > 0 then "Has Medical" else "Medical Waived"

 

[FireGeek21]

fisheromacse,

Interesting approach. I haven't tried this method... yet. I will apply and let you all know. THANK YOU for the idea!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)