Sales Order History GI/report

Hi ​@swartzfeger ,

To see the customer, item, and the last order date for a customer and item, you can group that data.

First, In the conditions in the report Schema Builder include a condition to filter SOOrder by OrderDate for your desired date range.  

In the Groups collection of the report properties, add a group that groups by SOOrder.CustomerID and SOLine.InventoryID.  In the header or footer for that group, add a text field with a formula like max([SOOrder.OrderDate]).

If you need to see other details, such as the order number, that can be accomplished using a left join.  The idea is to check that there isn't another order for the same inventoryid and customer with a later date.  Let me know if that's your objective and I can provide more details.

Hope this helps!

Laura 

Sorry for the delay, Laura -- thank you for the detailed response! Will be working on this today and implementing your method. Leaving this open as I may have a few questions, lol. :)

Hi Laura, I’ve mostly had luck with this but there are some hangups --

It lists the customers’ current order, order date and order total, and it lists the previous SO’s correct date, but the previous SO# and previous SO total is duplicating the current order’s values.

One of the things is I’m probably using the formulas incorrectly--

=max([SOOrder.OrderNbr])

=max([SOOrder.OrderDate])

=max([SOOrder.CuryOrderTotal])

Also, I “repurposed” an old report so there are parameters for other data ranges that aren’t being used (ranges B through F, but for some reason when I delte those parameters the report won’t load, so I just hid their visibility).

Thanks if anyone is able to look at the the .rpx!

Hi ​@swartzfeger ,

Because SOOrder.OrderNbr is a field in your grouping, the footer section will be displayed for each individual order.  Remove this field from the grouping to display only footer per customer with the maximum order date in the footer.  You could also unhide the detail section to see the individual order details above.  To display everything on one line is trickier and would probably require the use of  variables.

Hope this helps!

Laura