Script report filters

[kinzang]

i user script report in appraisal, but its not working. i have branch filter field, employee filter field and start and end date filter. but know of them are working

i use below js script:

frappe.query_reports["Appraisal Report"] = {
	"filters": [
	{
		"fieldname": "branch",
		"label":__("Branch Name"),
		"fieldtype":"Link",
		"options":"Branch",

	},

	{	
			"fieldname":"employee_name",
			"label": __("Employee Name"),
			"fieldtype": "Link",
			"options": "Employee",

			"get_query" : function(){
				var branch = frappe.query_report_filters_by_name.branch.get_value();
				return{
					"doctype": "Appraisal",
					"filters":{
						"branch":branch,
					}
				}
			}
		},

	{
		"fieldname":"start_date",
		"label": __("From Date"),
		"fieldtype": "Date",
		"reqd": 1,
		"default": frappe.datetime.year_start()
	},
	{
			"fieldname":"end_date",
			"label": __("To Date"),
			"fieldtype": "Date",
			"reqd": 1,
			"default": frappe.datetime.year_end()
		}, 

in firstly i have try to filter employee name, but its not working.

please help me

[Maheshwari_Bhavesh]

share your python code …

[kinzang]

def execute(filters=None):
columns = get_columns(filters)
data = get_data(filters)

return columns, data

def get_columns(filters):
return[
(“Appraisal Type”),
(“From Date”)+ “:Data:90”,
(“To Date”)+ “:Data:90”,
(“Employee”) + “:Link/Employee:100”,
(“Employee Name”) + “:Data:150”,
(“Designation”) + “:Data:160”,
(“Grade”) + “:Data:50”,
(“Branch”) + “:Data:150”,
(“Supervisor Name”)+“:Data:150”,
(“Total Score”),
(“Status”)+ “:Data:80”
]

def get_data(filters):
query = “select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal”
return frappe.db.sql(query)

if filters.get("employee"):
	data = "select employee from tabAppraisal"

return frappe.db.sql(data)

[kinzang]

@Maheshwari_Bhavesh,

can you suggestion python code. i don’t have much idea about python.

[Maheshwari_Bhavesh]

def execute(filters=None):
	columns = get_columns(filters)
	data = get_data(filters)
	return columns, data

def get_columns(filters):
	return[
		(“Appraisal Type”),
		(“From Date”)+ “:Data:90”,
		(“To Date”)+ “:Data:90”,
		(“Employee”) + “:Link/Employee:100”,
		(“Employee Name”) + “:Data:150”,
		(“Designation”) + “:Data:160”,
		(“Grade”) + “:Data:50”,
		(“Branch”) + “:Data:150”,
		(“Supervisor Name”)+":Data:150",
		(“Total Score”),
		(“Status”)+ “:Data:80”
		]

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal""" % conditions)

def get_conditions(filters):
	conditions = ""
	if filters.get("employee"): conditions += "employee = %(employee_name)s"
	return conditions

[kinzang]

thanks @Maheshwari_Bhavesh have done same like yours code but its giving error. error are screen short below

[Maheshwari_Bhavesh]

Try This

def execute(filters=None):
	columns = get_columns(filters)
	data = get_data(filters)
	return columns, data

def get_columns(filters):
	return[
		(“Appraisal Type”),
		(“From Date”)+ “:Data:90”,
		(“To Date”)+ “:Data:90”,
		(“Employee”) + “:Link/Employee:100”,
		(“Employee Name”) + “:Data:150”,
		(“Designation”) + “:Data:160”,
		(“Grade”) + “:Data:50”,
		(“Branch”) + “:Data:150”,
		(“Supervisor Name”)+":Data:150",
		(“Total Score”),
		(“Status”)+ “:Data:80”
		]

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s""" % conditions)
        return query

def get_conditions(filters):
	conditions = ""
	if filters.get("employee"): conditions += "employee = %(employee_name)s"
	return conditions

[kinzang]

its giving same error:
File “/home/erp/erpnext/apps/erpnext/erpnext/hr/report/appraisal_report/appraisal_report.py”, line 36, in get_data
query=frappe.db.sql(“”“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal”“” % conditions)
TypeError: not all arguments converted during string formatting

[shreya115]

@Maheshwari_Bhavesh

def get_data(filters): conditions=get_conditions(filters) query=frappe.db.sql(“”“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal”“” % conditions) return query

It should be

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s""" % conditions, filters)
    return query

[kinzang]

@shreya115, its giving same error;

my code:

def get_data(filters):
conditions=get_conditions(filters)
query=frappe.db.sql(“”“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s”“” % conditions)
return query

def get_conditions(filters):
conditions = “”
if filters.get(“employee”): conditions += “employee = %(employee_name)s”

	return conditions, filters

[Maheshwari_Bhavesh]

def get_data(filters):
conditions=get_conditions(filters)
query=frappe.db.sql(""“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s”"" % conditions)
return query

def get_conditions(filters):
conditions = “”
if filters.get(“employee”): conditions += “employee = %(employee_name)s”

	return conditions

[shreya115]

My bad, this will work.

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s""" % conditions, filters)
    return query

[kinzang]

@shreya115, but its not filtering any thing, when i enter branch or employee name, date between, it should filter. but its not filtering. please help me.

i have report page:

[shreya115]

Why don’t you go through some already created script reports and try?

You can refer this:
https://github.com/frappe/erpnext/blob/develop/erpnext/hr/report/employee_advance_summary/employee_advance_summary.py

[kinzang]

thanks @shreya115, i will try

[kinzang]

@shreya115,

i tried lot but fail,

my code:

{
def execute(filters=None):
columns = get_columns(filters)
data = get_data(filters)

return columns, data

def get_columns(filters):
return[
(“Appraisal Type”),
(“From Date”)+ “:Data:90”,
(“To Date”)+ “:Data:90”,
(“Employee”) + “:Link/Employee:100”,
(“Employee Name”) + “:Data:150”,
(“Designation”) + “:Data:160”,
(“Grade”) + “:Data:50”,
(“Branch”) + “:Data:150”,
(“Supervisor Name”)+“:Data:150”,
(“Total Score”),
(“Status”)+ “:Data:80”
]

def get_data(filters):
conditions=get_conditions(filters)
query=frappe.db.sql(“”“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal wheere %s”“” % conditions, filters)
return query
employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s"“” % conditions, filters)

def get_conditions(filters):
conditions = “”

	if filters.get("employee"): 
			conditions += " and employee = %(employee)s"

	if filters.get("branch"):
			conditions += " and branch = %(branch)s"

	if filters.get("status"):
			conditions += " and status = %(status)s"	

	return conditions

but its giving error:

ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘and status = ‘Draft’’ at line 1”)

please help me…

[shreya115]

@kinzang

query=frappe.db.sql(“”“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal wheere %s”“” % conditions, filters)
return query
employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s"“” % conditions, filters)

Why are you writing the query twice? Also, check the where condition, there’s a typo and modify it to something like where docstatus != 2 %s

[Rehan_Ansari]

did you find the solution?

[daeng_agiel]

How to filter data in link field on script report filter field?

Create your filters in the client code, not in the filter table. Add the script below: frappe.query_reports['report_name_here'] = { "filters": [ { fieldname: "fieldname_here", label: "label_here", fieldtype: "Link", options: "doctype_of_link_here", filters: { "fieldname_in_link_doctype_here": ["operator_here", "valuefield_here"] }, }, ] } Make sure your reload page report before rev…