Column wise date range in query report

ERPNext Reports
1

actually I make a month-wise query report but didn’t set report …

SELECT `tabSales Invoice Item`.`item_code` as `item::150`,

SUM(qty) AS Total QTY::100,
SUM(amount) - posting_date between “2020-04-01” and “2020-04-30” AS APR SALES:Float:100,

SUM(amount WHEN posting_date >= ‘20200401’ AND posting_date < ‘20200430’ THEN 1 END) AS April:float:100,

SUM(amount) AS MAY SALES:Float:100,
SUM(amount) AS JUN SALES:Float:100,
SUM(amount) AS JUL SALES:Float:100,
SUM(amount) AS AUG SALES:Float:100,
SUM(amount) AS SEP SALES:Float:100,
SUM(amount) AS OCT SALES:Float:100,
SUM(amount) AS NOV SALES:Float:100,
SUM(amount) AS DEC SALES:Float:100,
SUM(amount) AS JAN SALES:Float:100,
SUM(amount) AS FEB SALES:Float:100,
SUM(amount) AS MAR SALES:Float:100,
SUM(amount) AS Total Amount:Float:100

FROM tabSales Invoice Item,tabSales Invoice

where
tabSales Invoice Item.parent = tabSales Invoice.name
and tabSales Invoice.docstatus = 1

	Group by `item_code`

thanks.

2

@NCP , have you achieved this?

3

yes, @Suresh_Thakor,

Please check it.

4

Thanks @NCP for the reply. but this is static.

I am looking for help to get the planned_qty to be sum in the month-wise column. please find the code and screenshot.

image
image956×299 16.8 KB 

def get_columns(filters):
	columns = [{"label": _("Item"), "fieldname": "item_code", "fieldtype": "Data", "width": 140}]
	ranges = get_period_date_ranges(filters)
	for dummy, end_date in ranges:
		period = get_period(end_date, filters)
		columns.append({"label": _(period), "fieldname": scrub(period), "fieldtype": "Float", "width": 120})
	# frappe.msgprint("<span style='color:Red;'>" + "<pre>{}</pre>".format(frappe.as_json(columns)))
	return columns

def get_data(filters):
	# ------get the tabale data in data list
	data, entry = [], []
	pp = frappe.qb.DocType("Production Plan")
	ppi = frappe.qb.DocType("Production Plan Item")
	entry = ( frappe.qb.from_(pp)
            .left_join(ppi)
            .on(pp.name == ppi.parent)
            .select(ppi.item_code)
			.select(ppi.planned_qty)
			.where(pp.docstatus == '1')
            .run(as_dict=True) )
	for dt in entry:
		data.append(dt)
	frappe.msgprint("<span style='color:Red;'>" + "<pre>{}</pre>".format(frappe.as_json(data)))
	return data```