Custom reports in ERPNext: Report Builder vs query and script reports

Which ERPNext custom report type fits your question

ERPNext offers three ways to build a report beyond the standard list views: the no-code Report Builder, query reports and script reports. The right choice depends less on how complex the output is and more on who will maintain the report and where its logic should live. Finance, stock and production leads usually start with Report Builder, because it lets a trained user pick columns, filters and a group by field on a DocType and save the result without involving developers. When the question needs a calculation that the builder cannot express, such as a multi-step join across DocTypes or a custom aggregation, the logic moves into code.

A query report runs a SQL statement stored in ERPNext and suits teams with a strong database specialist who will own the query over time. A script report is a Python report that lives in a custom app, is version controlled and can call application logic, which makes it the better fit when the report must be tested, reviewed and deployed like other code. Ask who will edit the report after go-live, which skills that person has, and whether the logic should be reviewed in git. Confirm the report type's current behavior and any restrictions for your ERPNext version against the official Frappe documentation before you commit to a design.

All three types sit on the same permission model, but that protection only holds when it is configured correctly. Report Builder respects the role permissions and user permissions already defined on each DocType, so check those first. Query and script reports must be checked deliberately, because code that reads data directly can bypass what a user would see in the list view if the developer does not apply the same filters. Before a report reaches users, test it with accounts that have restricted roles and multi-company or territory limits, and record who may create, edit and run each report.

How Report Builder, query reports and script reports differ

Report Builder is the no-code option. A user picks a DocType, selects columns from its fields and linked fields, applies filters and, where the report supports it, groups rows and aggregates values by a field. Results can be exported and saved, so analysts can answer many routine questions without a developer. The report reads data through the permissions of the person running it, and the report definition is stored as a Report record in the site database.

Built by: a system manager or analyst working in the Desk interface, usually without writing code.
Stored in: a Report record in the site database, so the definition belongs to that site and does not live in the codebase.
Scope: one DocType at a time, with linked fields pulled in. Questions that need joins across unrelated DocTypes may outgrow it.
Group by: groups rows on a field and totals the values. Confirm which aggregate functions your ERPNext version exposes.
Ownership: saved reports belong to the user who created them, so name a maintainer for reports that finance or operations depend on.

Check that every field your analysts need sits on the DocType they query, and confirm the group-by behaviour you need in your version before promising the report to users.

Configuration options and when each report type fits

This table compares Report Builder, query reports and script reports on the skills they need, where the definition lives, how they are versioned, and how they handle filters, grouping and upgrades. Read it by matching your report's data logic and ownership model to a row, then check the upgrade and version control rows before choosing the type for reports that must survive ERPNext upgrades.

Criteria	Report Builder	Query report	Script report
Skill needed	Desk user comfortable with ERPNext fields and filters, no code	SQL knowledge and familiarity with ERPNext table structure	Python and Frappe report API knowledge
Where the definition lives	Saved in the database as a Report document on the site	Saved as a Report document with SQL in the site database	Python and JavaScript files inside a custom app or standard module
Version control	Not in git unless exported as a fixture or customization	Not in git by default, so export or record the SQL separately	Stored in app source, so it follows normal git workflows
Filter support	Filters built from the DocType fields in the UI	Filters defined in the report's JSON and passed to the SQL	Filters defined in code, with full control over layout and behavior
Grouping and totals	Group by and summary options available in the builder	Grouping written directly in SQL	Grouping and totals computed in Python
Permissions	Respects the user's role and permission rules on the DocType	Confirm with your vendor or test how it applies role checks in your version	Permission checks are written by the developer, so test them per role
Fit for upgrades	Usually survives upgrades because it is data, but confirm field names after major versions	SQL may break if table or field names change, so retest after upgrades	Code may need changes after framework upgrades, but is tested in source control

When Report Builder with group by is enough

Report Builder with group by is the right first choice when the question is a summary of one DocType or a few linked DocTypes: totals of Sales Invoice amounts by customer, open Purchase Orders by supplier, or stock quantities by warehouse and item group. You pick the fields, add filters, group rows and apply aggregate functions in the browser, and ERPNext applies the same role and user permission checks that govern the underlying records. Before building, confirm the report can be expressed with the fields the DocType exposes, including child table fields, and that the grouping level you need is available without a join. Most finance and operations summaries that a controller asks for weekly fall into this category, so teams should test them here before writing any code.

The limits appear when the report needs logic the UI cannot express. Typical triggers are joins across unrelated DocTypes, calculations that depend on row-by-row conditions, derived values that combine several sources, or output shaped for a specific external consumer. When you hit one of these, the choice moves to a SQL query report or a Python script report in a custom app, and each carries its own maintenance and permission review. Ask the implementation team to document which requirements were tested against Report Builder and which were rejected, so the decision can be revisited when the next version adds capabilities.

Report definitions created in the UI are stored in the database as Report documents, not in code. This means they do not travel with your app repository, and a copy made on a test site will not reach production unless you export it through a fixture or a deliberate data transfer. Plan a process for promoting saved reports between environments, record who owns each one, and decide whether business users may create reports in production at all. Reports that matter for audit or month-end should be moved into version control once their design is stable, so a restore or rebuild does not depend on someone remembering the filters.

How to build a custom report step by step

This sequence takes a reporting question from a business need to a tested, permissioned report that matches figures your team already trusts. Each step ends with a check, so errors surface before users depend on the output.

Confirm the business question and source DocTypes
Write down the question the report must answer, the period, the grouping and the filters users need. Identify the source DocTypes (for example Sales Invoice, Stock Ledger Entry or Journal Entry) and the fields that hold each figure. Agree the definition of each total with the finance or operations owner. The step is done when the owner signs off a one-page specification listing the columns, filters and the standard report that the output should reconcile to.
Choose the right report type
Select Report Builder when the question is a list or a simple group-by over one or two DocTypes that users can adjust themselves. Choose a Query Report when the logic needs SQL joins or aggregates the builder cannot express, and a Script Report when the output needs Python processing or custom formatting. Confirm with your platform team whether server-side code is allowed in your environment, since script-based reports depend on it. The check is a written reason for the choice, recorded in the specification.
Build the report and run it on test data
Create the report in the chosen tool and set its columns, filters, grouping and sort order. For Query Reports, parameterize filters rather than concatenating user input, and keep the SQL readable enough for a reviewer. Run the report against a staging copy or a test company with known transactions, not live ledgers. The check is that row counts and totals match a hand-calculated sample for at least one period and one filter combination.
Reconcile totals against a standard report
Run the same period and filters through the closest standard ERPNext report, such as the General Ledger, Accounts Receivable or Stock Balance report, and compare the totals line by line. Investigate any difference by checking cancelled documents, draft records, currency conversion, and date fields used for filtering. Record each explained difference. The step is done when the totals match or every variance has a documented cause agreed with the business owner.
Test with a restricted user
Create a test user that holds only the roles the report audience will have, then open the report as that user. Confirm which DocTypes, rows and fields appear, and that filters cannot reveal records outside the user's permissions. Include a user with a user permission restriction, such as one company or one warehouse, if your data uses them. The check is a short test log listing each role tested and the result, signed by the report owner.
Set report permissions and sharing
Assign the report to the roles that need it using the role permission settings, and remove access from roles that do not. Confirm that the report does not expose data through a parent DocType the user cannot read, since Query and Script reports run with the code's own queries. Decide who may edit the report definition and restrict that to administrators or developers. The check is a permission matrix comparing intended roles against the roles that can open the report.
Move app-level reports into version control
If the report is a Script or Query Report that ships with a custom app, commit its JSON definition and Python or SQL files to the app's repository, then install the app on staging through the normal deployment path. Use a code review step for changes to queries and permission-sensitive logic. Where the report was built in the UI and must travel between sites, check how your team exports and syncs customizations before relying on them. The check is that a fresh staging install produces the same report with the same totals as the reference build.
Hand over and monitor after release
Give the report owner a short runbook covering the purpose, source DocTypes, filters, known variances and the person responsible for changes. Schedule a reconciliation check at the next period close and after each ERPNext upgrade, since report queries can depend on field names and schema. The step is complete when the owner confirms the first live period matches the reconciled reference and the runbook is stored with the app or the implementation documentation.

How report permissions decide who sees the data

A report in ERPNext does not carry its own security model by default. Access depends on several layers that interact, so a user can see data through a report even when they cannot open the underlying records directly. The cards below cover each layer and the check to run before a report reaches users.

Role permissions on underlying DocTypes

Report Builder and query reports read the DocTypes beneath them, and the database query does not automatically apply the row-level checks a user gets when opening a form. A role that can read a DocType in a report may see fields and totals it would never open one record at a time. Check that every DocType a report touches grants read access only to roles that should see that data, and test with a user from each role.

Report-level role access

A report can be restricted to specific roles in its own permission settings, separate from the DocType permissions beneath it. This control decides who can open the report at all, so it is a second gate, not a replacement for the first. Confirm in your version which roles are listed on each report, and remove default roles that were never intended to see financial or HR figures.

User permissions on linked values

User permissions restrict a user to specific values, such as one company, warehouse or customer group. Whether a report honors those restrictions depends on how it is built, and a script or query report may not apply them unless the code filters explicitly. Create a test user limited to one company and confirm that totals and row counts match only that company's records.

Indirect exposure through aggregates

A grouped or summed report can reveal values a user cannot open, such as salaries by department or margin by customer, even when individual records are hidden. Small groups can identify a single person or account. Review every group-by and total in the report, and decide whether a minimum group size or a masked column is needed before the report is shared.

Script logic that bypasses checks

Script reports and query reports can run with database access that skips the permission checks Frappe applies in the form view. A query that selects directly from tables, or a script that calls privileged functions, can return rows regardless of the user's roles. Review each query and script for direct SQL and for ignore-permission flags, and confirm that a restricted user gets an empty or partial result when expected.

Testing with real restricted accounts

Permission rules change across versions and interact in ways that are hard to predict from configuration alone. Testing with administrator accounts shows nothing about what other roles see. Build a short matrix of roles and user permissions, run each report as each account, and record the results alongside the report so that changes to roles or the report can be retested.

Why script reports need an app and what server scripts cannot replace

Script reports run Python on the server, so they belong in a custom app rather than in the database. A script report defined through the UI lives only in one site's database, which makes it hard to review, test and promote from a staging site to production. Keeping the report's Python file, its JavaScript filters and its doctype and report_name definitions in a Git-tracked app gives you code review, a history of changes and a repeatable deployment step through bench on every environment. Decide early which reports are business-critical enough to justify that overhead, and plan an owner for the app who can review changes to financial or stock logic before they reach users.

Server scripts are a separate feature and do not replace a script report. A server script runs Python on document events or as an API endpoint, under a restricted execution environment, while a script report is a Python module inside an app that returns columns and rows to the report view. From version 15, server scripts are disabled by default and must be turned on with the server_script_enabled setting, so a report plan that assumes they will work on any site is incorrect. Frappe Cloud public shared benches do not allow server scripts at all. Confirm the hosting tier and the enablement setting for every environment before you design a report around them, and check the current behavior in the Frappe server script documentation.

Security and operational risks of custom reports

Custom reports often reach the most sensitive data in ERPNext, and they run queries that the standard screens never issue. The four risks below connect access, query safety, load and upgrades, and each one comes with a check you can run before a report goes live.

Data exposure through broad roles

A report inherits the permissions of the user who runs it, but a broad role can expose margins, salaries, customer bank details or whole companies in a single list. Report Builder and query reports do not automatically hide fields that a user could not open on a form. Before release, run the report as a user from each target role and confirm that restricted companies, fields and records are missing from the output, not just from the screen.

SQL injection in query reports

Query reports accept SQL, and filter parameters that are concatenated into the statement let a crafted value change the query's meaning or read other tables. Use named parameters bound through the report filters and never build SQL strings from user input. Review every query report that accepts free text, and test it with quote characters and SQL keywords in each filter before it is shared with more users.

Load on large tables

A report that scans ledger entries, stock ledger or item-level child tables without date limits can slow the whole site, especially when several users run it at month end. Script reports that loop over documents in Python add to that cost. Ask for a realistic row count in production, set default date ranges, and time the report on a copy of the production database before scheduling or exposing it.

Background and scheduled report jobs

Reports sent by email on a schedule run under a chosen user and rely on background workers and the scheduler. If that user's permissions change, or the worker queue backs up, recipients may receive stale data or nothing at all. Confirm which user owns each scheduled report, who receives it, and how failures are noticed, and review the list when staff change roles.

Regression testing after upgrades

Minor and major ERPNext releases can change standard report columns, filter behavior and the database fields a query depends on. A custom report that worked last quarter can return wrong totals or fail silently after an upgrade. Keep a list of custom reports with expected totals for a fixed period, rerun them on a staging site after every upgrade, and compare results before production is updated.

Undocumented reports and ownership

Reports created in the UI are often edited by several people, and nobody records what each one calculates. When a report produces a number used for a decision or a regulatory return, the lack of an owner and a written definition becomes an operational risk. Assign each report an owner, record its source tables and filters, and move reports that matter into version control so changes are reviewed.

Questions buyers ask about custom ERPNext reports

What drives the cost of a custom ERPNext report?

The cost follows the report type, the number of data sources, and how many rules the output must follow. A Report Builder report with grouping and a few filters is the least work. A SQL query report or a Python script report costs more because someone must write, test and review the query or code, check permissions, and maintain it. Other drivers are the number of reports, the number of stakeholders who must sign off the layout, and whether the figures must reconcile to accounting or stock ledgers. Ask any partner for a quote that lists each report, its type, its inputs, its output format and its test data, since a written scope is what makes the price comparable.

How long does it take to build one ERPNext report?

Duration depends mainly on the report type and on how quickly the business confirms the expected output. A Report Builder report is usually the fastest to deliver because it needs no code, but reviewing its results against finance or operations data still takes time. Query and script reports need development, peer review and testing against realistic data, so they take longer. Confirm the timeline in writing with the partner, and ask which steps depend on your team, such as approving sample outputs or granting access to a test site. DeployOpen plans timelines inside each fixed-scope agreement rather than publishing a general figure.

What does the work need from our team?

The work needs clear sample outputs, named data owners and a test environment with data that resembles production. Sample outputs should be spreadsheets or screenshots showing the columns, sort order, totals, filters and export format the users expect. Data owners, usually from finance, operations or sales, must confirm definitions such as which posting date counts, how cancelled documents are treated and which currency applies. Engineers also need read access to a non-production site and a list of the roles that will run the report. Without these inputs, reports often need rework after the first review.

Can our staff build Report Builder reports themselves?

Yes, in most organizations users with the right role can create Report Builder reports without code, and this is a practical way to handle simple summaries. Staff should understand the DocType they query, the difference between filters and group by, and how role permissions limit what each user sees. Reports that need joins across unrelated data, complex calculations or formatted outputs usually belong in a query or script report. A useful approach is to let trained staff own simple reports and have an engineer review reports that feed decisions, audits or external parties.

Will our custom reports survive an ERPNext upgrade?

Report Builder reports are stored as records in the database, so they usually carry over an upgrade, but their field references should be checked after each major version because standard fields can be renamed or removed. Query and script reports live in an app, so they survive upgrades when the code is kept under version control and tested against the new version before the upgrade. Tests should cover the report output, permissions and performance. Ask the partner how the report code is versioned, how upgrade testing is done, and whether a retainer covers those checks after go-live.

What are the risks of a SQL query report?

A SQL query report can return wrong figures if it misses a filter, double counts rows from a join, or ignores cancelled documents. It can also expose data if it bypasses the permission checks that Report Builder applies. Queries can slow the database when they scan large tables, which affects other users. Review each query with the data owner, test it against known totals, and confirm that it respects user permissions. Run heavy reports on a schedule or during quiet hours, and keep the query in version control so changes are traceable.

When is a Python script report the right choice?

A Python script report fits logic that the other two types cannot express clearly, such as multi-step calculations, custom aggregation, or output built from several sources that must be processed in order. It belongs in a custom app so it can be versioned, reviewed and deployed like other code. Expect more effort for design, testing and ongoing maintenance than for a Report Builder report. Confirm with your team that you can staff the maintenance, because a script report written by one person and left unreviewed becomes a business risk at the next upgrade.

How DeployOpen scopes and delivers custom ERPNext reports

DeployOpen starts by agreeing which reports the business needs, which data sources they read and who may see them. We then build each report with the lightest tool that meets the requirement, test it against real permission scenarios and hand over the code and documentation so your team can maintain it.

How we scope

Inventory the reports people run today, the questions each one answers and the decisions that depend on them
Decide per report whether Report Builder, a query report or a script report fits, and record the reason
Agree which roles and user permissions each report must respect, including multi-company restrictions
Confirm the ERPNext version and hosting model, since script reports and server-side code depend on them
Engagements are fixed-scope: deliverables and timelines are written down before work starts

How we build

Build no-code reports in Report Builder first, using group by and filters where they meet the requirement
Write SQL query reports only where joins or aggregations exceed what Report Builder can express
Place script reports in a custom Frappe app under version control, not edited directly on the production site
Test each report with users holding different roles to confirm the permission results match the agreed model
Review query performance on production-sized data before release, with the database and scheduler settings in view

How we hand over

Deliver the report definitions, app code and deployment steps so the reports move through your staging and production sites
Document each report's purpose, source tables, filters, permission rules and known limits in plain language
Provide a test checklist your finance or operations owners can rerun after each ERPNext upgrade
Walk your administrators through how to add a report, change a filter and read the permission settings
Hand over a list of open questions your team should confirm with the business before the next release

How we support

Ongoing support, upgrades and continuous testing are available as retainers after handover
Re-test custom reports during each ERPNext minor or major upgrade, since report code can depend on internal schema
Review report permissions when roles, companies or user groups change in your organization
Agree in writing what a retainer covers, response expectations and how changes to reports are requested
Ask for a written quote based on the agreed scope; DeployOpen does not publish a price list

Building custom reports in ERPNext: Report Builder, query and script reports