previous values in relational report

[madhancog]

Hi gurus,
  Let me know how to get previous values in relational reporting.
  order number  prev oredernumber
     1002                 --
     1005                1002
     1006                1005

let me know how can i get the prev value in relational model.

Thanks in advance
K.M

[Lynn]

I don't know how your data is structured, but you might try using the running-count function. Then join the query to itself.

Create your base data query to return:

Code Select Expand


Order OrderCount PrevCount
1002     1               0
1005     2               1
1006     3               2


Create the OrderCount item by using the expression running-count(1). You may need to provide scope via the "for" clause.

Create the PrevCount item by using the expression [OrderCount] - 1.

Now create your previous query. This is just a query reference back to base data query and drag in [Order] and [OrderCount].

Finally, create your report query as an outer join between the base data query and the previous query. Join [PrevCount] from base data query as 1..1 to [OrderCount] in previous query as 0..1.

Drag the desired elements from the two queries into the report query and associate your layout to this query.

This will require a lot of local processing so for very large data volumes it may not be practical. If this is the case you may want to investigate database options so that the previous order number is available directly.