Script Report
Script Report
Anything that can't be achieved using Report Builder or Query Report can be achieved using Script Reports. As the name suggests, these reports are built using Python scripts. Since these reports give you unrestricted access via Python scripts, they can only be created by Administrators. These reports are meant to be written during development and be a part of your app.
To create a Script Report, type "new report" in the awesomebar and hit enter.
- Set Report Type as "Script Report"
- Set "Is Standard" as "Yes"
- Select the Module in which you want to add this report
- In the module folder (for example if it is Accounts in OneHash the folder will be
onehash/accounts/report/[report-name]
) you will see that templates for the report files will be created. - Write your python script in the generated
{report-name}.py
file. - You can add filters to your report by adding them to
{report-name}.js
Standard and Custom Reports
Verson 12 onwards, you can make custom Query and Script reports in Frappe Framework. In custom reports, the script can be added directy in the Report itself and you can use the Script API functions of Frappe Framework.
Columns and Filters
You can configure the columns and filters in the Report document. Here you can set the label, width, format (fieldtype) for the columns and filters.
Filters can be used as formatting variables in the query. For example a filters of type customer
can be used as %(customer)s
in the query.
Writing the script
Custom Report
In custom reports, you can use the Script API and write the script directly in the Code section.
return frappe.db.get_all('User', ['first_name', 'last_name'], filters = filters)
Standard Report
The generated .py
file comes with a boilerplate for your report. There is one method named execute
which takes filters
and returns columns
and data
. You can use any combination of python modules and SQL queries to generate your report. The execute
function looks like this
__future__ import unicode_literals
# import frappe
def execute(filters=None):
columns, data = [], []
return columns, data
The execute
function is supposed to return the columns
and the data
to be shown in the report by default. A developer can optionally return a few parameters like message, chart, report_summary, skip_total_rows
.
The following are the parameters that can be returned by the execute function
columns
This is a list of dictionaries. This holds all the columns that are to be displayed in the datatable in an order.
Example:
= [
{
'fieldname': 'account',
'label': _('Account'),
'fieldtype': 'Link',
'options': 'Account'
},
{
'fieldname': 'currency',
'label': _('Currency'),
'fieldtype': 'Link',
'options': 'Currency'
},
{
'fieldname': 'balance',
'label': _('Balance'),
'fieldtype': 'Currency',
'options': 'currency'
}
]
Results
This can be a list of lists or a list of dictionaries. This holds the data to be displayed in the report
Example:
= [
{
'account': 'Application of Funds (Assets)',
'currency': 'INR',
'balance': '15182212.738'
},
{
'account': 'Current Assets - GTPL',
'currency': 'INR',
'balance': '17117932.738'
},
...
]
Chart
Contains the configuration for the default chart to be shown in the report.
Report Summary
This is a list of dictionaries that stores the important values in the report and is shown separately in the top section on the UI.
Example:
[{
"value": profit,
"indicator": "Green" if profit > 0 else "Red",
"label": _("Total Profit This Year"),
"datatype": "Currency",
"currency": "INR"
}]
Here is a script report from OneHash: Balance Sheet
Adding filters
To add filters in your report define the fields and their fieldtypes in the {report-name}.js
file. The filter values will be available in the execute
method as a dict.
.query_reports['Balance Sheet'] = {
filters: [
{
fieldname: 'company',
label: __('Company'),
fieldtype: 'Link',
options: 'Company',
default: frappe.defaults.get_user_default('company')
},
{
fieldname: 'periodicity',
label: __('Periodicity'),
fieldtype: 'Select',
options: [
'Monthly',
'Quarterly',
'Half-Yearly',
'Yearly'
],
default: 'Yearly',
depends_on: 'eval:doc.company=="Gadget Technologies Pvt. Ltd."'
}
]
}
Similar to the depends_on
property that controls the display of fields, in Version 13 we have introduced depends_on
for Script Report filters. This can be used to determine whether the filter will be visible based on the value of the condition in depends_on
. Contains the configuration for the default chart to be shown in the report.
Updated on: 21/03/2023
Thank you!