Hi! All JetReport Experts:
I would like to know how to get the latest sales quotes sorted by order date in the header. I checked the knowledge base but the answers are all for getting a SINGLE latest quote/order using NL("Last"…). My case is a little bit different, I need to get many latest sales quotes. Let me explain a little bit further.
I use the Sales Quote as a data holder of customer requirement forecast. The customer may send me forecast like this:
Order Date : 2012/4/9, SQ No. : SQ1, Ship-To Code: ST1
item no. : item1, quantity: 100, ship request date : 2012/4/23
item no. : item1, quantity: 200, ship request date : 2012/4/30
item no. : item2, quantity: 200, ship request date : 2012/4/23
Order Date : 2012/4/9, SQ No. : SQ2, Ship-To Code: ST2,
item no. : item1, quantity: 50, ship request date : 2012/4/23
item no. : item1, quantity: 100, ship request date : 2012/4/30
item no. : item2, quantity: 200, ship request date : 2012/4/30
Order Date : 2012/4/9, SQ No. : SQ3, Ship-To Code: ST3
item no. : item1, quantity: 50, ship request date : 2012/4/23
item no. : item1, quantity: 70, ship request date : 2012/4/30
item no. : item2, quantity: 200, ship request date : 2012/5/7
Next week, the customer updates the forecast like this:
Order Date : 2012/4/16, SQ No. : SQ4, Ship-To Code: ST1
item no. : item1, quantity: 100, ship request date : 2012/4/23
item no. : item2, quantity: 150, ship request date : 2012/4/23
item no. : item2, quantity: 300, ship request date : 2012/4/30
Order Date : 2012/4/16, SQ No. : SQ5, Ship-To Code: ST2,
item no. : item1, quantity: 50, ship request date : 2012/4/23
item no. : item1, quantity: 80, ship request date : 2012/4/30
item no. : item2, quantity: 100, ship request date : 2012/4/30
Order Date : 2012/4/16, SQ No. : SQ6, Ship-To Code: ST3
item no. : item1, quantity: 50, ship request date : 2012/4/23
item no. : item1, quantity: 90, ship request date : 2012/4/30
item no. : item2, quantity: 200, ship request date : 2012/5/7
The customer will keep sending requirements like above every week. New Sales Quotes will be created but the old ones don't get deleted. I only need the latest created Sales Quotes as the latest forecast. What I would like to list in JetReport is like:
Item 1……….2012/4/23, 2012/4/30, 2012/5/7, …
forecast Qty… 200, 170, 0, …
Item 2………. 2012/4/23, 2012/4/30, 2012/5/7, …
forecast Qty…. 150, 400, 200, …
So the conditions are:
1. SQ1, SQ2 and SQ3 will be ignored because their order dates are older.
2. Forecast Qty is the sum of quantity with the same ship request date regardless of ship-to code.
3. I use NL(row=3) to list the forecast by item.
4. I have a customization to copy ship-to code from the sales header to Sales Line. So I can get ship-to code directly from sales line.
I really can't figure out how to use JetReport's commands to get this result. If I use NL("Last") command sorting by order date, JetReport would only return one Sales Quote. Can anyone help me out please?
0 comments
Please sign in to leave a comment.