Crystal Formula? - problems

[qu1ncy]

Hi,
I'm suffering here. Hope you can refresh my memory. I'm pulling Employee data from an Oracle view. Each employee has many records reflecting changes (events) during their tenure such as being hired, merit increases, dept changes, etc. Each change is assigned a 'version' number (version) the highest of which represents the most recent change event, and an effective date (eff_date)the change took place. I need to create a formula which will pull past records 'as of' a parameter date. Meaning (I think I have the logic right), I need the maximum(version) of all records where the eff_date is less than the parameter date. But I don't know how to set this up properly. Any assistance will be much appreciated.
Thanks
Q

 

[chelseatech]

Use the following method.

1) Group your data by Employee
2) Group within that by EventCode
3) To get the last event of each code use a GROUP SELECTION formula of
{Table.EventDate} =
Maximum({Table.EventDate},{table.EventCode))

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[IdoMillet]

I would approach it as follows:

1. Group by Employee and sort the records by Version within Employee.

2. Set a record selection formula of:
----------------------------------
{eff_date} < {?eff_date_parameter}
----------------------------------

3. Insert the fields you want to see in the Group Footer
(Employee) and suppress the detail section.

Since the Group Footer shows the last record within
the group, and since the records are sorted by Version,
you get want you want... )

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[qu1ncy]

Thank you chelseatech, idomillet for your assistance. I'll give each suggestion a try.

Q