Try ERPNext Buy Support Partners Foundation Foundation Members

How I created this Sales KPI Dashboard

Try and add

GROUP BY `tabItem`.`default_warehouse`, `tabItem`.`item_code`

Below is the Script i used. I finally got the right table i was looking for.

SELECT tabBin.item_code AS item_code, tabItem.item_group, tabBin.warehouse AS warehouse, tabBin.stock_uom AS UOM, sum(tabBin.actual_qty) AS OH Qty, sum(tabBin.valuation_rate) AS Valuation_Rate, sum(tabBin.stock_value) AS Stock_Value
FROM tabBin
LEFT JOIN tabItem ON tabBin.item_code = tabItem.item_code
WHERE tabBin.item_code IS NOT NULL AND {{Ware}} AND {{Catego}}
GROUP BY tabBin.warehouse, tabBin.item_code
ORDER BY tabBin.item_code ASC

The above script gives me the screenshot below.

I was able to add filter for Warehouse and Category which give me exactly what i wanted. Thanks to @olamide_shodunke

Thank u so much for this post…@riyas

Has anyone run into an “Server error encountered” error before? I’m hosting both Metabase and ERPNext on Google Cloud and have the firewall ports open.

Hi ,
Enter the Port Number 3306.I could figure out from this image its not entered and could see only shadow default.So enter the port number 3306 and try again

po

@Malcolm_Wilson

If from the same cloud machine, use the 127.0.0.1 instead

I was able to get the connection working to the database. Has anyone run into this SQL error when trying to create a query? I setup the database to be mysql so I’m not sure why it’s saying there’s a syntax error.

Try

SELECT sum(grand_total) AS sum
FROM `tabSales Invoice`
WHERE docstatus = 1

Observe how the table name has been written in between ` character.

Thank you for the help!

How to write the quries for Ontime deliveries,late deliveries,number of recipients,Recipients affected by Late Deliveries,Actual Delivery Duration,Expected Delivery Duration and Actual vs Expected (days) in ERPNext database
can anyone helps me