I want to group the sales team reporting to their respecctive managers and also show their sales details.I have written the below query report and js filters. But the filter is not working.Please help.
#Below is the Query report
SELECT DISTINCT
SO.sales_person_p AS 'Sales Person:Data:120',
SO.name AS 'Sales Order:Link/Sales Order:120',
SO.company AS 'Company:Data:200',
SO.customer AS 'Customer:Link/Customer:120'
FROM
`tabSales Order` AS SO
LEFT JOIN `tabEmployee` as E on E.name = SO.sales_person_p
WHERE
SO.status NOT REGEXP 'cancelled|draft'
AND
E.reports_to REGEXP %(employee)s
#Below is the js filters
frappe.query_reports["Reports For Sales Manager Based On Team"] = {
"filters": [
{
'label': __("Reports To"),
'fieldname': 'employee',
'fieldtype': 'Link',
'options': 'Employee',
'default': 'Select Reporting Manager',
'reqd': 1
}
]
}