Custom query report

How can I add fields from child table.
Ex: I have created query report from BOM, how can I add items from “Items table”?

1 Like

Hi,

Can anyone help me for above query.

Regards
Santosh

Can you please elaborate what exactly you are trying to do?

You can take a look at other Query Reports in ERPNext as example.

Thanks for ur reply.

I have created query report from Purchase Receipt. I need to add columns from child table as shown in picture.

Purchase receipt


Purchase Receipt report

Pick the columns you need and Update, you should see data in the report. Screenshot below.

Thank you KanchanChauhan,

We cannot pick and drag columns in query reports.
We appreciate for further suggestions.

Regards
Santosh

Sorry missed that it is a query report, Can you share the query please.

Kindly refer below images

KanchanChauhan,
I can add the query for fields which are directly on document, but can’t fetch the fields(data) from child table like in above picture.

You can fetch the data from Purchase Reciept Item Table

Can you please modify the above query.

Thank you all, it works with below query.

SELECT
tabPurchase Receipt.posting_date as “Date:Date/Date:120”,
tabPurchase Receipt.name as “ID:Link/Purchase Receipt:100”,
tabPurchase Receipt Item.item_code as “Item Code:Data/Data:120”,
tabPurchase Receipt Item.qty as “Qty”,
tabPurchase Receipt Item.rate as “Rate:Currency/Currency:70”,
tabPurchase Receipt Item.amount as “Amount:Currency/Currency:110”,
tabPurchase Receipt.total as “Total Amount:Currency/total:110”,
tabPurchase Receipt.per_billed as “Billed:Percent/Purchase Receipt:50”,
tabPurchase Receipt.amount_billed as “Amount Billed:Currency/Purchase Receipt:100”,
tabPurchase Receipt.amount_to_be_billed as “Amt To Be Billed:Currency/Purchase Receipt:100”,
tabPurchase Receipt.discount_amount as “Discount:Currency/currency:90”,
tabPurchase Receipt.grand_total as “Grand Total:Currency/currency:100”,
tabPurchase Receipt.is_return as “Return:Check/Purchase Receipt:50”,
tabPurchase Receipt.status as “Status:Select/Purchase Receipt:100”,
tabPurchase Receipt.supplier_invoice_no as “Supplier Inv No:Data/Purchase receipt:100”

FROM
tabPurchase Receipt Item,
tabPurchase Receipt
WHERE
tabPurchase Receipt.docstatus=1 && tabPurchase Receipt Item.parent=tabPurchase Receipt.name

1 Like