A customer asked a very good question about filtering reports. We wanted to share her questions with you so here it is::
I am working on pulling an order summary and order detailed report of all orders that have a particular SKU (EMB%) on a monthly basis. This report will be used for invoicing a vendor, so it needs to be accurate.
Could you explain the logic of Date Options (Order, Ship, Payment) and Order Status (Shipped, Cancelled, Pending) fields in the search function? I want to make sure I am not missing any orders. Orders may be paid for in one month and shipped in a later month. Orders may be in a pending status too. I only want to capture orders once.
I am using the Orders -> Product Sales -> Sales by Date report.
This report gives me a summary of the orders that have SKU I am looking for. Is there a way I can filter for the individual orders that make up the report?
Our Answer:
My first thought is to use the “Ship” date option and the “Shipped” order status; however, not all orders necessarily have a ship or paid date, but all orders do have an order date.
At some point, pending orders will either become shipped or canceled. Using the ship date, you capture the order in the period it was shipped. Using these filters, the order should only display once without duplication in subsequent periods. Cancelled orders never become an issue when reporting this way.
As you pointed out, there are some issues using Order Date and the Shipped/Pending status which make using these filters unreliable for the use case scenario you described. Orders can be taken in one month (Order Date), paid in another (Paid Date), and finally shipped in another (Shipped Date).
Even though an order has been paid, doesn’t mean it won’t be canceled before it ships. This possibility makes using the order or paid date undesirable while an order is still pending. And for this very reason, I recommend using the “Ship” date option along with the “Shipped” order status
Take a look at the Orders -> Sales Summary or Sales Summary by Product reports. You can filter both reports by a range of products like EMB% via Products tab in the Data Filters. The results will yield a list of matching orders with the products, date range, and filters specified.
We hope this post has been helpful. Any questions or comments, please feel free to post.