Find a record and the previous record

[rhino143]

Hello and thanks in advance for reading through my question.

I'd like a crystal that finds end events ["INCOMPLETE","REVIEWED","COMPLETED"] and compares them to an immediately previous event["REVIEW","INCOMPLETE","REJECTED"]. If the previous event is in that list then the report would show the end event under the userID group.

Example:

11/01/05 Created BJohnson
11/02/05 Review RSmith
11/03/05 Reviewed EJones

Should show:
EJones

11/03/05 Reviewed

EJones Total: 1

Chris

 

[synapsevampire]

Please take the time to post technical information.

Group by USER ID, place the following formula in the suppress formula of the group footer:

not(
{table.field} in ["INCOMPLETE","REVIEWED","COMPLETED"]
and
previous({table.field}) in ["REVIEW","INCOMPLETE","REJECTED"]
)

Place the fields in the group footer and they'll show when those conditions exist.

-k

 

[rhino143]

I apologize, I had the technical info in my first draft, then deleted them by accident in subsequent ones.

I'm using crystal reports 8.5 on WindowsXP connecting to SQL tables.

The only question I have for this is if I put the fields in the footer where would I put the group totals?

I believe the report would show as:

GH: UserID
Details: Suppressed
GF: 11/03/05 Reviewed

Also, how would I suppress those UserIDs from showing if they didn't have any events in that time frame and with the beginning to end events?

Thanks again!
Chris

 

[rhino143]

By the way, this is the actual code I put into the report:

not
(
{WA5U999S.EVENT_DATTIM} > CurrentDate-21 and
{WA5U999S.WRKTYPE}= "COURTESY" and
{WA5U999S.STATCD} in ["INCOMPLETE","REVIEWED","COMPLETED"] and
previous({WA5U999S.WRKTYPE}) = "COURTESY" and
previous({WA5U999S.STATCD}) in ["REVIEW","INCOMPLETE","REJECTED"]
)

I added the date limitation because they only want to see one month, and the worktype limitations because they needed to be there.