Integration Guide — Dynamic Reports

Microservice: XDPeople.Soba.Report.WebAPI
Authentication: Bearer Token (JWT) or API Key header X-API-KEY

Authentication Requirement

To successfully follow this guide using JWT authentication, you must authenticate using the following endpoint:

POST /gateway/auth/combined-login

Learn more...

---

Introduction

This guide describes how to integrate with the dynamic reports API: reports created by the user, composed of a SQL SELECT query, a list of columns (fields), and parameters (parameters). The API exposes operations to validate the SQL without persisting, create or update the report (saved configuration), retrieve configuration and data at runtime, and execute ad hoc SQL (with the same security rules).

In contrast to Standard Reports (fixed catalog of pre-configured reports existing in XD GC), dynamic reports allow creating and modifying the SQL query itself (within the security constraints described below), maintaining column and parameter metadata aligned with the validation result.

This document is organized for step-by-step reading and tool reference: first the SQL concepts and placeholders, then the flows (create, update, execute), the parameter behavior in the interface, the jsonParameters format, the endpoints reference, and the DTO structure (including canonical values of fieldType / parameterType), examples, and errors. See Document Map.

---

When to Use

• Create or modify custom reports via the API or a partner application.
• Validate a query before saving (automatic discovery of columns and placeholders).
• Retrieve the report configuration (columns, parameters for the interface) and data filtered by parameters.
• Preview query data with execute-sqlquery (without creating a report).

---

Document Map

1. SQL Concepts — security restrictions, @WHERE / @OTHERS placeholders, @p_ pattern. Values of fieldType / parameterType: Type Contract; other fields and enums: DTO structure and OpenAPI documentation in Available Endpoints.
2. Implementation Flows — creation, update, and execution (including parallel with Standard Reports); section Implementation Flows.
3. Parameters: configuration and visualization — enrichment after verify-sqlquery, Lookup, UI impact; section Parameters: configuration and visualization logic.
4. jsonParameters — JSON object in the query string (GET / POST); section Query parameter jsonParameters.
5. Reference — Available Endpoints, DTO structure, JSON examples, error handling, related resources.

---

SQL and Security Rules

The API does not accept write or administrative commands in the SQL sent for validation or execution. Practically, among other rejections:

• INSERT, UPDATE, DELETE statements
• DDL (DROP, ALTER, CREATE, TRUNCATE, etc.)
• Execution of dangerous procedures or commands (EXEC, EXECUTE, sp_ / xp_ extensions, etc.)

The main query must be read-oriented (SELECT and associated clauses). Error messages returned by the API indicate validation failures or database errors during the limited test.

---

Query Composition: @WHERE and @OTHERS

The internal model concatenates sqlQuery with the filter and extra clauses:

• The sqlQuery text may include the placeholders @WHERE and @OTHERS, which are replaced by the contents of whereClause and otherClauses, respectively.
• If they are not present in the sqlQuery, the mapping from SqlQueryDTO appends them at the end of the main query, so that whereClause and otherClauses always take effect.

Ensure that the final SQL remains syntactically valid after substitution (for example, whereClause usually starts with AND ... or WHERE ..., depending on what already exists in the sqlQuery).

---

Parameters in SQL: @p_ Pattern

The placeholders recognized by the dynamic reports engine follow the regular expression @p_ followed by an identifier (e.g., @p_dataInicio, @p_entidadeId).

• Always use this prefix (@p_) in parameter names in the SQL.
• The names returned in parameters by validation match these placeholders.
• In the requests GET /gateway/dynamic-reports/{guid}/data and POST /gateway/dynamic-reports/execute-sqlquery, the JSON in jsonParameters must use the same keys as the parameter names (e.g., {"@p_dataInicio":"2025-01-01"}).

---

Implementation Flows

The recommended behavior follows two distinct paths: creating a new report and updating an existing one. The update does not repeat the entire creation text: first, the identifier is obtained from the catalog and the full configuration is loaded; then the same validation and metadata merging steps described for creation are applied.

Runtime Execution (same model as standard reports)

To execute an existing report, the client flow is the same as in Standard Reports: catalog → information to build the screen (columns and parameters) → GET request with jsonParameters in the query string (serialized and URL-encoded JSON object, with all mandatory parameters). The contract of jsonParameters and encoding mode are equivalent; only the URLs differ.

Step	Standard Reports	Dynamic Reports
Catalog	GET /gateway/reports	GET /gateway/dynamic-report
Configurations	GET /gateway/reports/{reportId}/info	GET /gateway/dynamic-reports/{guid}/config
Get data / execution	GET /gateway/reports/{reportId}?jsonParameters=…	GET /gateway/dynamic-reports/{guid}/data?jsonParameters=…

In dynamic reports, the keys of the JSON follow the @p_… placeholders of the SQL (as returned in the configuration), just as in standard reports the keys indicated by the paramHolder in the info step are used.

Creating a New Report

Diagram only for the first save flow (without listing or PUT).

1. Build the SqlQueryDTO body with sqlQuery, whereClause, and otherClauses.
2. Call POST /gateway/dynamic-reports/verify-sqlquery to confirm the query is valid and obtain fields and parameters with normalized types (fieldType / parameterType; allowed values in Type Contract).
3. For each returned column or parameter, if there is already saved configuration with the same name, preserve captions, visibility, formatting, pivot area, value lists, etc.; otherwise, use the object returned by validation (in pure creation, usually only what validation returned).
4. Send POST with complete DynamicReportDataDTO and save the report guid for subsequent requests.
5. At runtime: config to build the parameter screen; data with jsonParameters (serialized and URL-encoded JSON in the query string, as in standard reports).

Updating an Existing Report

To modify an already saved report, it is not enough to send PUT without context: you need to know which report to change. The typical flow is:

1. List the dynamic reports available to the user via GET /gateway/dynamic-report (optional pagination with page and pageSize). — See more →
2. Select the guid identifier of the desired report from the response.
3. Retrieve the full configuration with GET /gateway/dynamic-reports/{guid} (DynamicReportDataDTO), to start from the current version saved in the report service.
4. Apply the desired changes (e.g., to the SQL or metadata).
5. Repeat steps 2 and 3 of creation: POST /gateway/dynamic-reports/verify-sqlquery whenever sqlQuery, whereClause, or otherClauses have changed compared to the last successful validation; merge fields and parameters by name as in creation (preserve captions, pivot, value lists, etc.).
6. Send PUT /gateway/dynamic-reports with the same guid in the body and the updated DynamicReportDataDTO.

Summary diagram of the update (cross-reference to the creation flow in validation and merging steps):

Catalog and full saved configuration

GET /gateway/dynamic-report returns the catalog (paginated list of dynamic reports). GET /gateway/dynamic-reports/{guid} returns the full saved configuration (DynamicReportDataDTO), necessary to build the body of the PUT.

Revalidate after changing SQL

If sqlQuery, whereClause, or otherClauses change compared to the last successful validation, call POST /gateway/dynamic-reports/verify-sqlquery again before saving (POST or PUT), to synchronize columns, parameters, and types.

---

Parameters: Configuration and Visualization Logic

Besides the @p_... placeholders in the SQL, each parameter saved in DynamicReportParameterDTO defines how the value is chosen and which UI control the client should show to the end user. The server converts this combination into a presentation mode (ViewType in ReportParameterDetail), returned in GET /gateway/dynamic-reports/{guid}/config.

After SQL Validation

The response of POST /gateway/dynamic-reports/verify-sqlquery includes an entry in parameters for each @p_... found, usually only with name (equal to the placeholder) and initial parameterType. In creating or updating the report, each parameter should be enriched with:

Field	Purpose
name	Same as the placeholder in SQL (e.g., @p_dataInicio). It is also the key in jsonParameters during report execution.
caption	Text shown to the user (field label).
parameterType	Logical type (string, number, date, datetime, boolean, etc.) — see Type Contract.
entityType	Optional. Identifies a business entity for assisted lookup (customer, item, etc.), among the values accepted by the product in GC.
defaultValue / defaultValueType	Initial value (None, fixed value SpecifyValue, or date function DateTimeFunction).
availableValueType	Source of selectable values: no list (None / 0), fixed list (ValuesList / 1), SQL list (QueryResult / 2).
availableValuesList	List of { value, description } when availableValueType = fixed list.
query	Auxiliary SQL when availableValueType = SQL list (feeds the selection control).

Special Rule: Entity Lookup (Lookup)

If entityType is filled and availableValueType is None (i.e., not using fixed list or SQL list), the parameter is treated as an entity assisted lookup: the user selects a record (e.g., customer or item) through the product's lookup flow, instead of a free text field.

If availableValueType is ValuesList or QueryResult, the fixed list or SQL list prevails; in these cases, the entity mode does not apply this Lookup rule in the same way.

UI Impact: parameterType × availableValueType

When not in the entity lookup mode above, the typical combination is:

parameterType	availableValueType	Expected UI behavior
string	None	Text box
string	ValuesList	Dropdown list with entries from availableValuesList
string	QueryResult	List populated by SQL in query
number	None	Numeric input
number	ValuesList / QueryResult	List with numeric values (fixed or from query)
date or datetime	None	Date (or date and time) picker
date or datetime	ValuesList / QueryResult	List of dates or list from query
boolean	None	Yes/no control
boolean	ValuesList / QueryResult	Options list or list from query

In practice, the client consumes ReportParameterDetail.viewType (and fields like valueList, query, entityTypeValue, paramHolder) returned by the report configuration to render the correct control.

Where to See the Result for Integration

• GET /gateway/dynamic-reports/{guid}/config — returns the configuration already with detailed parameters to build the form before calling GET /gateway/dynamic-reports/{guid}/data.
  See more →
• POST /gateway/dynamic-reports/parameters — sends a list of DynamicReportParameterDTO and receives the corresponding ReportParameterDetail without saving the report (useful to preview UI behavior).
  See more →

---

Query Parameter jsonParameters

To get data (GET /gateway/dynamic-reports/{guid}/data) and to execute SQL (POST /gateway/dynamic-reports/execute-sqlquery), the optional query parameter jsonParameters must be a JSON object serialized as text and URL-encoded (just like in Standard Reports): keys equal to the placeholder names (@p_...), values in the expected format for the type.

Important

Send jsonParameters as JSON text encoded in the URL query string. HTTP libraries usually handle serialization and URL encoding automatically.

---

Available Endpoints

List Dynamic Reports (Catalog)

URL: /gateway/dynamic-report
Method: GET
Query (optional): page, pageSize — pagination

Returns the paginated list of dynamic reports accessible to the authenticated context. It is the usual step before GET /gateway/dynamic-reports/{guid} in the update flow.
See more →

---

Operations under /gateway/dynamic-reports

The following requests use this prefix. All require authentication.

Test SQL Validity (without saving)

URL: /gateway/dynamic-reports/verify-sqlquery
Method: POST
Body: SqlQueryDTO

Performs security validations and a test execution to infer columns and parameters.

Field	Type	Required	Description
sqlQuery	string	Yes	Main SELECT statement (may include @WHERE and @OTHERS).
whereClause	string	No	Filter replaced in @WHERE.
otherClauses	string	No	Text replaced in @OTHERS (e.g., GROUP BY, ORDER BY).

Response (200): object with lists fields and parameters (normalized types).
See more →

---

Create Dynamic Report

URL: /gateway/dynamic-reports
Method: POST
Body: DynamicReportDataDTO

Response: 204 No Content on success.
See more →

---

Update Dynamic Report

URL: /gateway/dynamic-reports
Method: PUT
Body: DynamicReportDataDTO (include the same guid)

Response: 204 No Content on success.
See more →

---

Delete Dynamic Report

URL: /gateway/dynamic-reports/{guid}
Method: DELETE

Response: 204 No Content on success.
See more →

---

Get Saved Report (Full Configuration)

URL: /gateway/dynamic-reports/{guid}
Method: GET

Response (200): DynamicReportDataDTO (full data as saved).
See more →

---

Get Configuration for Presentation (Columns and Interface Parameters)

URL: /gateway/dynamic-reports/{guid}/config
Method: GET

Response (200): ReportConfig (columns, parameters with details for the screen, final query, etc.).
See more →

---

Get Report Data

URL: /gateway/dynamic-reports/{guid}/data
Method: GET
Query (optional): jsonParameters — JSON with values of @p_... parameters

Equivalent, in the integration model, to GET /gateway/reports/{reportId}?jsonParameters=… in standard reports; see Runtime Execution and Standard Reports — Step 3.

Response (200): ResultReport with count and rows.
See more →

---

Execute a SQL Query (without saved report)

URL: /gateway/dynamic-reports/execute-sqlquery
Method: POST
Query (optional): jsonParameters
Body: SqlQueryDTO

Useful for preview; subject to the same security rules as POST /gateway/dynamic-reports/verify-sqlquery.
See more →

---

Parameter Details from DTOs

URL: /gateway/dynamic-reports/parameters
Method: POST
Body: list of DynamicReportParameterDTO

Response (200): list of ReportParameterDetail (presentation / integration).
See more →

---

Main DTO Structure

Sections follow data composition from part to whole: SqlQueryDTO, element of each column (DynamicReportFieldDTO), element of each parameter (DynamicReportParameterDTO), shared canonical values (fieldType / parameterType), and finally the saved document (DynamicReportDataDTO).

SqlQueryDTO

Property	Type	Required	Description
sqlQuery	string	Yes	Main query.
whereClause	string	No	Filter clause.
otherClauses	string	No	Other SQL clauses.

DynamicReportFieldDTO

Property	Type	Description
name	string	Column name (must match SQL result).
caption	string	Caption shown to the user.
fieldType	string	Canonical column type in the API — allowed values in Type Contract (below).
entityType	string	Entity type for navigation (optional).
entityLink	object	Entity link configuration (optional).
area	enum	Pivot semantics not applicable while isPivotReport is false (recommended). Expected values for future support (PivotFieldArea): Column (0), RowArea (1), DataArea (2), FilterArea (3).
formatting	string	Presentation mask / formatting.
isVisible	boolean	Column visible in the result.

DynamicReportParameterDTO

Property	Type	Description
name	string	Placeholder in SQL (e.g., @p_inicio).
caption	string	Parameter caption.
parameterType	string	Canonical parameter type in the API — same allowed values as for fieldType; see Type Contract.
entityType	string	Entity type for assisted lookup (optional).
defaultValue	object	Default value.
query	string	SQL for value list (AvailableValueType = query result).
defaultValueType	ParamDefaultValue	Default value mode.
availableValueType	ParamAvailableValue	Source of possible values.
availableValuesList	list of ParamListValue	Fixed list when applicable.

Type Contract: fieldType and parameterType

After knowing the properties fieldType and parameterType in the tables above, use only strings with one of the following canonical lowercase values (same for columns and parameters):

API Value	Meaning
string	Text
number	Numeric values
boolean	True / false
datetime	Date and time
date	Date (domain date type)
object	Generic object

DynamicReportDataDTO (most relevant fields)

Property	Type	Required	Description
guid	string	On update	Unique identifier (GUID).
name	string	Yes	Short name (max. 25 characters).
description	string	Yes	Description (max. 250 characters).
query	SqlQueryDTO	Yes	SQL composition.
fields	list of field	Yes	Report columns.
parameters	list of parameter	No	SQL parameters.
permissionType	int	No	Permission model.
permissionGroupsId	list of int	Conditional	Group IDs if permissionType = groups.
reportType	int	Yes	Report type in the system.
isPivotReport	boolean	No	Recommended: false. Pivot mode not supported by the API at this time.
dataAccess	string	No	Data access configuration.
addToRibbon	boolean	No	Show in menu.
showInPos	boolean	No	Show in POS.
userId	int	No	Associated user (permissions for "current user").

---

JSON Examples

Request to POST /gateway/dynamic-reports/verify-sqlquery

{
  "sqlQuery": "SELECT Id, KeyId, GroupId, Description, NetPrice1, RetailPrice1 FROM items",
  "whereClause": "WHERE (@p_itemgroupId IS NULL OR GroupId = @p_itemgroupId)",
  "otherClauses": "ORDER BY KeyId DESC"
}

Response

{
  "fields": [
    {
      "name": "Id",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "KeyId",
      "caption": null,
      "fieldType": "string",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "GroupId",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "Description",
      "caption": null,
      "fieldType": "string",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "NetPrice1",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "RetailPrice1",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    }
  ],
  "parameters": [
    {
      "name": "@p_itemgroupId",
      "caption": null,
      "parameterType": "string",
      "entityType": null,
      "defaultValue": null,
      "query": null,
      "defaultValueType": 0,
      "availableValueType": 0,
      "availableValuesList": []
    }
  ]
}

Body for creation (POST /gateway/dynamic-reports)

Pivot mode

Always use isPivotReport: false. The API does not yet support pivot in dynamic reports; the area fields in the examples are neutral values (0) required by the contract.

{
  "guid": "d972a28e-4f6a-47b7-9342-650b7758d0a5",
  "name": "Report test docs",
  "description": "Report test docs desc",
  "query": {
    "sqlQuery": "SELECT Id, KeyId, GroupId, Description, NetPrice1, RetailPrice1 FROM items",
    "whereClause": "WHERE (@p_itemgroupId IS NULL OR GroupId = @p_itemgroupId)",
    "otherClauses": "ORDER BY KeyId DESC"
  },
  "isPivotReport": false,
  "addToRibbon": true,
  "showInPos": true,
  "permissionType": 0,
  "permissionGroupsId": [],
  "reportType": 2,
  "dataAccess": "",
  "userId": 0,
  "fields": [
    {
      "name": "Id",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "KeyId",
      "caption": null,
      "fieldType": "string",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "GroupId",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "Description",
      "caption": null,
      "fieldType": "string",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "NetPrice1",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    },
    {
      "name": "RetailPrice1",
      "caption": null,
      "fieldType": "number",
      "entityType": null,
      "entityLink": null,
      "area": 0,
      "formatting": null,
      "isVisible": true
    }
  ],
  "parameters": [
    {
      "name": "@p_itemgroupId",
      "caption": "Family",
      "parameterType": "string",
      "entityType": "itemsgroups",
      "defaultValue": null,
      "query": null,
      "defaultValueType": 0,
      "availableValueType": 0,
      "availableValuesList": []
    }
  ]
}

(The exact values of fields and parameters should be aligned with the result of POST /gateway/dynamic-reports/verify-sqlquery and with your installation's business rules.)

---

Error Handling

Operations return problem responses (Problem Details) or error payload aligned with the API's Result pattern when the operation fails (SQL validation, permissions, report not found, etc.).

The integration client should:

1. Check the HTTP status code (4xx / 5xx).
2. Read the error response body (code, type, message) and act accordingly (correct the SQL, authentication, or sent data).

This guide does not list fixed error codes: consult the returned message and the documentation of the operation in question.

---

Conclusion

This guide summarizes two paths: creation (POST /gateway/dynamic-reports/verify-sqlquery, metadata merging, POST /gateway/dynamic-reports to save, then config / data) and update (catalog at /gateway/dynamic-report, GET /gateway/dynamic-reports/{guid}, repeat POST /gateway/dynamic-reports/verify-sqlquery and merge as in creation, PUT /gateway/dynamic-reports). In both cases, respect the @p_ prefix, the @WHERE / @OTHERS placeholders, and the canonical strings of fieldType / parameterType defined in the Type Contract. For trials without saving, use POST /gateway/dynamic-reports/execute-sqlquery.

Exhaustive testing in a quality environment before production is recommended, including invalid SQL, missing parameters, and schema changes.

For dynamic charts built on dynamic reports, see the Integration Guide — Dynamic Charts.

---

Related Resources

• List Dynamic Reports (pagination)
• Test SQL Query Validity
• Create Dynamic Report
• Update Dynamic Report
• Delete Dynamic Report
• Get Saved Report by GUID
• Get Report Configuration
• Get Report Data
• Execute SQL Query
• Parameter Details
• Integration Guide — Standard Reports
• Getting Started / Authentication

---

Last updated: May 12, 2026