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

netmanthan · January 27, 2020, 12:29pm

Hi everyone,

Can anybody guide me to add FROM Date and TO Date in ERPNext Query Report.

smino · January 27, 2020, 6:43pm

The Report Builder can select Date Between . Would that work?

SanRam · January 28, 2020, 4:24am

Add this to report_name.js file

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
}
]
}

ValS · January 28, 2020, 10:09am

SanRam added the client-side filters here, but don’t forget to process this information also server-side in the .py file or else it won’t do anything.

You can for example set an emtpy directory as the variable “filters”

filters = {}

Then you can fill this empty dictionary with the from and to dates like so:

from_date = filters.get('from_date')
to_date = filters.get('to_date')

Now you can use these variables in your report.

SanRam · February 1, 2020, 5:20am

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)

Chinjuprotech · March 2, 2020, 6:30am

Hi

How to fetch the transaction date is Today for query report

Eli · April 29, 2020, 1:51pm

@ValS -

Thank you for highlighting the server side config. I’ve been trying the following but with no success:
`from future import unicode_literals

import frappe
from frappe import _
from frappe.utils import flt, cint, getdate
from frappe.utils import nowdate

def execute(filters=None):
columns, data = [], []
return columns, data
from_date = filters.get(‘from_date’)
to_date = filters.get(‘to_date’)`

I’d appreciate if you could advise where I’m getting it wrong.

Thanks in advance

ValS · April 30, 2020, 7:20am

Is this your full code? Because it seems that you didn’t declare filters as variable first and well, that means you can’t use it in any way.

Eli · April 30, 2020, 7:32am

@ValS

Thanks for the prompt response.

I greatly appreciate it.

Yes- that is my full .py code.

Thanks for pointing out where I went wrong.
Let me try to get it right and if/when I do- I’ll post the working code here.

crafter · April 30, 2020, 8:46am

There is something wrong with that code.

On line 3, you return from the execute function()
return columns, data

This means that lines 4 and 5 will never be executed.

Eli · April 30, 2020, 9:11am

@crafter @ValS - Thank you for the pointers .

I’ve tried the following but can’t seem to find joy. Could you advise where I’m getting it wrong still:

from future import unicode_literals
import frappe
from frappe import msgprint, _
from frappe.utils import getdate, cstr, flt, fmt_money


def execute(filters=None):
	if not filters:
		return [], []	
	if not filters.get("to_date"):
		frappe.throw(_("'To Date' is required"))
	if not filters.get("from_date"):
		frappe.throw(_("'From Date' is required"))

crafter · April 30, 2020, 9:22am

Have a look at my code here for a custom script report :

The file “client_statement.js” contains the filter parameters, which includes a start and end date.

The file “client_statement.py” contains the handling of the dates.
The dates are used within local functions, but you can see they are referred to by the notation as in the following example code

from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)

Eli · April 30, 2020, 9:29am

@crafter

Thank you very much. I’ll check it out right now

Eli · April 30, 2020, 10:06am

@crafter - May you grant me some grace today but I have tried the below and still no success .
May you advise what I’m mssing?

from future import unicode_literals
import frappe
from frappe import msgprint, _
from frappe.utils import getdate, cstr, flt, fmt_money


def execute(filters=None):

	if not filters:
		return [], []	
	

		

def get_conditions(filters):
	conditions = []
	from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)

ValS · April 30, 2020, 11:52am

Do you have any errors when you run this code?
What does the console return, if you print filters?

If it returns nothing, then if not filters will always be true, meaning your script ends at this point, if I’m not mistaken. I’m afraid I can’t help much more beyond that

Eli · April 30, 2020, 12:52pm

@ValS

Thank you for replying.

The console gives the message below.

Uncaught SyntaxError: Invalid or unexpected token
    at Object.eval (dom.js:33)
    at query_report.js:172
query_report.js:177 Uncaught (in promise) TypeError: Cannot set property 'html_format' of undefined
    at query_report.js:177
    at request.js:381
    at new Promise (<anonymous>)
    at Object.frappe.after_ajax (request.js:374)
    at query_report.js:175

Eli · May 1, 2020, 11:54am

@crafter - any ideas?

Eli · May 12, 2020, 11:07am

@ValS

I don’t suppose you have any other pointers?

marination · May 12, 2020, 7:13pm

Can you post your whole .py code AND .js code. Declare the filters in .js just like @SanRam suggested . Right after declaring it in js you should be able to see the filters on your report page after refreshing it. You can then move on to retrieving and manipulating data via server side.

ValS · May 13, 2020, 12:57pm

I’m sorry, I can’t be of any more help right now.