Introduction: What Is a Query Report in Frappe?

A query report in Frappe Framework v15 is a type of report that fetches data directly from the database using SQL queries. Unlike Script Reports, Query Reports rely on structured SQL statements to return tabular data.

Query reports are ideal when:

• Data spans multiple DocTypes
• Complex joins or aggregations are required
• High-performance read-only reporting is needed

Why Use Query Reports?

Query reports are commonly used because they:

• Provide fast, optimized data retrieval
• Support complex joins and calculations
• Are easy to maintain for read-only analytics
• Work well for dashboards and exports

They are read-only by design, ensuring database safety.

Types of Reports in Frappe v15 (Context)

Frappe supports multiple report types:

• Query Report – SQL-based, read-only
• Script Report – Python-based logic
• Report Builder – UI-based simple reports

This guide focuses exclusively on Query Reports.

How to Create a Query Report in Frappe v15 (Step-by-Step)

Step 1: Open the Report List

Answer:
Go to Report List → New and create a new report.

Fill in:

• Report Name
• Ref DocType (primary DocType)
• Report Type → Query Report

Save the document.

Step 2: Write the SQL Query

Answer:
Add your SQL query in the Query field of the report.

Example:

SELECT
name,
customer,
posting_date,
grand_total
FROM
`tabSales Invoice`
WHERE
docstatus = 1

This query fetches submitted Sales Invoices.

How to Add Filters to a Query Report

Using Filters in SQL

Query reports support named placeholders for filters.

Example:

SELECT
name,
customer,
posting_date,
grand_total
FROM
`tabSales Invoice`
WHERE
docstatus = 1
AND posting_date BETWEEN %(from_date)s AND %(to_date)s

The placeholders map directly to report filters.

Defining Filters in the Report

Add filters in the Filters section:

• Fieldname: from_date
• Fieldtype: Date
• Fieldname: to_date
• Fieldtype: Date

Frappe automatically passes these values to the SQL query.

How Column Labels Are Generated

Answer:
Column headers in query reports are automatically generated from SQL aliases.

Example:

SELECT
name AS "Invoice:Link/Sales Invoice:120",
customer AS "Customer:Data:180",
grand_total AS "Total Amount:Currency:120"
FROM
`tabSales Invoice`

This controls:

• Label
• Field type
• Column width

How Query Reports Handle Permissions

Query reports:

• Respect user permissions
• Obey DocType access rules
• Do not bypass role-based security

If a user lacks permission for a DocType, data will not be returned.

Best Practices for Query Reports

Always use parameterized queries
Never concatenate raw values into SQL.

Avoid heavy joins where possible
Optimize for performance and readability.

Use aliases for column formatting
This improves UI clarity and export quality.

Keep reports read-only
Do not attempt updates or deletes.

Common Mistakes and Troubleshooting

Report shows no data

• Check docstatus conditions
• Verify filter values
• Ensure data exists

SQL syntax error

• Validate table names (tabDocType)
• Check alias formatting
• Avoid unsupported SQL functions

Permission denied

• Review user roles
• Confirm DocType permissions

Advanced Use Case: Joining Multiple DocTypes

SELECT
si.name AS "Invoice:Link/Sales Invoice:120",
si.customer AS "Customer:Data:180",
soi.item_code AS "Item:Link/Item:120",
soi.amount AS "Amount:Currency:120"
FROM
`tabSales Invoice` si
JOIN
`tabSales Invoice Item` soi
ON
si.name = soi.parent
WHERE
si.docstatus = 1

This query joins parent and child tables safely.

Industry Relevance and Use Cases

Query reports are widely used for:

• Financial summaries
• Sales analysis
• Inventory movement reports
• Compliance and audit reporting
• Management dashboards

They are essential for ERPNext analytics and reporting.

Technical Prerequisites

• Frappe Framework Version 15
• SQL knowledge (MariaDB/MySQL syntax)
• Access to Report DocType
• Read-only reporting requirement

Related Documentation

• Script Reports in Frappe
• Report Builder
• User Permissions
• Database Tables and Naming

Conclusion

Query reports in Frappe Framework v15 provide a powerful, efficient way to build read-only reports using SQL. By combining parameterized queries, filters, and column metadata, developers can create high-performance reports that respect permissions and scale across ERPNext deployments.
For ERPNext and Frappe professionals, mastering query reports is essential for advanced reporting and analytics.