Crystal Report Question 1

[haeddy]

Hope someone out there can help me with this report.

I'm trying to create a report that will show the most current health and dental insurance coverage our employees have. The report is ommitting coverage when there are diffferent effective dates i.e. when the health and dental insurance have different effective dates, I only get the one with the most current date - not both health and dental like I get for the employees that have the same effective dates for both plans). I'm using two tables, one with employee information and one with benefit information and the tables are linked by employee ID number (EMPLID).

The Select Expert group formula I used is:

{PS_HEALTH_BENEFIT.EFFDT} = maximum ({PS_HEALTH_BENEFIT.EFFDT},{PS_HEALTH_BENEFIT.EMPLID})

Thanks to anyone out there that can give me some guidence on this!

 

[kenhamady]

Your formula specifies that you should only see records where the date matches the last date for the EMPLOYEE. So if only one of their plans matches that date then you will only see one. If both plans match that date you will see both.

What you need is a second group by coverage type (health, dental, etc). This should be within the primary employee group. Then ask for records where the date matches the maximum date for the coverage type group. Something like this (I am making up the last field name):

{PS_HEALTH_BENEFIT.EFFDT} = maximum ({PS_HEALTH_BENEFIT.EFFDT},{PS_HEALTH_BENEFIT.COVG_TYPE})


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Charliy]

It would help to know what your data looks like. Does a single record have fields for Health Care Effective Date and Dental Care Effective Date, or are they in separate records with a Type Flag as Ken suggested? You may be able to just group on the patient, and put the most recent results in the group footer.