MOST RECENT DATE RECORD

[kettie]

I'm attempting to develop a report which will bring back an employee's most recent hearing test record.

The data could look something like :

Employee A : 19/5/97
19/5/99
19/5/01

Employee B : 4/4/98
4/4/00
4/4/02

In this instance, I would only want to retrive the data associated with the 19/5/02 test (Emp A) and 4/4/02 test (Emp B)

Is there some formula I can write in the select expert that will automatically check all date records and return the correct data.

Any help would be most appreciated

 

[synapsevampire]

What version of Crystal, and what database/version are you using?

There are a few ways that you can approach this, using a View or Stored Procedure on the database, or passing through SQL with grouping to get the MAX (if version 9) being the most efficient.

In Crystal, you can group by the Employee and sort by the Date, then suppress the details section and use the Employee group footer to display your fields (the highest date will be the last for the employee ID and hence show up as the only).

Or you might use a Top N report by grouping on the Employee, and then creating a max(date) Total with where N is 1 and do not show others.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kettie]

Thanks

I'm using Crystal V8.5 on SQL. I'll give your suggestions a go.

 

[kettie]

Hi synapsevampire

I'm not a genius with formulas. If you wouldn't mind, could you please explain a bit further about the TopN report. I understand the bit about grouping by employees, but not totalling where N is 1. How do I get N to equal 1?

Sorry to be such a duffer

 

[dgillz]

TopN is not a great way to get this report, but it will work. To make a TopN, you must first have a grouping -- TopN reports only work with groups -- and then click on the TopN/Sort Group Expert icon, or select the TopN/Sort Group option from the report menu.

After you select that menu option, it is very easy to follow, your choices are All, TopN or BottomN, and then you would just make N=1. No formulas are required.

As I stated earlier I would not make a topN for this report, I would just create a group by employee and insert a maximum summary on the date field, then hide the detail section. Much simpler.

Let me know if you have any questions. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com