Customize Query Report with Group Concat Query

[hani.rizqi]

Hi, my name is Rizqi. I’m Indonesian. I used this Erpnext for my business. I love this app very much. But very hard to find resource about this.

Well, I want to custom report for My CLIENT UPDATE. Wich is, i wanna combine the ‘BackOrder Status Report’ with ‘Ordered Item To Be Delivered Report’.

This is how my custom report looks like :

and this is the looks that i want :

and this is my query

select
tabSales Order.name as “Sales Order:Link/Sales Order:120”,
tabSales Order.transaction_date as “SO Date:Date”,
tabSales Order.customer as “Customer:Link/Customer:120”,
tabSales Order.customer_name as “Customer Name::150”,
tabSales Order.po_no as “Customer PO::150”,
ifnull(tabDelivery Note Item.parent, ‘-’) as “Delivery Order:Link/Delivery Note:120”,
tabSales Order Item.item_code as “Item:Link/Item:120”,
tabSales Order Item.description as “Description::200”,
tabSales Order Item.qty as “Ordered Qty:Float:140”,
ifnull(tabDelivery Note Item.qty,0) as “Delivery Qty:Float:140”,
(tabSales Order Item.qty - ifnull(tabSales Order Item.delivered_qty, 0)) as “Qty to Deliver:Float:140”,
IF(tabSales Order Item.qty - ifnull(tabSales Order Item.delivered_qty, 0)=‘0’,‘Completed’, ‘Partial’) as “Status::150”,
tabDelivery Note.posting_date as “Delivery Date:Date:120”,
tabSales Order Item.item_name as “Item Name::150”

from
tabSales Order JOIN tabSales Order Item
LEFT JOIN tabBin ON (tabBin.item_code = tabSales Order Item.item_code
and tabBin.warehouse = tabSales Order Item.warehouse)
LEFT JOIN tabDelivery Note Item ON (tabDelivery Note Item.against_sales_order = tabSales Order.name and tabDelivery Note Item.item_code = tabSales Order Item.item_code and tabDelivery Note Item.warehouse = tabSales Order Item.warehouse )
LEFT JOIN tabDelivery Note ON (tabDelivery Note Item.parent = tabDelivery Note.name)

where
tabSales Order Item.parent = tabSales Order.name
and tabSales Order.docstatus = 1
and tabDelivery Note.docstatus != 2
and tabSales Order.status not in (“Stopped”, “Closed”, “Completed”, “Cancelled”)
order by tabSales Order.name desc, tabDelivery Note Item.item_code desc, tabDelivery Note.posting_date desc

Please help me master

[clarkej]

Assuming you have a data set and have debugged your sql what is your difficulty here?

[mijan1373]

Hi,

I do have the similar requirement. Is there anyone to guide a solution!