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

ERPNext
21

@ValS

Noted with thanks.

22

@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:

Thanks in advance.

23

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

24

@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

25

@marination

:wave:

26

@clarkej

This issue is still unresolved.

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

1 Like
27

@marination

Just bumping this up in case you missed it.

28 
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

29

@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!

30

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’ :slight_smile:

Screenshot%20from%202020-06-11%2016-33-16
Screenshot%20from%202020-06-11%2016-33-161247×897 52.1 KB

31

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:

image
image2134×858 49.1 KB

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

image
image1088×874 48.2 KB

However when I include the query in the SQL command I get following error message:
image
image1220×416 17.1 KB

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!

image
image2116×1126 111 KB

32

hi @bluesky, i have this:

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

and it works.

1 Like
33

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

34

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?

35

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

36

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

37

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)

1 Like
38

@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.

1 Like
39

will try and let you know

40

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