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
You can use rank function to get the 2 shipments and filter for top 2.
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?