Script Reports

Script Reports are the custom reports written in Python. While query reports are great for querying the database and show reports, scripts reports allow you to query the datbase and then process the data.

1. Define Report Columns

This is a list of dictionaries. This holds all the columns that are to be displayed in the datatable in an order. You can configure the columns in the Report document by setting 'fieldname', 'label', 'fieldtype', 'options', 'width'.

column = [
    {
    'fieldname': 'first_name',
    'label': _('First Name'),
    'fieldtype': 'Data',
    'width': 120
    },
    {
    'field_name': 'date_of_joining',
    'label': _('Date Of Joining'),
    'fieldtype': 'Date',
    'width': 150
    },
    {
    'fieldname': 'gender',
    'label': _('Gender'),
    'fieldtype': 'Link',
    'options': 'Gender',
    'width': 120
    }
]

2. Prepare Report Content

It is a list of lists or a list of dictionaries. It holds the data to be displayed in the report.

Example 1 : Passing raw data (external data) to results.

result = [['John Doe','2020-12-4','Male'],['Jake Bob','2010-10-30','Female']]

Example 2: Fetching internal database via SQL query. Result will hold values of executed query.

result = frappe.db.sql("SELECT first_name,date_of_joining,gender from `tabEmployee`")

Example 3: Passing SQL query as a string .

query = f'''
SELECT first_name,date_of_joining,gender  FROM `tabEmployee` '''
result = frappe.db.sql(f"{query}", as_dict=True)

3. Return columns and content.

Finally assign column and content to 'data' as shown below.

data = column,result

Note: Send output via 'data' as shown above. Do not use a different name instead of 'data'.

EmpDetails