Query report error 'from_date'

Hi
My query

select b.parent as “Voucher No:Data:120”, posting_date as “Date:Data:100”, c.account as “Customer:Data:180”, b.debit as “Advance Amount:Currency:180”,tabSales Invoice Advance.allocated_amount as “Advance Used Amount:Currency:180”, (b.debit - allocated_amount) as “Balance Advance Amount:Currency:180” from tabJournal Voucher , tabJournal Voucher Detail b left outer join tabSales Invoice Advance on tabSales Invoice Advance.parent = b.parent and tabSales Invoice Advance.docstatus = “1” left outer join tabJournal Voucher Detail c on c.parent = b.parent and c.account <> “Cash - SR” where b.parent = tabJournal Voucher.name and tabJournal Voucher.docstatus = “1” and tabJournal Voucher.voucher_type = “Cash Voucher” and b.account = “Cash - SR” and b.is_advance = “Yes” and posting_date >= %(from_date)s and posting_date <= %(to_date)s and b.parent not in (“JV-01052”,“JV-01073”)

When i run the query from_date error is occurs.

Traceback (innermost last):
File “/home/sunrise/bench/apps/frappe/frappe/app.py”, line 51, in application
response = frappe.handler.handle()
File “/home/sunrise/bench/apps/frappe/frappe/handler.py”, line 66, in handle
execute_cmd(cmd)
File “/home/sunrise/bench/apps/frappe/frappe/handler.py”, line 89, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/sunrise/bench/apps/frappe/frappe/init.py”, line 532, in call
return fn(*args, **newargs)
File “/home/sunrise/bench/apps/frappe/frappe/widgets/query_report.py”, line 81, in run
result = [list(t) for t in frappe.db.sql(report.query, filters)]
File “/home/sunrise/bench/apps/frappe/frappe/database.py”, line 114, in sql
self._cursor.execute(query, values)
File “/usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.5-py2.7-linux-i686.egg/MySQLdb/cursors.py”, line 185, in execute
for key, item in args.iteritems())
KeyError: ‘from_date’

Hi @Mohamed_Sajid,

How you are replacing from_date string? Paste your complete code

Thanks, Rohit

select b.parent as “Voucher No:Data:120”, posting_date as “Date:Data:100”, c.account as “Customer:Data:180”, b.debit as “Advance Amount:Currency:180”,tabSales Invoice Advance.allocated_amountas “Advance Used Amount:Currency:180”, (b.debit-allocated_amount) as “Balance Advance Amount:Currency:180” fromtabJournal Voucher,tabJournal Voucher Detailb left outer jointabSales Invoice AdvanceontabSales Invoice Advance.parent= b.parentandtabSales Invoice Advance.docstatus= “1” left outer jointabJournal Voucher Detailc on c.parent= b.parentand c.account<> “Cash - SR” where b.parent =tabJournal Voucher.name andtabJournal Voucher.docstatus= “1” andtabJournal Voucher.voucher_type= “Cash Voucher” and b.account= “Cash - SR” and b.is_advance= “Yes” andposting_date>= %(from_date)s andposting_date<= %(to_date)s and b.parent not in (“JV-01052”,“JV-01073”)

select
tabSales Invoice.employee_name as “Employee Name:Read Only:150”,
tabSales Invoice.customer_name as “Customer:Read Only:150”,
tabSales Invoice.posting_date as “Date:Date:100”,
tabSales Invoice Item.from_time as “From Time”,
tabSales Invoice Item.to_time as “To Time”,
case
when tabSales Invoice Item.to_time > ‘18:00’ then hour(timediff(tabSales Invoice Item.to_time,‘18:00’))
else 0
end as “OT Hour(s):Float:100”,
case
when tabSales Invoice Item.to_time > ‘18:00’ then minute(timediff(tabSales Invoice Item.to_time,‘18:00’))
else 0
end as “OT Minutes(s):Float:100”,
case
when tabSales Invoice Item.to_time > ‘18:00’ then (hour(timediff(tabSales Invoice Item.to_time,‘18:00’)) * 10 )
else 0
end as “OT Amounts:Currency:100”,
tabSales Invoice Item.qty as “Total Hours Worked/Day:Float:100”,
tabSales Invoice Item.amount as “Amount/Day:Currency:100”
from
tabSales Invoice, tabSales Invoice Item
where
tabSales Invoice Item.parent = tabSales Invoice.name and tabSales Invoice Item.to_time > ‘18:00’
and tabSales Invoice.docstatus = “1” and posting_date >= %(from_date)s and posting_date <= %(to_date)s

this my anotherr query but output is same error…‘from_date’…In my old machine this query run successfully…when i take a backup and restore in a new machine ,this type of report error is occurs.

@Mohamed_Sajid
Can you paste the code for your JS file as well?

frappe.query_reports[Over Time Details] = {
“filters”: [
{
“fieldname”:“from_date”,
“label”: __(“From Date”),
“fieldtype”: “Date”,
“width”: “80”,
“default”: frappe.datetime.get_today()
},
{
“fieldname”:“to_date”,
“label”: __(“End Date”),
“fieldtype”: “Date”,
“width”: “80”,
“default”: frappe.datetime.get_today()
}
]
}