Creating Custom Tabulated Reports Using Server Side Scripts

Developers
1

Introduction

Welcome to this comprehensive guide for developers, focusing on the feature: Creating Tabulated Reports Using Server Side Scripts in Empress. This feature is crucial for the development and customization of business solutions, providing unrestricted access via Python scripts for administrators.

Introduction

The creation of tabulated reports using server-side scripts is a powerful feature in Empress, providing developers with the flexibility to generate complex and customizable reports. This feature has significant advantages when creating reports that require advanced calculations or data manipulation that is beyond the capabilities of standard SQL queries.

Prerequisites

Before beginning, ensure that you have Administrator permissions. This is due to the unrestricted access provided by Python scripts used in these reports, making them exclusive to administrators. The script part of the report becomes an integral part of the application repository.

Note: Developer Mode must be active to create these reports.

Step 1: Create a New Report

Firstly, create a new report. Here, set the Report Type as “Script Report” and select “Is Standard” as “Yes”. Choose the module where you want to add this report.

In the module folder (for example, if it is Accounts in ERPnext, the folder will be erpnext/accounts/report/[report-name]), you will see that templates for the report files will be created. The .js and .py files are where you will set filters and write the script for generating the report, respectively.

Step 2: Add Filters

The next step is to add filters in the .js file. Here is an example:

frappe.query_reports["Accounts Receivable"] = {
    "filters": [
        {
            "fieldname":"company",
            "label": __("Company"),
            "fieldtype": "Link",
            "options": "Company",
            "default": frappe.defaults.get_user_default("company")
        },
    ]
}

The properties are set in the same way as you would in a DocField in a DocType.

Step 3: Add the Script

In the .py file, the script for generating the report is added. In the execute method, two lists columns and data are returned. Columns must be a list of dictionaries containing fields like fieldname, label, fieldtype, options, width. Here’s an example:

columns = [{
    "fieldname": "account",
    "label": _("Account"),
    "fieldtype": "Link",
    "options": "Account",
    "width": 300
},
{
    "fieldname": "currency",
    "label": _("Currency"),
    "fieldtype": "Link",
    "options": "Currency",
}]

You can use all server-side modules to build your report. For example, you can look at existing reports like the Balance Sheet for reference.

Step 4: Add link for your report on the module page

Finally, add a link for your report on the module page. In the module folder, you will see labels and items for various sections. The new report can be added in the item list as shown in the example:

def get_data():
    return [{
            "label": _("Accounting Statements"),
            "items": [{
                "type": "report",
                "name": "Balance Sheet",
                "doctype": "GL Entry",
                "is_query_report": True
            }]
        }]

Conclusion

In conclusion, the Creating Tabulated Reports Using Server Side Scripts feature is a powerful tool for developers to generate complex and customizable reports in Empress. Through this guide, we have walked you through the process of creating a new report, adding filters, writing the script, and adding the report to the module page. This feature enhances the development and customization of business solutions, providing unrestricted access via Python scripts.