Previous/Next record in SQL.

[TopJack]

I'm using Crystal Reports 9 and using the functions "Previous" and "Next" records to calculate a formula. The Previous and Next functions seem to be evaluated as the report is printed and not in the SQL that creates the source. Is there a command I can use directly in the SQL to find a previous or next record? Any help or pointers would be appreciated.

Thanks,
TopJack.

 

[IdoMillet]

The general answer is "No" but if you explain what you have in mind, it may be possible to suggest a solution. For example, you can use a WhileReadingRecords formula to load the value into a variable and compare the value in the variable to the value in the next record...

hth,
- Ido

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

http://www.MilletSoftware.com

 

[TopJack]

I thought the answer might be no, I couldn't find anything about the subject. The kind of data I'm looking at is like this.

Order Line Date
=== === =======
123 10 13/05/05 (dd/mm/yy)
123 20 15/05/05
123 30 Null
123 40 Null
345 10 23/05/05
345 20 26/05/05
345 30 27/05/05
345 40 Null

I want the report to find the first "Line" on an "Order" with a Null but to also carry the previous "Line" date on the "Order" for comparison.

The report output of the table above should be :-

Order Line Date
=== === =======
123 30 15/05/05 (dd/mm/yy)
345 40 27/05/05

The report already does this without a problem using a surpression formula and the function "Previous(Date)"

The issue I have is the report output needs to be sorted by descending date. As soon as I sort by date I can't use the Surpression and Previous record function because the Orders and Lines are out of sequence.

I was thinking if I could pull the data using a smarter SQL the importance of holding the Order and Line sequence wouldn't be so relevant.

I almost want to pass the data through the report twice but I dont know how to.

I hope I have explained myself clearly.

Thanks,
TopJack.

 

[IdoMillet]

If your data source supports an ORDER BY in the SQL, just apply the sort that way (in a Command, View. or Stored Procedure). That way, Crystal doesn't need to bother with the sort.

Alternatively, simply Group on Order and sort by Line or by date. This would ensure the records are first sorted by Order.

hth,
- Ido

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

http://www.MilletSoftware.com