Maximum date as Selection criteria 1

[timroop]

I have a payroll report with a subreport. They are linked on the Employee Id. The subreport pulls from a table with check summary information. There is one entry for each check writen to an employee. I need the subreport to return only the last check written. As it is now it returns a detail section for each check in the table.

I have a summary function named lastcheckdate that correctly returns the date of the last check. I have tried the selection expert and had it return based on:

{UPR30100.EMPLOYID} = {?Pm-UPR00100.EMPLOYID} and
{UPR30100.CHEKDATE} in Last7Days

That works for the most part but if the last paycheck for an employee was more than a week ago then no data is returned. Basically I need to do a:

{UPR30100.EMPLOYID} = {?Pm-UPR00100.EMPLOYID} and
{UPR30100.CHEKDATE} = maximum(upr30100.checkdate)

But crystal reports that the maximum summary needs to be calculated later (or something to that effect).

Ideas?

Please and Thank you.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[timroop]

I forgot to add that we are using Crystal XI R2.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[CR85user]

Add a group within your subreport on {upr30100.checkdate}, keep your selection criteria in the subreport as:

{UPR30100.EMPLOYID} = {?Pm-UPR00100.EMPLOYID}

and move the fields in the subreport from the detail section to the footer of the {upr30100.checkdate} group.

'J

CR8.5 / CRXI - Discovering the impossible

 

[timroop]

[bold]CR85user[/bold] Thanks for the feedback. I removed the selection criteria, created a UPR30100.checkdate group and moved the detail fields to the group footer. I'm still getting a set of entries for each check in the table.

Did I miss something?

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[lbass]

In the subreport, create a group on employee and then (still in the sub) go to report->selection formula->GROUP and enter:

{UPR30100.CHEKDATE} = maximum(upr30100.checkdate),{UPR30100.EMPLOYID})

In the record selection formula area, just leave the link on employee ID.

-LB

 

[kskid]

Here's what I have done on reports similar to yours
1. Create a group on {UPR30100.EMPLOYID}
2. Move the check detail fields into the group header
3. Suppress the detail section
4. Insert a sort on date in descending order. This will display the most recent check.

 

[timroop]

That was the missing piece.
Thank you [bold]lbass[/bold]
I appreciate the input.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[lbass]

kskid's solution would also work.

-LB

 

[timroop]

I'll give it a look. I'm always looking for more techniques.

Thanks again.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva