Skip to main content

Creating Query Reports

This guide walks through building a QUERY view type report: defining the SQL statement, setting viewer access, and saving the section. The examples in the reference videos include get_active_loans and sumo-user-guide.

Prerequisites​

  • Access to Settings → My reports in Report Builder.
  • A new query report created via Add New Report, or an existing query opened through the row gear icon.

Add Section — Query Configuration​

After creating a new Query report (or opening an existing one), the Add Section editor opens. The breadcrumb reads New Report → Add Section.

Section Fields​

FieldDescription
NameInternal section name. Becomes the report title in the render view (for example, get_active_loans).
DescriptionOptional explanation of what the query returns.
View TypeSet to QUERY for SQL-based sections.
ViewersControls which users or roles can see this section. Select one or more viewer groups from the dropdown.

Writing the SQL Query​

  1. Scroll to the SQL editor at the bottom of the Add Section page.
  2. Enter a valid SELECT statement against the ledger database.

Example: Active Loans Query​

The following query retrieves all records from the loan_accounts table type:

SELECT
  *
FROM
  table_data
WHERE table_type_id = 'loan_accounts'

Example: User Holdings Query​

A more advanced query selects and casts specific columns from the user_holdings table type:

SELECT
  CAST(h.freecode1 AS TEXT) AS user_id,
  CAST(h.freecode2 AS TEXT) AS scrip_name,
  CAST(h.total_qty AS TEXT) AS total_qty,
  CAST(h.pledged_qty AS TEXT) AS pledged_qty,
  CAST(h.total_qty - h.pledged_qty AS TEXT) AS available_qty,
  CAST(h.latest_price AS TEXT) AS latest_price
FROM (
  SELECT DISTINCT ON (freecode1, freecode2) *
  FROM table_data
  WHERE table_type_id = 'user_holdings'
  ORDER BY freecode1, freecode2, value_date DESC
) h
  1. Use meaningful column aliases — action field mappings and export columns rely on the names returned by the query.

Saving the Query Section​

  1. Review the Name, Viewers, and SQL statement.
  2. Click Save (floppy disk icon) at the bottom-right of the SQL editor.
  3. The section is persisted and the report becomes available in the View List.

To discard unsaved changes, click Delete (trash icon) or close the section using the X button in the breadcrumb area.

Testing the Report​

After saving, verify the report from the end-user portal:

  1. Navigate back to the Report Builder home page (View List).
  2. Locate the new report by ID and name (for example, 3456 — sumo-user-guide).
  3. Click the report row to open the render view.
  4. Click Run Query and confirm the expected columns and rows appear.
  5. Optionally click Export Excel to validate the export output.

See Running Reports for the full end-user procedure.

Optional Next Steps​

TaskGuide
Add runtime filters (account_id, boid, etc.)Parameters and Actions
Link rows to Form Config workflowsParameters and Actions
Reference the view from a processReport Stage in Process documentation

Important Notes​

  • Query section Name should be descriptive and unique within the report.
  • Always assign Viewers before publishing — unrestricted views may expose sensitive ledger data.
  • Test with Run Query after every SQL change to catch syntax errors before users access the report.
  • Column names returned by the SQL query must match the field names used in action mappings.