Report result in REST API

ERPNext
1

Hi,
Can we get response of a report (query report/script report/report builder) by using REST API?

2

Can this be done?

3

Possible now?

4

In stock_balance.py, add @frappe.whitelist() above execute method.
image

And then try by calling the below endpoint by passing filters like start_date & end_date: http://your_domain/api/method/erpnext.stock.report.stock_balance.stock_balance.execute.

DISCLAIMER:

  1. Not sure if we can use @frappe.whitelist() as it turns on the guest access.
  2. I didn’t try on this stock_balance.py instead created another custom_file.py file in some custom module (my_custom_module) and imported this execute method in that file and then made a call something like below.
    http://my_domain/api/method/my_custom_module.custom_file.get_stock_balance.

In get_stock_balance method, I’ve made a call to actual execute method.

1 Like
5
  1. Not sure if we can use @frappe.whitelist() as it turns on the guest access.

It doesn’t unless you set allow_guest to true.

You can use this: frappe/frappe/desk/query_report.py at fe82487c15d15ceae4db2813a5749a84456b8d86 · frappe/frappe · GitHub

This isn’t officially part of “REST API” but it’s unlikely to break in near future. IMO this should be part of API, can you create a feature request on github?

1 Like
6

Hi all. I found a way to do it by watching the payloads when opening a report in the browser. The URL to use is:

http[s]://your_site_name/api/method/frappe.desk.query_report.run?report_name=name+of+report&filters={"filter_one":"filter+value"}
4 Likes
7

Hi I am trying to fetch data from Account Receivables Summary report using postman.

I am getting output as follows:

{
“message”: {
“prepared_report”: true,
“doc”: null,
“add_total_row”: true
}
}

I want to fetch actual data of the report.

1 Like
8

Hi, did you ever find a way to fetch “result”? I am running into the same problem with Stock Balance now. It only returns { "message": { "prepared_report": true, "doc": null, "add_total_row": true } }.

9

please pass parameter ignore_prepared_report=True to the method frappe.desk.query_report.run.

image

2 Likes
10

@szufisher that worked! Thanks so much. For those wondering, it should look like this (per the example from @archais), with &ignore_prepared_report=True at the end:

http[s]://your_site_name/api/method/frappe.desk.query_report.run?report_name=name+of+report&filters={"filter_one":"filter+value"}&ignore_prepared_report=True
4 Likes
11

Hi!!

I’m able to pull the report. Thanks a lot!! But filtering is not working

Can you provide an actual example? I’m unable to figure out the syntax for filtering

I tired-
https:// SITE NAME /api/method/frappe.desk.query_report.run?report_name=Sales%20Invoice&filters={“Posting_Date”:“2023-03-01”}&ignore_prepared_report=True

I’m getting the full report. Filtering Didnt work!!

Thanks

12

Normally the filter field name is in lower case,eg posting_date, for details check the report filter definition js file.

13

Here is an example:

/method/frappe.desk.query_report.run?report_name=Sales+Analytics&filters={"tree_type":"Territory","doc_type":"Sales Invoice","value_quantity":"Value","from_date":"2022-01-01","to_date":"2022-12-31","company":"RANDOM COMPANY","range":"Monthly"}

Let me know if this helps!

1 Like
14

Apparently the ignore_prepared_report parameter didn’t get included! See here:

/method/frappe.desk.query_report.run?report_name=Sales+Analytics&filters={"tree_type":"Territory","doc_type":"Sales Invoice","value_quantity":"Value","from_date":"2022-01-01","to_date":"2022-12-31","company":"RANDOM COMPANY","range":"Monthly"}&ignore_prepared_report=True
1 Like
15

Hi
I’m trying to get report Debtors Outstanding and Provisioning through api and when I’m expecting the command I’m getting response as
{“message”:{“prepared_report”:true, “doc”:null, “add_total_row”:0}}
How to get actual report data?
Please help me with this