Query Report

Query Reports are reports generated using a single SQL query. The query can be simple or complex, as long as it returns the required columns and records. These reports are stored in the database and can be created only by users with the System Manager role.

Tip:

Query Reports are ideal when your reporting requirements can be fulfilled using SQL alone without writing Python code.

How to Create a Query Report

To create a Query Report, search for New Report using the Awesome Bar and create a new report with the required configuration.

Set Report Type to Query Report.
Select the Reference DocType. Users with access to this DocType can access the report.
Select the appropriate Module. The report will appear under the module’s Custom Reports section.
Write the SQL query that returns the report data.

Note:

If Is Standard is enabled while Developer Mode is active, Frappe generates a JSON file for the report. This file should be committed to version control if the report is intended to be distributed as part of an application.

The selected module determines where the generated JSON file is stored within your application.

Columns and Filters

Available from Version 13

Version 13 allows you to configure report columns and filters directly from the Report document instead of defining them inside the SQL query.

For each column and filter, you can configure:

Label
Width
Field Type
Formatting options

Filter values can be referenced inside the SQL query using formatting variables.

%(customer)s

Tip:

Using report filters allows a single query to generate different results based on user input without modifying the SQL statement.

Example Query

The following example retrieves work orders that are not yet fully completed.

SELECT
    name,
    creation,
    production_item,
    qty,
    produced_qty,
    company
FROM
    `tabWork Order`
WHERE
    docstatus = 1
    AND IFNULL(produced_qty, 0) < qty;

Formatting Columns (Legacy Method)

Before Version 13, column formatting was defined directly within the SQL query using a special column alias format.

{label}:{fieldtype}/{options}:{width}

If your report already defines columns in the Report document, you do not need to use this legacy syntax.

Example

SELECT
    `tabWork Order`.name AS "Work Order:Link/Work Order:200",
    `tabWork Order`.creation AS "Date:Date:120",
    `tabWork Order`.production_item AS "Item:Link/Item:150",
    `tabWork Order`.qty AS "To Produce:Int:100",
    `tabWork Order`.produced_qty AS "Produced:Int:100",
    `tabWork Order`.company AS "Company:Link/Company:"
FROM
    `tabWork Order`
WHERE
    `tabWork Order`.docstatus = 1
    AND IFNULL(`tabWork Order`.produced_qty, 0) < `tabWork Order`.qty
    AND NOT EXISTS (
        SELECT name
        FROM `tabStock Entry`
        WHERE work_order = `tabWork Order`.name
    );

Each column alias specifies how the column should be displayed in the report.

Label – The column heading displayed in the report.
Field Type – Determines how the value is formatted (for example, Link, Date, Currency, or Int).
Options – Additional information such as the linked DocType.
Width – Specifies the column width in pixels.

For example, Work Order:Link/Work Order:200 creates a Link field pointing to the Work Order DocType with a column width of 200 pixels.

Rating: 0 / 5 (0 votes)