Query report for filters

ERPNext
1

i have create the simple query report with filter but the filter doent carry any values. it show s blank.
the date range is working but company not showing values
SELECT
si.item_code AS ‘Item Code’,
si.item_name AS ‘Item Name’,
si.qty AS ‘Quantity’,
si.net_amount AS ‘Net Value’,
s.customer AS ‘Customer’,
s.company AS ‘Company’
FROM
tabSales Invoice Item si
JOIN
tabSales Invoice s
ON
si.parent = s.name
WHERE
s.posting_date BETWEEN %(from_date)s AND %(to_date)s
AND s.company = %(company)s;

2

Hi @Rajkumudu_Rk,

Please check the post.

Thank You!

3

I did exactly what you showed. But it didn’t work. I gave customer name directly and it works. When filtering it shows nothing.

yyy
yyy1036×441 7.34 KB

ddd
ddd1252×706 30.7 KB

4

Hi @Rajkumudu_Rk,

Why use a select field type for company?

Please set the Link field type and also add the option “Company” in the company filter.
After then reload and check it.

Thank You!

5

Nothing Change

ccc
ccc1402×738 30.3 KB

zzz
zzz1050×479 7.54 KB

xx
xx1252×698 30.2 KB

6

Hi @Rajkumudu_Rk,

Please check it.

SELECT
    si.item_code AS 'Item Code',
    si.item_name AS 'Item Name',
    si.qty AS 'Quantity',
    si.net_amount AS 'Net Value',
    s.customer AS 'Customer',
    s.company AS 'Company'
FROM 
    `tabSales Invoice` s, 
    `tabSales Invoice Item` si
WHERE
    si.parent = s.name
    AND s.posting_date BETWEEN %(from_date)s AND %(to_date)s
    AND s.company = %(company)s;

Thank You!

1 Like
7

It works
Thank you indeed. I have no words to thank you