How To: Run a report on orders using a SQL query

Here's a simple SQL Query to get orders directly from the database using the builtin DNN Host SQL page.  Of course, modify PortalID as necessary; you could also easily filter by date and whatnot.
SELECT Smith_StoreOrders.OrderDate, Smith_PayHist.Amount - Smith_StoreOrders.ShippingTotal - Smith_StoreOrders.TaxTotal AS SubTotal,Smith_StoreOrders.ShippingTotal, Smith_StoreOrders.TaxTotal,
Smith_PayHist.Amount AS Total,
Smith_StoreOrders.ShipFirstName, Smith_StoreOrders.ShipLastName
FROM Smith_StoreOrders INNER JOIN
Smith_PayHist ON Smith_StoreOrders.PayHistID = Smith_PayHist.PayHistID
WHERE (Smith_StoreOrders.PortalID = 0) AND (Smith_StoreOrders.Status <> 'Cancelled')
The Reports module in DNN allows you to do some pretty spiffy stuff.

Here is a query that I use in the Reports module that shows me Model Number, Model Name, QOH, and Categories that the product resides in.

SELECT Smith_Products.ModelNumber, Smith_Products.ModelName,Smith_Products.QuantityOnHand, '('+ CAST(Smith_CategoryProduct.CategoryID AS Varchar(20)) + ') ' +Smith_Category.CategoryName AS Category
FROM Smith_Category INNER JOIN
Smith_CategoryProduct ON Smith_Category.CategoryID = Smith_CategoryProduct.CategoryID LEFT OUTER JOIN
Smith_Products ON Smith_CategoryProduct.ProductID = Smith_Products.ProductID
WHERE Smith_Products.PortalID = 0 AND Smith_Products.Archived = 0
ORDER BY Smith_Products.ModelNumber

Add Feedback