Sales by Item in Sales Order

hello, i was trying to do a report that gets you the item name, item code and quantity sold from sales order.
this is my query , but the numbers i’m getting seems wrong to be honest too much high numbers for the qty.
in anyone knowledge will this query works as intended, this is the one i did.

SELECT `tabSales Order Item`.item_code as itemcode,
`tabSales Order Item`.item_name as itemname,
sum(`tabSales Order Item`.qty) as quantity
from `tabSales Order Item`,`tabSales Order`,`tabItem`
where `tabSales Order`.`status` != 'Cancelled' and `tabSales Order Item`.item_name=`tabItem`.item_name and `tabSales Order Item`.creation BETWEEN %(from_date)s and %(to_date)s
GROUP BY `tabItem`.item_name Order BY `tabItem`.item_name)

Hi @AnthonyS,

have you tried

SELECT 
  `tabSales Order Item`.item_code as itemcode,
  `tabSales Order Item`.item_name as itemname,
  sum(`tabSales Order Item`.qty) as quantity
from `tabSales Order Item` /* ,`tabSales Order`,`tabItem` */
left join `tabSales Order` on `tabSales Order`.`name` = `tabSales Order Item`.`parent`
where 
  `tabSales Order`.`docstatus` = 1 
  and `tabSales Order`.`transaction_date` BETWEEN %(from_date)s AND %(to_date)s
GROUP BY `tabSales Order Item`.item_name 
Order BY `tabSales Order Item`.item_name ASC
2 Likes

Thank you for your reply, now i understand what i’ve done wrong. Your query fixed it.
i’ve only changed tabSales Order.docstatus = 1
to
tabSales Order.status != ‘Cancelled’
because i need the draft and the submitted except the cancelled one
i tried tabSales Order.docstatus = 1 or 0 (maybe i wrote it wrong as far as ik , 0=draft, 1=submitted and 2=cancelled.
but it gave me 33 instead of 9 for qty for a particular item which ik it’s only 9
when i used back this one tabSales Order.status != ‘Cancelled’ it returned 9 for qty.

You can query

`tabSales Order`.`docstatus` < 2

this will give you draft and submitted.

1 Like

I created a similar report using the Report Builder

  • Navigate to the Sales Listing
  • Click the Reports → Report Builder item from the left sidebar
  • Click Menu → Toggle Fields
  • Select the fields you want
  • Add filters
  • Click Menu → Save As

Now, you can access the report from your Awesome Bar

2 Likes

will check yours too , it already worked in the other query.
Thank you though for sharing that .