> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lerian.studio/llms.txt
> Use this file to discover all available pages before exploring further.

# Regulatory mapping reference

> The keys, values, and refusals of the mapping that you send to Reporter for a regulatory document.

The mapping is a YAML document. It says where each value of a regulatory document comes from in your data. To send it, [replace the mapping](https://docs.lerian.studio/en/reference/products/reporter/replace-subscription-mapping.md) of a subscription. Reporter reads the document, checks it against the contract of the pinned version, and stores it.

The contract of a document and its template belong to Lerian. The contract names the sets and fields of the document. The template places each value in the file. This page describes the mapping.

A mapping that Reporter cannot read is refused with HTTP `400` and `RPT-0143`. The detail names the point to correct, for example `query 'saldos_cosif'`. See the [error list](https://docs.lerian.studio/en/reference/products/reporter/reporter-error-list.md).

## Example

***

This mapping fills two sets of CADOC 4010. The query `saldos_cosif` adds up the approved entries of each `route_code` that were created before the end of the period of the parameter `dataBase`. A credit adds its `amount`. A debit subtracts it. The published reference mapping for this document also filters on the BRL asset, on entries that moved a balance, and on rows that are not deleted. It also cuts the end of the period by the business calendar.

```yaml theme={null}
kind: DocumentBinding
contract: br.bcb.cadoc-4010
contractVersion: 1.0.0

queries:
  - name: declarante
    dataSource: midaz_onboarding
    table: organization
    filters:
      - field: deleted_at
        operator: isNull

  - name: saldos_cosif
    dataSource: midaz_transaction
    table: operation
    filters:
      - field: status
        operator: eq
        values: ["APPROVED"]
      - field: created_at
        operator: lt
        values: [{parameter: dataBase, at: end}]
    compute:
      - as: valor_no_razao
        case:
          column: direction
          when:
            credit: {column: amount}
            debit: {negate: amount}
          otherwise: refuse
    aggregate:
      groupBy: [route_code]
      measures:
        - as: saldo
          fn: sum
          of: valor_no_razao

datasets:
  institution:
    from:
      query: declarante
    fields:
      cnpj:
        from: query
        column: legal_document

  cosif_balances:
    from:
      query: saldos_cosif
    fields:
      cosifCode:
        from: query
        column: route_code
      saldo:
        from: query
        column: saldo
```

## How Reporter reads the document

***

* The document must be YAML. An empty document is refused. The top level must be an object of keys and values.
* Each block takes the keys in its table on this page. A block with any other key is refused. The refusal lists the keys that the block takes.
* A known key with no value counts as not written.
* A required text cannot be empty.
* Reporter reads a value as the text that you write. `7` and `"7"` are the same value.
* Reporter accepts YAML anchors and aliases. It refuses the merge key `<<`.
* Two queries cannot share a name. Two sets cannot share a name. Two fields of a set cannot share a name.
* These names must match a query of the mapping: the `from` of a query, the `query` of the `from` of a set, the `query` of a join, and the `query` of a field of a set. A query can be declared after the set or query that refers to it.

## Top-level keys

***

| Key | Type | Required | What it does |
| - | - | - | - |
| `apiVersion` | text | No | Format marker. |
| `kind` | text | No | Format marker. When you write it, the value must be `DocumentBinding`. |
| `contract` | text | Yes | ID of the document that the mapping fills, for example `br.bcb.cadoc-4010`. |
| `contractVersion` | text | No | Version of the contract that you wrote the mapping against. |
| `queries` | list of queries | No | The reads of your data. |
| `datasets` | object of sets | Yes | How each set of the document is filled. `datasets: {}` is accepted and maps nothing. A document with no `datasets` key is refused. |
| `manualOverrides` | list of overrides | No | Values that Reporter checks on save and stores with the mapping. To change a value in a filing, [correct the draft](https://docs.lerian.studio/en/reference/products/reporter/correct-regulatory-report.md). |
| `generatable` | `true` or `false` | No | Your declaration of whether the document can be generated. |
| `incompleteReason` | text | No | Why the document cannot be generated. |
| `incompleteFields` | list of text | No | Fields that have no source, each written as `set.field`. |

## Queries

***

A query reads rows from your data. Sets and other queries refer to a query by its name.

| Key | Type | Required | What it does |
| - | - | - | - |
| `name` | text | Yes | Names the query. |
| `description` | text | No | A description of the query. |
| `dataSource` | text | With `table` | The `configName` of the data source that the query reads. |
| `table` | text | With `dataSource` | Key of the table in that data source. |
| `from` | text | Instead of `dataSource` and `table` | Another query to read. See the constructs that a generation refuses. |
| `expand` | text | No | List inside each document whose items become rows. |
| `filters` | list of conditions | No | Keeps the rows that pass, before grouping. |
| `having` | list of conditions | No | See the constructs that a generation refuses. |
| `join` | list of joins | No | Brings a second table or query next to the rows. |
| `derive` | list of derived columns | No | See the constructs that a generation refuses. |
| `compute` | list of computed columns | No | Adds a column that a case computes on each row. |
| `aggregate` | object | No | Groups the rows and computes measures. |
| `select` | list of text | No | Names the columns that the query returns. |

A query is refused when:

* it has neither `dataSource` nor `from`;
* it has `from` and also `dataSource` or `table`;
* it has `dataSource` and no `table`;
* its `from` names itself, names a query that the mapping does not declare, or closes a ring with other queries.

### Filters

A row stays when it passes each condition of `filters`. Conditions on the same column all apply.

A condition is a test or a combination:

| Key | Type | What it does |
| - | - | - |
| `field` | text | The column to test. |
| `operator` | text | How to compare. |
| `values` | list | What to compare with. |
| `anyOf` | list of conditions | A combination. See the constructs that a generation refuses. |
| `allOf` | list of conditions | A combination. See the constructs that a generation refuses. |

A condition needs `field` and `operator`, or `anyOf` or `allOf`. These operators are accepted:

| Operator | Values |
| - | - |
| `eq`, `in`, `nin` | One or more values, or one parameter. |
| `gt`, `gte`, `lt`, `lte` | One value, or one parameter. |
| `startsWith`, `notStartsWith` | One or more values, or one parameter. Each value is a prefix. |
| `between` | Exactly two values: the bounds. A parameter is not accepted. |
| `isNull`, `notNull` | No values and no parameter. |

To exclude rows by prefix, use `notStartsWith`. This filter drops the rows whose `route_code` starts with `7` or `8`:

```yaml theme={null}
filters:
  - field: route_code
    operator: notStartsWith
    values: ["7", "8"]
```

A condition is refused when:

* it has a key other than `field`, `operator`, `values`, `anyOf`, and `allOf`;
* it has `field` and also `anyOf` or `allOf`;
* it has neither `field` nor `anyOf` or `allOf`;
* it has `field` and no `operator`;
* its `operator` is not in the table. The refusal lists the accepted operators;
* it has `isNull` or `notNull` with values or a parameter;
* it has `between` with a parameter, or with a number of values other than two;
* it has any other operator with no value and no parameter.

Two conditions of `filters` on one column are refused when they use the same operator. They are also refused when one of them is `isNull` or `notNull`. Two different operators on one column combine, which is how you write a period: `gte` for its start and `lt` for its end. A generation refuses `between` beside `gte` or `lte`.

#### Values read from a parameter

A parameter is a value that you send in the generation request. An item of `values` that is an object reads one parameter:

| Key | Type | Required | What it does |
| - | - | - | - |
| `parameter` | text | Yes | Name of the parameter. |
| `at` | `start` or `end` | No | The edge of the period that the parameter names. |
| `of` | `day`, `month`, or `year` | No | The period that the edge belongs to. It needs `at`. |
| `calendar` | `business` | No | Cuts the period by the business days of the contract. |

Without `at`, the condition compares with the value of the parameter as the request carries it. With `at`, the parameter names a period: `AAAA-MM` for a month, `AAAA-MM-DD` for a day. The edges follow the report time zone. `start` is the first instant inside the period. `end` is the first instant after it. Compare `start` with `gte` and `end` with `lt`. No instant then falls in two periods or in none.

With `calendar: business`, a period ends right after its last business day. It starts right after the last business day of the period before.

This query reads the month of the parameter, up to the end of its period:

```yaml theme={null}
filters:
  - field: created_at
    operator: gte
    values: [{parameter: dataBase, at: start, of: month}]
  - field: created_at
    operator: lt
    values: [{parameter: dataBase, at: end}]
```

A parameter value is refused when:

* `values` lists two parameters, or lists a text beside a parameter;
* the object of the parameter has a key other than `parameter`, `at`, `of`, and `calendar`, or has no `parameter`;
* `at` is not `start` or `end`;
* `of` is not `day`, `month`, or `year`;
* `calendar` is not `business`;
* it has `of` or `calendar` and no `at`;
* it has `at: start` with an operator other than `gte`, or `at: end` with an operator other than `lt`.

A generation refuses an `of` that is shorter than the period of the parameter, for example `of: day` on a month.

### Joins

A join brings a table next to the rows of the query. Each item of `join` has these keys:

| Key | Type | Required | What it does |
| - | - | - | - |
| `table` | text | One of `table` or `query` | The table to bring. |
| `query` | text | One of `table` or `query` | The query to bring. See the constructs that a generation refuses. |
| `match` | object | Yes | Pairs a column of this query (key) with a column of the joined table or query (value). |
| `columns` | object | With `table` | The columns that the join brings. Each key is the name that this query uses. Each value is the column of the joined table. Other columns stay out. |
| `type` | text | No | `left` or `inner`. The default is `left`. |

With `left`, each row of the query stays, and the brought columns are empty where no row pairs. With `inner`, the rows without a partner leave. A row with several partners becomes several rows.

A join is refused when:

* it names neither `table` nor `query`, or names both;
* it has no `match`, or `match` has no pair;
* it writes `on` in place of `match`. Write `match`;
* it has a key other than `table`, `query`, `match`, `columns`, and `type`;
* it brings a table and has no `columns`, or `columns` has no pair;
* it brings a query and has `columns`;
* two joins of the query bring the same name, or a join brings a name that it also pairs on in `match`;
* `type` is not `left` or `inner`.

### Computed columns

`compute` adds a column to each row. The steps after it read it like a column of the table: filters, grouping, and sums.

| Key | Type | Required | What it does |
| - | - | - | - |
| `as` | text | Yes | Name of the new column. |
| `case` | object | Yes | Picks the value of the column. |

A `case` has these keys:

| Key | Required | What it does |
| - | - | - |
| `column` | Yes | The column whose value picks the answer. |
| `when` | Yes | An object. Each key is a value of `column`. Each value is an answer. |
| `otherwise` | Yes | The answer for a value that `when` does not list. It is `refuse` or an answer. |

An answer is an object with exactly one key:

| Key | The value of the new column |
| - | - |
| `column` | The value of that column. |
| `negate` | The value of that column with its sign changed. |
| `constant` | The text that you write. |

With `otherwise: refuse`, a row whose value `when` does not list refuses the generation.

A computed column is refused when:

* `compute` has two items with the same `as`;
* an item has a key other than `as` and `case`, or lacks `as` or `case`;
* the `case` has a key other than `column`, `when`, and `otherwise`;
* `column` or `otherwise` is missing, or `when` is missing or empty;
* `otherwise` is a text other than `refuse`;
* `as` is also a name that a join brings or a derive names, or the `case` reads another computed column;
* an answer does not have exactly one of `column`, `negate`, and `constant`, or its value is empty.

### Grouping and measures

`aggregate` folds the rows into one row for each group.

| Key | Type | Required | What it does |
| - | - | - | - |
| `groupBy` | list of text | No | Columns that define a group. Without it, all rows fold into one row. |
| `measures` | list of measures | Yes | Values that Reporter computes for each group. |

A measure has these keys. All three are required:

| Key | What it does |
| - | - |
| `as` | Name of the result. A field reads the result under this name. |
| `fn` | The function. Write `sum`. Reporter refuses another function when it generates a filing. |
| `of` | The column to add up. It can be a column of the table, a joined column, or a computed column. |

A query that groups returns the `groupBy` columns and one column for each measure. A field that reads another column is refused when you save. A generation is refused when a row has no value in a `groupBy` column.

`aggregate` is refused when it has a key other than `groupBy` and `measures`, or `measures` is missing or empty. A measure is refused when it has a key other than `as`, `fn`, and `of`, or lacks one of them. A generation refuses two measures with the same `as`.

### Columns that a query returns

`select` names the columns that a query returns. Without it, the query returns each column and the fields choose. With it, a field that reads another column is refused when you save.

`select` is refused when the query also has `aggregate`, when it is empty, or when it names a column twice or names an empty column.

### Lists inside documents

`expand` names a list inside each document, as a dotted path of the fields of the table. Each item of the list becomes a row. The query reads the fields of the item under the path of the list.

On a PostgreSQL data source, the answer of the save lists `expand` under `unexecuted`, and a generation is refused.

`expand` is refused when the path is empty, starts or ends with a dot, has two dots in a row, or has a space or a `$`. It is also refused on a query that has `from`.

### Constructs that a generation refuses

Reporter reads and stores these constructs. The answer of the save lists them under `unsupported`. A generation of a mapping that holds one is refused.

| Construct | How you write it |
| - | - |
| A query that reads another query | `from` on a query |
| Narrowing after grouping | `having` |
| A derived column | `derive`, with `as`, `from`, and `apply`, all required |
| A join of a query | `query` in a join |
| Combined conditions | `anyOf` or `allOf` in `filters` |

## Sets

***

Each key of `datasets` is the name of a set that the contract declares. Its value has these keys:

| Key | Type | Required | What it does |
| - | - | - | - |
| `from` | object | No | Has one key, `query`. It names the query whose rows become the rows of the set. |
| `rowKey` | object | No | For a set whose rows the catalog publishes. Pairs the field that identifies a published row (key) with the column of the query that identifies the same row (value). |
| `parentMatch` | object | No | For a set that the contract declares as a child of another set. Pairs a field of the parent set (key) with the column of the query that carries it (value). |
| `fields` | object of fields | Yes | Where each field of the set gets its value. It cannot be empty. |
| `also` | list of sources | No | Other sources of the same set. See Consolidation. |

An open set has one row for each row of its query. Reporter sorts the rows by the key fields of the set, as text in byte order. Without `from`, the set has no query rows. If the contract declares the set as one row, its constants and parameters fill that row. When the contract declares a hierarchy for the code of the set, Reporter also adds a row for each ancestor code, with the sum of the rows beneath it. Return the amount posted to each code. Do not return a parent amount that already includes its children, because Reporter adds the children to it.

The rows of a set that the catalog publishes are the published rows, one for each. The mapping gives their values:

* The queries of the set read only the rows whose `rowKey` column holds a published code. When the contract declares a hierarchy for the code, they read the rows whose code falls under a published code. Reporter leaves out a row with another code and does not refuse it. In an item of `also`, Reporter does not leave out a row with another code: it refuses the generation.
* A result row fills the published row of its code. When the contract declares a hierarchy, it also fills each published row above that code. Such a published row reads the sum of the measures of the result rows at and below its code.
* With a hierarchy, return the amount posted to each code. Do not return a parent amount that already includes its children, because Reporter adds the children to it.
* A published row with no result row reads `0` in a measure that a query fills. Its other query columns get no value. A required field that reads one refuses the generation.
* Two result rows with the same code refuse the generation.
* A generation where no result row matches a published row is refused.

A set is refused when:

* its `from` is not an object with a `query` key;
* it has a key other than `from`, `rowKey`, `parentMatch`, `fields`, and `also`;
* `rowKey` or `parentMatch` has no pair;
* it has no `fields`, or `fields` is empty;
* its `from` names a query that the mapping does not declare.

### Fields

A field says where its value comes from. Write one source, or write `byRowClass`.

| Key | Type | What it does |
| - | - | - |
| `from` | text | The origin of the value: `query`, `constant`, `parameter`, `manual`, or `rowSet`. |
| `column` | text | The column that Reporter reads. |
| `query` | text | The query that Reporter reads, when it is not the query of the set. |
| `value` | text | The fixed value. |
| `parameter` | text | The name of the parameter. |
| `pendingReason` | text | Why a manual field has no value. |
| `byRowClass` | list of cases | Chooses the source by the row. |

Each origin needs one thing:

| `from` | Needs | The value of the field |
| - | - | - |
| `query` | `column` | The `column` of the row of the query. In a set whose rows the catalog publishes, `query` names the query to read in place of the query of the set. Write a field's own `query` key only in such a set. A generation is refused when the query returns no such column. |
| `constant` | `value` | The `value`. |
| `parameter` | `parameter` | The value of that parameter in the generation request. When the request leaves it out, Reporter uses the default of the contract. With no default, a parameter that the contract requires refuses the request. Name a parameter that the contract declares. |
| `manual` | `value` or `pendingReason` | The `value`, when you write it. Without a value, the field stays blank for a person to fill in the draft. The `pendingReason` tells that person why. |
| `rowSet` | `column` | The `column` of the published row. |

A field of the contract that the mapping does not name stays blank, and the answer lists it as a gap. Reporter refuses to approve a draft while a required field is blank.

A field source is refused when:

* `from` is missing, or is not one of the five origins;
* it names a `query` and its origin is not `query`;
* the origin is `query` or `rowSet` and there is no `column`;
* the origin is `constant` and there is no `value`;
* the origin is `parameter` and there is no `parameter`;
* the origin is `manual` and there is neither a `value` nor a `pendingReason`;
* the field has a key other than the keys in the table above;
* the `query` that it names is not declared by the mapping.

#### Sources that depend on the row

`byRowClass` is for a set whose rows the catalog publishes. Each case has `when` and the keys of a source. The first case whose `when` matches the published row gives the value. A row that no case matches gets no value from this field. The generation is refused when the contract requires the field.

`when` has two required keys: `rowSet`, the column of the published row, and `equals`, the value that it must have.

This field reads a balance for one class of row and a monthly total for the other. Each class reads its own query:

```yaml theme={null}
saldoDia:
  byRowClass:
    - when: { rowSet: measure, equals: stock }
      from: query
      column: saldoDia
    - when: { rowSet: measure, equals: flow }
      from: query
      query: volumes_fluxo
      column: volumeMes
```

A `byRowClass` is refused when the field also has a `from` of its own. A case is refused when it has no `when`, when `when` has a key other than `rowSet` and `equals`, when `rowSet` or `equals` is missing, or when it has a key other than `when` and the keys of a source.

### Consolidation

`also` adds sources to a set. Each item maps the same set over another query, for example another database of the filing institution. Reporter does not limit the query of an item to the filing institution. The filters of that query must choose the rows of the filing institution. The contract of the document says how Reporter joins the rows of the sources, and which sets accept `also`.

An item has the keys `from`, `rowKey`, and `fields`. It has no `parentMatch` and no `also`. Its `from` is required. Its `fields` use the keys of the fields of a set. An item with no `fields`, or with empty `fields`, maps nothing, and each field it does not map is a gap.

Reporter refuses an item whose `from` is also the `from` of the set or of another item. It refuses an item that reads a query that the mapping of any set reads. It also refuses an item that maps a field that the set's own mapping does not map. Give each item its own queries.

## Manual overrides and declarations

***

Reporter checks these keys when you save the mapping, and stores them with it.

Each item of `manualOverrides` has these keys:

| Key | Type | Required | Meaning |
| - | - | - | - |
| `field` | text | Yes | The field, written as `set.field`. |
| `value` | text | Yes | The value. |
| `reason` | text | No | Why you wrote it. |

An item is refused when it has a key other than these three, or when `field` or `value` is empty.

`generatable`, `incompleteReason`, and `incompleteFields` hold your declaration about the document:

* `generatable` must be `true` or `false`.
* `generatable: false` is refused when `incompleteReason` is missing.
* `incompleteFields` is refused when it lists a field and `generatable` is not `false`.

## Checks against the contract

***

After the document is read, Reporter compares it with the contract of the pinned version. The save is refused when:

| The mapping | What you change |
| - | - |
| Names a `contract` other than the document of the subscription. | Write the ID of the document. |
| Fills a set or a field that the contract does not declare, or maps a field that the contract derives from another field. | Use the sets and fields of the contract. Map the field that the derived field comes from. |
| Compares a column with a parameter that the contract does not declare, uses `at` on a parameter that is not a month or a date, or uses `calendar: business` when the contract has no calendar. | Use a parameter of the contract. |
| Reads a column from a query that groups, in a field, `rowKey`, or `parentMatch`, and the query does not return it. | Name a `groupBy` column or the `as` of a measure. |
| Fills a set whose rows the catalog publishes from a query and has no `rowKey`, or pairs a field other than the one that identifies the rows. | Pair the field that identifies the published rows. |
| Uses `rowKey` or `from: rowSet` on a set with no published rows. | Remove `rowKey`, and read each value from another origin. |
| Fills a child set from a query and does not pair each key of the parent in `parentMatch`, or uses `parentMatch` on a set that is not a child. | Pair each key of the parent. |
| Uses `also` on a set that the contract does not declare for consolidation. | Remove `also`. |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.