Developer's Guide to Creating and Customizing Query Reports in Empress

Developers
1

Introduction

Welcome to this comprehensive developer guide. Today, we’ll be delving into a crucial feature in Empress - the Query Reports.

Query Reports in Empress are reports that can be generated using a single SQL query. This query can range from simple to complex, provided it generates columns and records. A key point to remember is that these reports can only be created by a System Manager and are stored in the database.

Creating a Query Report

To begin creating a Query Report, type “new report” in the awesomebar and hit enter. Follow these steps:

  1. Set Report Type as “Query Report”
  2. Set the Reference DocType - Users that have access to the Reference DocType will have access to the report
  3. Set the Module - The report will appear in the “Custom Reports” section of the module.
  4. Write your query

If you set Standard as “Yes” and Developer Mode is enabled, then a JSON file will be generated which you will have to check in to your version control. You should do this only if you want to bundle Query Reports with your app. The Module will decide where the JSON file will go.

Columns and Filters

Starting from Version 13, you can configure the columns and filters in the Report document. Here you can set the label, width, format (fieldtype) for the columns and filters.

Filters can be used as formatting variables in the query. For example a filters of type customer can be used as %(customer)s in the query.

Example

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

Formatting Columns (Old Style)

Alternatively, columns can also be formatted by specifying the label of the column in a particular format: {label}:{optional fieldtype}{optional /}{optional options}:{optional width}

If you have configured the fields and columns in the Report itself, you do not need to use this style.

Example (Old Style)

Here’s what a query may look like:

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)

If you notice there is a special syntax for each column, we use this information to format the Report View.

For example: The first column Work Order:Link/Work Order:200 will be rendered as a Link Field with the DocType Work Order and the column width would be 200px.

Conclusion

Understanding the Query Reports feature can significantly enhance your software development and customization capabilities within Empress. It extends greater control over data representation and accessibility, contributing to the development of robust and custom business solutions. As a developer, mastering this feature will allow you to create reports that are both dynamic and tailored to specific business needs.