How to add FROM Date and TO Date in ERPNext Query Report

[Eli]

@marination

Thank you for the assistance.

Here is the .py:
How to add FROM Date and TO Date in ERPNext Query Report - #14 by Eli

And the .js is identical to what is mentioned here:

How to add FROM Date and TO Date in ERPNext Query Report

report_name.js: frappe.query_reports[“Report Name”] = { “filters”: [ { “fieldname”: “from_date”, “label”: __(“In Date From”), “fieldtype”: “Date”, “default”: frappe.datetime.get_today(), “reqd”: 1 }, { “fieldname”: “to_date”, “label”: __(“To”), “fieldtype”: “Date”, “default”: frappe.datetime.get_today(), “reqd”: 1 } ] } then query report: SELECT ‘tabTable Name’.‘field_name’ FROM ‘tabTable Name’ WHERE (DATE(‘tabTable Name’.‘date_field’) between %(from_date)s and %(to_date)s) …

Thanks in advance.

[marination]

@Eli

Cannot set property ‘html_format’ of undefine

If this still is the issue, there could be a missing comma in your .js file , which is why i asked if i could see your .js. I understand that it is similar to the one above, but clearly there is a minor error. It should work fine otherwise. Also what do you see on your screen ? Does it go blank? Its hard to debug without knowing what is going on here

[Eli]

@marination

Here is the .js

frappe.query_reports["sales report"] = {
	"filters": [
	{
		"fieldname":"from_date",
		"label": __("From Date"),
		"fieldtype": "Date",
		"width": "80",
		"reqd": 1,
		"default": frappe.datetime.month_start()
	},
	{
		"fieldname":"to_date",
		"label": __("To Date"),
		"fieldtype": "Date",
		"reqd": 1,
		"width": "80",
		"default": frappe.datetime.month_end()
	}

	]
}

Thanks in advance

[Eli]

@marination

[Eli]

@clarkej

This issue is still unresolved.

The marked solution was in response to a request for more data

[Eli]

@marination

Just bumping this up in case you missed it.

[clarkej]

@Eli

Uncaught SyntaxError: Invalid or unexpected token

frappe.query_reports["sales report"] = {
	"filters": [
	{
		"fieldname":"from_date",
		"label": __("From Date"),
		"fieldtype": "Date",
		"width": "80",
		"reqd": 1,
		"default": frappe.datetime.month_start()
	},
	{
		"fieldname":"to_date",
		"label": __("To Date"),
		"fieldtype": "Date",
		"reqd": 1,
		"width": "80",
		"default": frappe.datetime.month_end()
	}

	]
}

Maybe terminate that expression with ‘;’

An online Javascript validator complains that is missing!

For example

frappe/erpnext/blob/develop/erpnext/projects/report/project_summary/project_summary.js#L42

[Eli]

@clarkej

Thank you very much for following up.
I am so ashamed of the mistake I had been making that wasted everyone’s time.

I only realized yesterday that the report’s name value is case sensitive!

Instead of
frappe.query_reports["sales report"]

is should have been

frappe.query_reports["Sales Report"]

After making this small change, everything worked as advertised!

[clarkej]

On the contrary I disagree @Eli - mistakes are human and inevitable and learning is never wasted time - you also have informed me and many more too.

Yet another discovery and bug prospect for me just now as a result:

A DocType name must be capitalised - ERPNext forces that rule by simply capitalising name violations when it saves and validates a new DocType.

However for a new Query Report, ERPNext does not apply that same rule in that case too?

And that can mean confusion as you have found.

Attached is my test case Query Report named ‘dummy doctype query report’

[bluesky]

Hi, I am trying to implement a similar thing in a query report. The SQL query works fine (except the WHERE Date >= %(from_date)s and Date <= %(to_date)s part). I searched the entire documentation/forum but couldn’t get a grasp how the query report actually works if hosted on frappe.cloud. On V13, the filters can be added above the SQL query:

This works and I can see the filter boxes above the report:

However when I include the query in the SQL command I get following error message:

As said the docs are quite confusing and it isn’t clear whether this is possible at all if hosted on Frappe Cloud. I can add a script below the query box, but how/if this works on Frappe Cloud.
Any suggestion would be really helpful!

[landaverdelbo]

hi @bluesky, i have this:

and ( date_format(inv.creation, “%%Y-%%m-%%d”) between %(fecha_inicial)s and %(fecha_final)s )

---

and it works.

[bluesky]

@landaverdelbo Thanks for your help! I could fix it now and it work.

[madurajith_Perera]

How did you fix this? I have the same error.
in a script report, some at the last query then,
AND p.posting_date >%(start_date)s
SO I want to filter the records according to the user select date.
this doesn’t working. Can someone help pls?

[madurajith_Perera]

I asked the question here few days ago, no one care to help me yet

Getting an error in script report as not all arguments converted during string formatting

I am new to ERPNext. I am trying to get a filter into my report script. code as below def print_something(filters): start= filters.get(“start_date”) print(“Start” , start) conditions=get_conditions(filters) query=frappe.db.sql(“”“select (sum(debit_in_account_currency)-sum(credit_in_account_currency)) AS Openingbalance from tabGL Entry where account = ‘SAMP’ “”” % conditions) return query def get_conditions(filters): conditions = “” if filters.get(“start_date”): conditions += “posti…

[Rehan_Ansari]

can you share the code i want to also pass the parameters from js to py file

[madurajith_Perera]

Hi Rehan,

I figured it out. To help you out here how it worked for me.
in js file suppose you have a filter setup for the date

“filters”: [
{
“fieldname”: “end_date”,
“label”: __(“End Date”),
“fieldtype”: “Date”,
“default”: frappe.datetime.get_today()
}
]

In the script(py file, you can call this with %s,
for an example
AND posting_date <= ‘%s’)) “”" % (filters.end_date)

[EugeneP]

@madurajith_Perera and @Rehan_Ansari

Also have a look at [Tutorial] Script Report / Chart
where I use both a From and To date filter along with code to make sure the dates are acceptable as part of a Script Report.

[Rehan_Ansari]

will try and let you know

[Elan_K]

Mark it as a Mandate on the Filter to avoid executing the SQL Query before both date parameters are passed.

[falah123]

How to Add Date flter in Script Report

I want date filter in my script report like if I select date fromdate To todate then I want data between that. [image] I’ve tested a lot of methods but couldn’t find any. Here is my script columns = [ { "label": _("Supplier"), "fieldname": "supplier", "fieldtype": "Data", "width": 200, }, { "label": _("Total Quantity"), "fieldname": "total_qty", "fieldtype": "Int", "width": 150, }, { "label": _("Received Quantity"), "fieldname": "receiv…

Please help me In this.