maximum based on multiple fields 2

[psimonnyco]

Hello

I am running CRX. I have easily done a maximum(check_date, employee) calculation to pull an employee's latest check date. I did my group on EMPLOYEE and got one record per employee (although the employee has multiple checks).

The following is vexing me, though.

EMPLOYEE, PLAN_TYPE, PLAN_CODE, START_DATE
12345678, HEALTH, SIGNA1, 1/11/08
12345678, HEALTH, SIGNA1, 1/21/08
12345678, HEALTH, SIGNA1, 1/31/08
12345678, DENTAL, SIGNA2, 1/11/08
12345688, HEALTH, SIGNA1, 3/30/08
12345698, DENTAL, SIGNA2, 1/11/08

In this example, I want the following set returned:
12345678, HEALTH, SIGNA1, 1/31/08
12345678, DENTAL, SIGNA2, 1/11/08
12345688, HEALTH, SIGNA1, 3/30/08
12345698, DENTAL, SIGNA2, 1/11/08

I want to pull only the latest enrollment per employee (max of start date) by plan code. I don't see how I can use the Maximum function, as I'm basing my caculation on more than one field (unlike my first example above). I can do this in Access very easily in one simple query:

SELECT BENEFIT.EMPLOYEE, BENEFIT.PLAN_TYPE, BENEFIT.PLAN_CODE, Max(BENEFIT.START_DATE) AS MaxOfSTART_DATE
FROM BENEFIT
GROUP BY BENEFIT.EMPLOYEE, BENEFIT.PLAN_TYPE, BENEFIT.PLAN_CODE
ORDER BY BENEFIT.EMPLOYEE;

Any ideas? Possible concatenation?

Thanks.

 

[beltmanjr]

I believe that in the group record selection formula editor (report - selection formulas - group) you could put something like

{BENEFIT.START_DATE} = Maximum({BENEFIT.START_DATE})

 

[dgillz]

Concatenate the employee and plan type in a formula:

{Employee}&{PlanType}

Then group on this field, rather than by employee. Then take the maximum as you have been doing already.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[psimonnyco]

{BENEFIT.START_DATE} = Maximum({BENEFIT.START_DATE}) wouldn't work, as this is employee-specific.

The concat grouping works. Thanks, dgillz!

 

[lbass]

You could have used the group selection formula, if you added a group condition, as in:

{BENEFIT.START_DATE} = Maximum({BENEFIT.START_DATE},{table.plantype})

-LB

 

[flaaquileo]

I need to select max effective date in a health plan for employees. Employees have multiple health plan records. I believe the formula is
Maximum({EMPLOYEE_BENEFITS.EFFDT}, {EMPLOYEE_BENEFITS.EMPLID})
My report is grouped by EMPLID (employee).
I tried setting this up as a formula. I tried putting the criteria in my record selection.

This is a sample of my data

EMPLID, PLAN_TYPE, PLAN_CODE, START_DATE
12345678, HEALTH, EPO, 1/11/08
12345678, HEALTH, DP-EPO, 1/21/08
12345680, HEALTH, HMO, 1/11/08
12345680, HEALTH, HMO, 1/31/08
12345681, HEALTH, EPO, 3/30/08
12345681, HEALTH, EPO, 1/11/08

I want the following rows returned:

12345678, HEALTH, DP-EPO, 1/21/08
12345680, HEALTH, HMO, 1/31/08
12345681, HEALTH, EPO, 3/30/08

How do you set up the Crystal report to select only max effective dates?
Can anyone help?
Thanks.

 

[lbass]

What is the issue you are having after trying my solution (posted right before your post)? It should work.

-LB

 

[psimonnyco]

I think that I'm good now. Thanks.

 

[flaaquileo]

Thanks lbass, it did work.

 

[scott1971]

Guys, using Crystal XI an I need to get a maximum out of multiple fields.

My fields are located in GH3 and I have 5 fields though not all will be completed.

My fields are as follows:

{proc1} {procamt1)
{proc2} {procamt2)
{proc3} {procamt3)
{proc4} {procamt4)
{proc5} {procamt5)

What I need to show is the Maximum amount from fields {procamt1} through {procamt5} and show that amount AND the relevant {proc1} through {proc5} field. I then want to be able to work out that maximum field + 25% or 40% dependant on another formula I have in this heading.

sorry, there's a lot in there but I'm stumped as how to do this. Hoping the +25% will be easy enough but I need that maximum before I can work that out.

Cheers

Scott

 

[lbass]

1-Are these really 10 different fields, or two different fields with five instances of each?

2-You say they are located in the GH3 section. Are these literally database fields that you have placed there or are they summaries at the Group #3 level? If so, what are they?

3-If you are looking for a maximum, would that be the maximum within Group #2? Or for the entire report?

-LB