Forum Discussion
Northwind Traders Developer 2.2 Template Sales Reports bug
I believe there is a bug in the Monthly Orders by Product report, or more specifically in the underlying query qryrptSalesByProduct. This report shows each product from orders in the specified timeframe, however it shows the order total rather than the item total from that order. So if you have one order with 2 different items on it, say one for $50 and the other for $75, the report will show 2 rows (one for each of the items), but the amount on each row is the order total of $125 instead of their respective extended amounts, and then the report total will show $250 instead of the actual total of the order which is $125.
This causes the Monthly Orders by Product to have much different (and higher) amounts than the Monthly Orders per Employee report for the same period.
Hi Sue_69
We have a solution for the query issue you have identified in the NW2.2 Dev Edition.
Please change query qryrptSalesByProduct to the following
SELECT Products.ProductName, Products.ProductID, Format([OrderDate],"mmm-yyyy") AS MonthYear, Format([OrderDate],"yyyy-mm") AS MonthYearSort, Format([OrderDate],"q-yyyy") AS QuarterYear, Sum([OrderDetails].[Quantity]*(1-[OrderDetails].[Discount])*[OrderDetails].[UnitPrice]) AS SumOfOrderTotal
FROM Products INNER JOIN (Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) ON Products.ProductID = OrderDetails.ProductID
WHERE (((Orders.OrderDate) Between [Forms]![frmReports].[txtStartDate] And DateAdd("d",1,[Forms]![frmReports].[txtEndDate])))
GROUP BY Products.ProductName, Products.ProductID, Format([OrderDate],"mmm-yyyy"), Format([OrderDate],"yyyy-mm"), Format([OrderDate],"q-yyyy");We would also like to suggest you change the caption on rptSalesByProductQuarterly to Quarterly Orders By Product - the current caption is the same as the rptSalesByProduct. If both are open at the same time it will be confusing.
Great find! Thank you for your feedback.
-Kim-
- Hi Sue,
Thank you again. Where were you when we needed beta testers 🙂
We'll look into this over the weekend and will post steps to fix it. It's a great find: compare two reports and the totals should be the same. Hi Sue_69
We have a solution for the query issue you have identified in the NW2.2 Dev Edition.
Please change query qryrptSalesByProduct to the following
SELECT Products.ProductName, Products.ProductID, Format([OrderDate],"mmm-yyyy") AS MonthYear, Format([OrderDate],"yyyy-mm") AS MonthYearSort, Format([OrderDate],"q-yyyy") AS QuarterYear, Sum([OrderDetails].[Quantity]*(1-[OrderDetails].[Discount])*[OrderDetails].[UnitPrice]) AS SumOfOrderTotal
FROM Products INNER JOIN (Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) ON Products.ProductID = OrderDetails.ProductID
WHERE (((Orders.OrderDate) Between [Forms]![frmReports].[txtStartDate] And DateAdd("d",1,[Forms]![frmReports].[txtEndDate])))
GROUP BY Products.ProductName, Products.ProductID, Format([OrderDate],"mmm-yyyy"), Format([OrderDate],"yyyy-mm"), Format([OrderDate],"q-yyyy");We would also like to suggest you change the caption on rptSalesByProductQuarterly to Quarterly Orders By Product - the current caption is the same as the rptSalesByProduct. If both are open at the same time it will be confusing.
Great find! Thank you for your feedback.
-Kim-
- Thank you for the quick response AccessAllstars
