Thread: order report for Product Type

Was wondering if any one had written a custom report for finding all orders by *Product Type or how one would do so...

Are you just looking for order numbers? In it's most basic form, you could use this

Code:

select o.OrderNumber from dbo.Orders o with(NOLOCK)
join dbo.Orders_ShoppingCart osc on osc.OrderNumber = o.OrderNumber
join dbo.Product p on p.ProductID = osc.ProductID
where p.ProductTypeID = 1

where 1 is the product type id that you want to search on. If you're looking to pull additional information (like how many of that product were ordered per order number, or the order total, just include those in the select part of the query

Code:

select o.OrderNumber, sum(osc.Quantity), sum(osc.OrderedProductPrice * osc.Quantity) from dbo.Orders o with(NOLOCK)
join dbo.Orders_ShoppingCart osc on osc.OrderNumber = o.OrderNumber
join dbo.Product p on p.ProductID = osc.ProductID
where p.ProductTypeID = 1
GROUP BY o.OrderNumber

You can get the email address of the ordering customer by joining the customer table as well

Code:

select o.OrderNumber, c.Email, sum(osc.Quantity), sum(osc.OrderedProductPrice * osc.Quantity) from dbo.Orders o with(NOLOCK)
join dbo.Orders_ShoppingCart osc on osc.OrderNumber = o.OrderNumber
join dbo.Customer c on c.CustomerID = o.CustomerID
join dbo.Product p on p.ProductID = osc.ProductID
where p.ProductTypeID = 1
GROUP BY o.OrderNumber, c.Email

You're right George, I guess I should be more specific. I didn't really think of that. (doh!)

There are these canned reports in the order menu: Orders by Category.
These reports have all kinds of nice stats in them, but can't be filtered by Product Type.

Having the ability to filter this report by Product Type would be perfect. Else, create another custom report that would have some order stats in it, like $ spent, dates, etc.