Report - How would you go about this?

[damienm]

Hi I need to create a report that can be filtered on a sequence column, i.e. show only records 1+2.

First problem, how would you go about sequencing records based on two criteria, product code and ETA date, basically I only want to display the next two shipments of a particular product on the report. Can a calculated field do this?

Basically I need the report to look something like the below, I must list enough orders to cover the forecast, I was thinking by sequencing the reports based on ETA and filter showing the 1st and 2nd order would normally cover the forecast quantity but if there is a way to list enough orders to cover the forecast quantity even better.

Product  Forecast        ETA        Order Qty
xxxxx         185        12/02/08        150
                              18/03/08        15
                              20/04/08        50

[Suraj]

You can use rank function to get the 2 shipments and filter for top 2.

[almeids]

Well...you can ALMOST cover the forecast, if you sort by ETA and add a filter running-total([order_qty]) < [forecast].  This would give you one less delivery than you need.  Maybe (running-total([order_qty])-[order_qty])<[forecast] would do it?