Reports & Queries

Two Tools for Two Jobs

dForge separates reports (formal, packaged with modules) from ad-hoc queries (personal, exploratory).

Tool	Owner	Where it lives	Use case
Reports	Module developer	Packaged in the module, shared with all users	Recurring dashboards, packaged analytics, parameterized exports
Visual Query Builder	End user	Personal, saved per user per module	Ad-hoc questions, one-off analysis, exploring data

Both produce the same query JSON, so a useful ad-hoc query can later be turned into a formal report dataset.

Reports

A report is a collection of one or more datasets rendered together with a layout. Each dataset is either a query or a stored procedure that returns rows. Datasets can be hierarchical (master-detail), and they share parameters.

Datasets

Each dataset has:

• A type: query (Q) or stored procedure (S)
• A query definition or stored procedure reference
• Optional parameters
• Optional column display properties (formatting, alignment, widths)
• An optional parent dataset for master-detail relationships

A single report can mix query datasets and stored procedure datasets — useful when you need a developer-written SP for performance-critical aggregations alongside a metadata-defined query.

Parameters

Report parameters are derived from dataset parameters. Each dataset declares the parameters it needs; the report’s effective parameter set is the union of all dataset parameters by name.

"params": {
  "start_date": {
    "type": "date",
    "label": "Start Date",
    "required": true,
    "default": "=STARTMONTH(NOW())"
  },
  "end_date": {
    "type": "date",
    "label": "End Date",
    "required": true,
    "default": "=ENDMONTH(NOW())"
  },
  "region": {
    "type": "reference",
    "entity_cd": "region",
    "label": "Region"
  }
}

Parameters with formula defaults (prefixed with =) are evaluated at report execution time, so a parameter like STARTMONTH(NOW()) always opens to the first day of the current month.

If two datasets declare the same parameter name with incompatible types, that’s a design-time error — fix it by renaming one of the parameters.

Saved Parameter Sets

Users can save the parameter values they use most often as named parameter sets (e.g., “Q1 East Region”, “Last Month All”). These are stored against the report so the user can re-run the same view with one click.

Auto-Refresh

Reports can declare a reload_interval (in seconds) at the report level, overridable per layout panel. The UI re-runs the queries on the interval — useful for live dashboards.

Security

Report execution honors the same security stack as any other query:

1. The user must have E (Execute) right on the report’s sec_object
2. Each dataset’s query is executed with the current folder’s row filter and the user’s row-level security rules applied
3. Column-level security from the active entity view is enforced

A report can never expose more data than the user could see by querying the underlying entities directly.

Visual Query Builder

The query builder lets users create, save, and re-run ad-hoc queries without writing SQL. It produces the same query JSON used by report datasets, so a query created here can later be promoted into a formal report.

Where to Find It

Queries is a top-level sidebar link visible to all users. Open it to see your saved queries for the current module’s folder.

Building a Query

1. Pick a root entity — the SQL FROM clause
2. Pick columns from the relationship tree (the builder follows references and sets, so you can pull in fields from related entities without thinking about JOINs)
3. Add filters
4. Optionally turn on grouping to aggregate
5. Pick a sort order
6. Save it

Flat vs Grouped Mode

A toggle controls whether the query returns rows or aggregates:

Mode	SQL analog	Use case
Flat	SELECT ... FROM ...	List individual records
Grouped	SELECT ... FROM ... GROUP BY ...	Aggregate / summarize

In grouped mode, every column needs an aggregation: group (dimension), sum, count, avg, min, or max. At least one column must be a group and at least one must be an aggregate.

Scope and Ownership

• Saved queries are module-scoped and user-owned — only you see them
• A query you save in one folder can be run from any other folder in the same module; the execution-time folder determines which row-level filters apply
• This is the same module_id FK pattern reports use, so queries can later be moved into reports

Promoting a Query into a Report

When an ad-hoc query becomes a recurring need, the same query JSON can be packaged into a module’s reports.json as a dataset. No translation step — the structure is identical.