Overview

Pivot

Pivot Grid (license feature pivot-grid, Professional tier): generates and runs PIVOT queries server-side starting from a metadata route, with a preview in the Pivot Builder and materialization into a view. This page covers runtime and API; for the UI see View/Pivot builder.

What it does

From a route (md_route_name) you pick the row-axis columns, the column-axis columns (pivot key) and one or more aggregated values.
The backend emits the pivot SQL in the dialect of the route's DBMS, applies the route's filters and sorting and returns rows with dynamic columns.
The result can be materialized as a view and scaffolded as a new route (with an optional menu entry), so lists, charts and reports use it like any table.

Route and license

UI: /<route>/pivot-builder (guard requireFeature: 'pivot-grid').
With a Developer license the route answers with access denied: check features in GET /api/Meta/LicenseStatus.

API (`MetadataProviderService` → `MetaService.*`)

Client method	Endpoint	Returns
`generatePivotQuery(route, rowColumns, columnColumns, valueColumns, aggregateFunction, valueDefinitions?, filterInfo?, sortInfo?, rowColumnOptions?, columnColumnOptions?, topRows=300)`	`MetaService.generatePivotQuery`	`{ ok, query }`: the ready SQL, not executed
`executePivotQuery(route, rowColumns, columnColumns, valueColumns, aggregateFunction, valueDefinitions?, filterInfo?, sortInfo?, maxRows=200, rowColumnOptions?, columnColumnOptions?)`	`MetaService.executePivotQuery`	`{ ok, rows, rowCount }` with dynamic columns (`maxRows` capped at 1000)
`createPivotView(..., targetSchema='dbo', createMenu=false, viewName?, enableDynamicScheduler=false, schedulerFrequency='', topRows=300, overwriteIfExists=false)`	`MetaService.createPivotView`	creates the view, scaffolds it as a route and optionally schedules the refresh

Parameters:

rowColumns / columnColumns: aliases of the route columns (friendly name mc_nome_colonna).
valueColumns + aggregateFunction (SUM | AVG | MIN | MAX | COUNT), or valueDefinitions[] with { alias, aggregateFunction, caption } for a different aggregate per value.
rowColumnOptions / columnColumnOptions: { alias, castDate, groupBy } to treat a column as a date and group it by period on the axes.
filterInfo / sortInfo: same format as the list-grid filters and sorting.
topRows: row limit of the generated query (default 300).

Example:

Snippet 1ts

const res = await metadataProvider.executePivotQuery(
  'orders',        // route
  ['customer'],    // rowColumns
  ['year'],        // columnColumns
  ['amount'],      // valueColumns
  'SUM',           // aggregateFunction
  undefined,       // valueDefinitions

Materialization and refresh

createPivotView creates the view targetSchema.viewName (default dbo), registers it as a metadata route (with createMenu=true it also adds the menu entry) and with enableDynamicScheduler=true + schedulerFrequency creates a scheduler task that rebuilds it periodically (see Scheduling). overwriteIfExists=true replaces an existing view with the same name.

Limits and notes

Dynamic columns depend on the values present in the data at query time: when data changes the view schema changes, hence the scheduled refresh.
maxRows is capped at 1000 in preview; for larger volumes materialize the view and read it through the paged list-grid.
A wrong JSON key in the payload does not return 400: the backend uses defaults (see Troubleshooting).