Volusions report w sales number and price - Ecommerce Forums
Results 1 to 8 of 8

Thread: Volusions report w sales number and price

  1. #1

    Volusions report w sales number and price

    Hi guys, so I'm trying to get a report within Volusion to show me the following:

    • Product code
    • Quantity sold
    • Average price per item sold

    Of course, I would also like to be able to define the time frame of the report as well.

    Any help would be appreciated.

    Thanks
    Ken

  2. #2
    Ken there is very little you can do with reports in V unless you know sql. This is one of the main reasons we are looking at BC to move to.

  3. #3
    This should work and let you change the date:

    Code:
    SELECT OrderDetails.ProductCode, OrderDetails.Quantity, AVG(OrderDetails.ProductPrice) AS Average_Price
    FROM OrderDetails
    JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
    WHERE Orders.OrderDate >= '01/01/2001 12:00 AM'
    AND Order.OrderDate <= '12/31/2014 11:59 PM'

  4. #4
    Thanks bfarrow. I get the following error when I run it in Volusion:
    Column 'OrderDetails.ProductCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Quote Originally Posted by bfarrow View Post
    This should work and let you change the date:

    Code:
    SELECT OrderDetails.ProductCode, OrderDetails.Quantity, AVG(OrderDetails.ProductPrice) AS Average_Price
    FROM OrderDetails
    JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
    WHERE Orders.OrderDate >= '01/01/2001 12:00 AM'
    AND Order.OrderDate <= '12/31/2014 11:59 PM'

  5. #5
    Quote Originally Posted by Ken View Post
    Thanks bfarrow. I get the following error when I run it in Volusion:
    Column 'OrderDetails.ProductCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Whoops, spaced out and forgot that. Try this instead:

    Code:
    SELECT OrderDetails.ProductCode, SUM(OrderDetails.Quantity) AS Total_Sold, AVG(OrderDetails.ProductPrice) AS Average_Price
    FROM OrderDetails
    JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
    WHERE Orders.OrderDate >= '01/01/2014 12:00 AM'
    AND Orders.OrderDate <= '12/31/2014 11:59 PM'
    GROUP BY OrderDetails.ProductCode

  6. #6
    Now I get this error...

    SQL Error: SELECT * ,157 as RecordCount FROM (SELECT TOP 10 * FROM (SELECT TOP 10 OrderDetails.ProductCode, SUM(OrderDetails.Quantity) AS Total_Sold, AVG(OrderDetails.ProductPrice) AS Average_Price FROM OrderDetails JOIN Orders ON OrderDetails.OrderID = Orders.OrderID WHERE Orders.OrderDate >= '01/01/2015 12:00 AM' AND Orders.OrderDate <= '01/31/2015 11:59 PM' GROUP BY OrderDetails.ProductCode ORDER BY ) SUBSEL ORDER BY ) PAGESEL ORDER BY

    I appreciate all the help. Thanks!

  7. #7
    Quote Originally Posted by Ken View Post
    Now I get this error...

    SQL Error: SELECT * ,157 as RecordCount FROM (SELECT TOP 10 * FROM (SELECT TOP 10 OrderDetails.ProductCode, SUM(OrderDetails.Quantity) AS Total_Sold, AVG(OrderDetails.ProductPrice) AS Average_Price FROM OrderDetails JOIN Orders ON OrderDetails.OrderID = Orders.OrderID WHERE Orders.OrderDate >= '01/01/2015 12:00 AM' AND Orders.OrderDate <= '01/31/2015 11:59 PM' GROUP BY OrderDetails.ProductCode ORDER BY ) SUBSEL ORDER BY ) PAGESEL ORDER BY

    I appreciate all the help. Thanks!
    Are you trying to limit it to the top 10 items from that report?

  8. #8
    No I wasn't, but I figured it out by looking at some of the other reports and realized maybe it was missing a ORDER BY so I just added that into the code and it worked.

    I was able to get the report, but as it turns out I can't really use the numbers because we have products that are offered as a combo where the customer can get product B at a cheaper price if they purchase product A as well for example. The average price per item is skewed because for some reason V can't register that a product is sold at a discount. It'll count that it's sold at $0...and that's where it screws up the average price.

Similar Threads

  1. Report showing Product Views and Sales
    By PaulH in forum Reporting
    Replies: 7
    Last Post: 04-16-2014, 03:11 PM
  2. Volusion Sales Report
    By GentleBath in forum Reporting
    Replies: 2
    Last Post: 12-08-2012, 12:17 AM
  3. Replies: 10
    Last Post: 10-09-2012, 03:30 PM
  4. Replies: 0
    Last Post: 09-25-2012, 11:38 AM
  5. Sales report by product code/state
    By GGG in forum Reporting
    Replies: 8
    Last Post: 07-03-2012, 02:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •