> For the complete documentation index, see [llms.txt](https://docs.openiam.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.openiam.com/administration-guide/reports/design-a-report.md). # Design a report The report designer — change a report's data source, fields, calculated fields, grouping, totals and file naming after it exists, and what Save checks before it writes. ## What this is, and when you need it The designer is where you change a report that already exists: swap or extend its data source, pick and rename the fields, add calculated fields, set grouping and totals, and decide how its files are named. Open it with **Edit** in the row menu of the [Reports list](/administration-guide/reports/manage-reports.md#list-row-menu) or with **Edit fields & layout** on the [details page](/administration-guide/reports/manage-reports.md#details-header); the route is `/webconsole-v2/audit/reports//edit`. You are in the wrong place if you are starting from nothing — the [wizard](/administration-guide/reports/create-a-report.md) asks the same questions in order and creates the report for you. You are also in the wrong place if the report is a **system template**: it opens read-only with a **Clone to edit** button, and the copy is what you design (see [System templates](/administration-guide/reports/system-templates.md#template-clone)). A report over an **SQL data source** opens in the designer like any other, but its data source itself is edited on the [view editor](/administration-guide/reports/views-and-sql-sources.md#sql-source). The schedule, the subscribers and the retention days are not in the designer. They live on the details page: see [Schedule and deliver a report](/administration-guide/reports/schedule-and-deliver.md) and [Retention](/administration-guide/reports/run-and-download.md#retention-policy). ## At a glance | Tab | What you set there | Page section | | ----------- | ----------------------------------------------------------- | -------------------------------------------- | | Data source | Build from entities, or use an existing view; Save as view | [The Data source tab](#designer-data-source) | | Fields | Which fields, their headings, their sort; calculated fields | [The Fields tab](#designer-fields) | | Configure | Group by levels; the totals per column | [The Configure tab](#designer-configure) | | Naming | CSV or Excel; the file name pattern | [The Naming tab](#designer-naming) | Nothing reaches the server until you choose **Save**. Every tab shows a **Has errors** marker while it holds a validation problem, and the summary above the tabs lists them all. ## Before you begin * You own the report, or you are a super administrator. Anyone else gets *You do not have access to this report*. See [Access and permissions](/administration-guide/reports/access-and-permissions.md#access-report-rights). * The report is not a system template. Templates open read-only; clone first. * No run of the report is queued or running if you intend to delete it from the footer. ## Open the designer The header holds what the wizard asked for first: | Element | What it is | | ------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | | Edit report / New report | The page label. *New report* appears only when the designer is reached without a saved report; the wizard is the supported way to create one. | | Report name | Inline, required. The name is used in lists, in e-mails and as the `{report}` placeholder of the file name. | | Description | Inline, optional. | | Source line | *Entities: User + User role membership + Role* for an inline source, *View: Contractor roles* for a view, or *No data source chosen*. | | Unsaved changes | A badge that appears as soon as anything differs from the saved report. | | The definition has errors | A summary of every validation error, above the tabs, with the tab each belongs to. | A system template shows the notice *This is a system template and cannot be edited* with **Clone to edit** in place of the editable header. ## The Data source tab Two modes, as a pair of tabs inside the tab. Which one a report uses is decided here and shown on the source line in the header. ### Build from entities The report gets an **inline data source**: entities, relationships and filters that belong to this report alone and are deleted with it. The hint reads *Combine one or more entities. Removing one also removes its fields and filters.* The editor is the same visual builder the view editor uses, in its four numbered sections — primary entity, related entities, filters, validate. It is described once, on [Views and SQL data sources](/administration-guide/reports/views-and-sql-sources.md#builder-primary-entity); the [filters section](/administration-guide/reports/filters-and-parameters.md#filter-editor) has its own page. Because the designer has no separate Filter tab, **the report's filters are edited here**, in section 3 of the builder. ### Save as view Below the inline builder, **Save as view** copies the entities, relationships and filters into a named view that other reports can read. The dialog asks for one thing, **View name**, and **Save view** confirms. The toast reads *Saved as view. The report now uses it.*: the report switches from its inline source to the new view, so after this step the Data source tab is in *Use an existing view* mode and the builder is gone from this page. Edit the view itself from the [Data Catalog](/administration-guide/reports/data-catalog.md#catalog-views-tab). ### Use an existing view The report reads a **view** by name. The hint reads *Pick one custom view. Choosing another replaces the current one.* Pick it in the **Choose a view** select, which searches by name; when there are none yet the tab says *No views yet. Build one from entities or create it in the Data Catalog.* A view's entities, relationships and filters are managed on the view, not here. What the report adds on top is the choice of fields, the grouping, the totals and the file naming. When the view is an administrator SQL data source, the tab shows the notice *This view is an administrator SQL data source. It is not subject to field and row filters, so the report shows every row and Field it returns* — see [Access and permissions](/administration-guide/reports/access-and-permissions.md#access-sql-sources). ### Switch between the two modes Switching modes is destructive, so the designer asks first: > **Switch data source type?** Switching clears the chosen entities or view, and the selected fields, group-by and filters. **Switch and clear** does it; **Keep current source** backs out. There is no undo short of leaving the page without saving. ## The Fields tab Two lists side by side with arrows between them, and the calculated-fields section underneath.

The designer Fields tab with the User fields under Available, Role under Selected with its heading and sort control, and the Add calculated field button below

### Available Every field of the main entity and of each related entity, grouped by entity, plus the report's calculated fields. A search box narrows the list. A field marked **Locked** (*You do not have access to this field*) cannot be moved to Selected; see [locked fields](/administration-guide/reports/access-and-permissions.md#access-locked-fields). *No fields match* and *No more fields to add* are the two empty states; *Choose a data source to see its fields* appears while the Data source tab is still empty. ### Selected The columns of the file, in order. Each row has a grip to drag it, the heading input, the sort toggle, and **Remove field**. Double-clicking a field in Available adds it; **Add field** and **Remove field** do the same with the arrows. At least one field is required (*Select at least one field*). #### Column heading The text in the first row of the file. It starts as the field's catalog name — *Last login*, *Employee type* — and you type over it in the row. #### Sort The sort toggle on a selected row cycles **none → ascending → descending**. Sorting is part of the saved definition: every run and every scheduled file comes out in that order. A field that the catalog marks as not sortable is refused at Save with the code `NOT_SORTABLE`. #### Not grouped Once the report groups or totals anything, a selected field that is neither grouped nor totalled shows the warning *Not grouped: add it to Group by or remove it*, with an **Add to Group by** button that does the first. This is the [grouping rule](/administration-guide/reports/overview.md#rules-grouping): a grouped report has one row per group, so every column must be a group key or a total. ## Calculated fields A calculated field is a column computed for each row from other fields of the same row. The section sits under the field picker on the Fields tab and is available only for an inline data source; over a view it reads *These calculated fields belong to the view; edit the view to change them*. **Add calculated field** opens the dialog; each existing field has **Edit** and **Delete**, and Delete is blocked (*Used by …; change those formulas first*) while another calculated field references it. ### The dialog

The Add calculated field dialog with label Days since start, key days_since_start, Formula mode, the formula DATEDIFF of user.start_date and TODAY, and the message Valid formula; result type: Number

\| Field | What to enter | | :--- | :--- | | Field label | The column heading, for example \*Days since login\*. | | Key | The stable identifier: lower-case letters, digits and \`\_\`, starting with a letter, at most 63 characters (\`^\[a-z]\[a-z0-9\_]{0,62}$\`). Other formulas reference the field as \`calc.\`. The key cannot change once the field is saved. | | Formula / Conditions | The two modes, below. | | Result type | Shown by the validator once the formula is valid: \*Valid formula; result type: NUMBER\*. | **Add field** (or **Save field** when editing) closes the dialog. The field then appears in Available like any other and must be moved to Selected to become a column. ### Formula mode A spreadsheet-style expression. The hint in the editor says how to reference things: *Type `[` for Fields, `{{param.` for parameters, or a function name*. A field reference is written `[entity.field]` — `[login.last_login]`, `[user.employee_type]` — and the editor completes it from the entities in the data source; text is written in double quotes; a parameter is `{{param.name}}`. Operators: `=` `<>` `<` `<=` `>` `>=` for comparison, `&` for text concatenation, `+` `-` `*` `/` for arithmetic. | Group | Functions | | ------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Logic | `IF(condition, then, else)` · `IFS(condition1, value1, condition2, value2, …, default)` · `AND(…)` · `OR(…)` · `NOT(condition)` | | Empty values | `ISBLANK(value)` · `COALESCE(value1, value2, …)` — the first value that is not empty | | Text | `CONCAT(…)` · `UPPER(text)` · `LOWER(text)` · `TRIM(text)` · `LEFT(text, n)` · `RIGHT(text, n)` · `LEN(text)` · `CONTAINS(text, search)` · `REPLACE(text, from, to)` | | Numbers | `ROUND(number, digits)` · `ABS(number)` · `FLOOR(number)` · `CEILING(number)` · `MOD(number, divisor)` | | Dates | `TODAY()` · `NOW()` · `YEAR(date)` · `MONTH(date)` (1–12) · `DAY(date)` · `DATEDIFF("day"\|"hour"\|"month"\|"year", start, end)` — the unit boundaries crossed from start to end · `DATEADD("day"\|"hour"\|"month"\|"year", count, date)` | The validator runs as you type and reports either the result type or the first error with its position (*at 12: …*). Two restrictions depend on the data source: * Over an **OpenSearch** entity a calculated field can be selected as a column but cannot be used in a filter or a Group by level. * A calculated field may reference another with `calc.`, but not in a circle: the server answers `CALCULATED_FIELD_CYCLE`. ### Conditions mode The same formula built as rows instead of typed: **IF** a condition, optionally **AND** further conditions, **THEN** a value, any number of **ELSE IF** branches, and **ELSE** a value. Each condition is a field, an operator (equals, not equals, greater than, at least, less than, at most, contains, is empty, is not empty) and a value, where the value can be a literal, another **Field**, **Today** or **No value**. **Result type** is shown at the top. Conditions mode and Formula mode edit the same thing: a formula written in Conditions mode is stored as an `IF`/`IFS` expression and can be opened in Formula mode. The reverse is not always true — a formula that is not a plain IF-chain shows *This formula cannot be shown as conditions; edit it as a formula*. ### Limits | Limit | Value | | ---------------------------- | ----------------------------------------------------- | | Calculated fields per report | 50 (*A report can have at most 50 calculated fields*) | | Formula length | 2,000 characters | | Nesting depth | 32 | | Key length | 63 characters | The operator figures for the whole installation are on the [overview](/administration-guide/reports/overview.md#limits). ## The Configure tab

The Configure tab with Role in the first Group by level and the Contractors total

\### Extension Two cards, **CSV** and **Excel**, both disabled and labelled *Available in a later release*. Ignore them: the file type is chosen on the [Naming tab](#designer-naming-file-type). ### Group by Three selects, **Level 1** to **Level 3**, each offering the selected fields the catalog marks as groupable, or **None**. Level 2 and 3 group inside the level above. A report over a view that already groups shows *Grouping comes from the data source …* instead, and the levels cannot be changed here. Setting any level switches on the [grouping rule](/administration-guide/reports/overview.md#rules-grouping): every selected column must now be a group level or carry a total, and the Fields tab marks the others *Not grouped*. ### Totals One row per selected column, with the checkboxes **Count**, **Sum**, **Average**, **Min** and **Max**. Which boxes are offered depends on the field's type: | Field type | Totals offered | | ------------------------- | ----------------------------- | | Number | Count, Sum, Average, Min, Max | | Text, date, date and time | Count, Min, Max | | Yes/no | Count | The hint *Add a number field to calculate totals* appears while no column allows Sum or Average. A total becomes a column of its own in the file, headed *Count of Employee ID* and so on; in the saved definition it is an aggregation aliased `_`. ## The Naming tab

The Naming tab with Excel selected, the pattern {report}_{date}, the insert buttons, the example line Contractor_role_access_2026-10-09.xlsx and the folder Contractor_role_access

\*Name the files this report produces. Placeholders are replaced when the report runs.\* ### File type **CSV** or **Excel**. This is the format of every **Run** and of every scheduled run; **Export** in the row menu can override it for one run. A new report defaults to Excel. PDF is not produced in this release. ### File name and folder The **File name** field, its placeholders and the *Or type an example* helper are described on [Name the output files](/administration-guide/reports/output-file-names.md#naming-pattern). The read-only line **Folder: …** is the folder, named after the report, in which all of this report's files are stored; see [Folder](/administration-guide/reports/output-file-names.md#naming-folder). ## Save, cancel, delete The footer has **Delete** on the left (owner or super administrator, never a template), then **Cancel** and **Save**. Save is disabled while the report is read-only. **Save** sends the whole definition to the report service, which validates it again from scratch — the entities and relationships against the catalog, the operators against the field types, the grouping rule, the formulas, the limits — and refuses with the first error otherwise. On success the toast reads *Report saved*, the report's version counter goes up, and one audit entry `REPORT_UPDATE` records what changed, one attribute per section (fields, calculated fields, filters, sort, grouping and totals, output, retention and so on) with the old and new values. **Cancel** with unsaved edits asks *Discard changes?* — **Discard** leaves, **Keep editing** stays. Leaving by any other route asks the same. **Delete** asks *Delete this report?* — *The report and its schedule are removed. This cannot be undone.* Generated files stay in the [Download center](/administration-guide/reports/run-and-download.md#downloads) until they expire. Delete is refused while a run of the report is queued or running. ### Somebody else saved first Each save carries the version it started from. When another session saved the same report in the meantime, Save fails with *Someone else saved this report in the meantime. Reload it and apply your changes again.* Nothing is written; reload, then repeat your edits. ### Has errors A tab carrying a validation problem shows **Has errors** next to its name, and the summary above the tabs lists every problem. The common ones: | Message | Tab | What to do | | -------------------------------------------- | ----------- | ---------------------------------------------------------------------------------------------- | | Select at least one field | Fields | Move a field to Selected. | | Not grouped: add it to Group by or remove it | Fields | A grouped report cannot show an ungrouped, untotalled column. | | You no longer have access to field … | Fields | The field was locked since the report was saved; remove it or request the entity's permission. | | Choose a data source before adding fields | Data source | The Data source tab is empty. | | Enter a report name | Header | The name is required. | ## The running example Dana opens **Contractor role access** in the designer to add how long each contractor has been away. On the Fields tab she chooses **Add calculated field**: label *Days since login*, key `days_since_login`, Formula mode: {% code overflow="wrap" %} ``` DATEDIFF("day", [login.last_login], TODAY()) ``` {% endcode %} The validator answers *Valid formula; result type: NUMBER*. She moves the new field to Selected, sets its total to **Max** on the Configure tab so the grouped report shows the longest absence per role, and chooses **Save**. The audit entry records *Calculated fields* and *Group by and totals* as the two changed sections. ## Verification * The header shows the new source line and no **Unsaved changes** badge after Save. * On the details page the **General** card lists the entities and the file format you set. * The **Preview** card on the details page renders the new columns, with the calculated field in place and the totals as extra columns. * The audit log (**Audit & Reports → Log Viewer**) shows a `REPORT_UPDATE` entry naming the sections you changed. ## When it goes wrong | Symptom | Likely cause | | ------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------- | | **Save** is disabled | The report is a system template, or you are neither its owner nor a super administrator. Clone it. | | *Someone else saved this report in the meantime* | Another session saved first. Reload and redo the edits. | | *The definition has errors* and a tab says **Has errors** | A validation problem on that tab; the summary names it. | | A field you used is now **Locked** | Its entity's data permission was taken from you. Remove the field or request access. | | **CSV** and **Excel** cards on Configure cannot be clicked | They are placeholders. Set the file type on the Naming tab. | | The Views group under Primary entity says *Available in a later release* | A view cannot be the primary entity of an inline source. Use *Use an existing view* instead. | | The calculated-fields section says *These calculated fields belong to the view* | The report reads a view. Edit the view, or switch to an inline source. | | *This entity is stored in another backend than …* on a related entity | The [one-backend rule](/administration-guide/reports/overview.md#rules-one-backend). | ## Related * [Create a report](/administration-guide/reports/create-a-report.md) — the same choices in wizard form, for a new report * [Views and SQL data sources](/administration-guide/reports/views-and-sql-sources.md) — the visual builder the Data source tab embeds, and the SQL alternative * [Filters and parameters](/administration-guide/reports/filters-and-parameters.md) — the condition editor in section 3 of the builder * [Name the output files](/administration-guide/reports/output-file-names.md) — the File name field on the Naming tab * [Manage reports](/administration-guide/reports/manage-reports.md) — where Edit and Delete also live * [Report builder overview](/administration-guide/reports/overview.md) — the rules and limits the designer enforces --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.openiam.com/administration-guide/reports/design-a-report.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.