Group Selection Question

[TEM3]

We are using Crystal Reports 8.5 and Oracle 9i tables.

In the tables I am using, a "Case" is a singular. There is only one case. However, a case can gather multiple "Assignments" which have starting dates, ending dates and some other activity dates collected along the way. My report is grouped by case and then by assignment.

I want to track any case with at least one assignment that was completed in the current year. Most assignments are started and completed in the same year. However some completed cases have assignments that span several years. Therefore I cannot filter on the starting date of case assignments. But I do want to select only cases that have a completed date in the current year.

How can I group select cases that have at least one completed assignment in the current year? Remember I want to look at all assignments, even those created and completed in years past, as long as at least one was completed in the current year.

Basic Tables:

LABCASE LABREPT
Case Key ----------> Case Key
left outer Date Assigned
Date Completed
etc.

 

[lbass]

Don't filter on dates in the record selection formulal. Instead, go to report->selection formula->GROUP and enter:

maximum({table.completeddate},{table.case}) >= date(year(currentdate),1,1)

-LB

 

[synapsevampire]

Group the report by the case.

Create a formula of:
//@CntCurrYear
if year({table.enddate}) = year(currentdate) then
1
else
0

Now go into the Report->Selection Formula->Group and place:

sum({@CntCurrYear},{table.enddate}) > 0

-k