Custom API Rows in Financial Report Templates

A Financial Report Template row with Data Source set to Custom API retrieves values directly from a server-side Python function instead of using account filters or calculated formulas.

This feature is supported in:

• Balance Sheet
• Profit and Loss Statement
• Cash Flow
• Custom Financial Statement

Custom API rows allow organizations to include data from custom business logic, budgets, external systems, or advanced calculations directly within financial reports.

1. When to Use Custom API Rows

Use a Custom API row when the required report value cannot be generated using standard account filters or formula calculations.

Common use cases include:

• Budget variance calculations
• Custom KPIs
• External data integrations
• Complex business-specific calculations
• Aggregated data from custom DocTypes

If the returned value needs to be combined with other report rows, create another row using Calculated Amount and reference the API row using its Line Reference.

Custom API rows are ideal when report values depend on logic that extends beyond standard accounting data.

2. How to Configure a Custom API Row

Follow these steps:

1. Open the Financial Report Template.
2. Add a new row in the Report Line Items table.
3. Set Data Source to Custom API.
4. In the Formula or Account Filter field, enter the Python method path.
5. Use a dotted path format such as:
   my_app.financial_report_api.get_budget_variance
6. Optionally define a Line Reference if the result will be used in later calculations.
7. Configure formatting options such as Reverse Sign, Hide If Zero, Include in Charts, or Value Type if required.
8. Save the template.

The API path must point to a valid module-level Python function inside an installed ERPNext app.

3. How ERPNext Executes the Function

When generating the report, ERPNext automatically calls the configured Python function and passes the following parameters:

• filters – Current report filters as a dictionary-like object
• periods – Generated reporting periods
• row – Current Financial Report Row document

Each period object contains information such as:

• from_date
• to_date
• key
• label

Your function can accept all parameters or only the ones required.

4. Example Custom API Function

The following example returns a value for each reporting period:

from frappe.utils import flt

def get_budget_variance(filters=None, periods=None, row=None):
    filters = filters or {}
    periods = periods or []

    values = []

    for period in periods:
        variance = 0.0
        values.append(flt(variance))

    return values

Replace the placeholder logic with your own database queries, calculations, or business rules.

5. Return Value Requirements

Every Custom API function must follow these rules:

• Return a list of numeric values.
• Return exactly one value for each reporting period.
• Maintain the same order as the periods supplied by ERPNext.

For example:

• If the report contains 12 monthly periods, the function must return 12 values.
• If the report contains 4 quarterly periods, the function must return 4 values.

If the row contains a Line Reference, it can be used later in formula rows:

API001 + SALES001

6. Important Notes

• The API path must follow the format app.module.method.
• The target function must exist within an installed application.
• @frappe.whitelist() is not required unless the function is also exposed through the API.
• Server Scripts are currently not supported.
• Custom API rows are processed before account-based and calculated rows.
• Custom API rows should not depend on values generated by other report rows.
• If an error occurs, ERPNext logs the exception and automatically fills the row with zero values.

Since Custom API rows execute first, they should generate independent values and should not rely on other report calculations.

7. Troubleshooting

If a Custom API row does not work as expected, check the following:

• Verify that the API path is correctly formatted.
• Confirm that the function exists within the specified module.
• Ensure the application containing the function is installed.
• Review Error Logs for any Custom API Error entries.
• Check that Line References are correctly defined when using API results inside formulas.

Common issues include:

• Method not found – Incorrect module path or function name.
• Validation error – Invalid API path format.
• Zero values returned – Function execution failed and ERPNext defaulted to zeros.
• Formula reference error – Missing or incorrect Line Reference.

Reviewing ERPNext Error Logs is the quickest way to diagnose issues related to Custom API rows.

8. Benefits of Using Custom API Rows

• Extend financial reports with custom business logic
• Integrate external data sources into reports
• Create advanced KPIs and financial metrics
• Automate complex calculations
• Reduce manual spreadsheet work
• Build highly customized financial statements

9. Related Topics

• Financial Report Templates
• Account Categories
• Calculated Amount Rows
• Balance Sheet
• Profit and Loss Statement
• Cash Flow Statement