# Datasets

> How a dataset selects value types and dimensions for an app, and how spreading, initial values, locked values, filters, expressions and row-level access shape that data.

A dataset is a named, dimensioned slice of plan data that app components read from. It contains one or more value types and a dimension set: all, or a subset of, the dimensions that the value types share. A dataset belongs to one app. Every table, chart and metric in the app points to exactly one dataset.

The dataset also holds most of the app's data rules (aggregation and spreading, input rows, locks, computed rows), which apply to every component that uses it.

In the app editor, each dataset has two parts: **value type cards** and a **dimensions matrix**.

## Value types in a dataset

Each value type in the dataset has a card showing its position, ID, aggregation (*Aggregering*) and spread (*Fördelning*). Value types are added with **Add value types to dataset** (*Lägg till värdetyper till dataset*), or created on the spot with **New value type** (*Ny värdetyp*). The card footer lists the dimensions included (*Dimensioner inkluderade*).

A value type can also get a display name for this dataset (`NameOverride`), a condition and default values for excluded dimensions, all described below.

### Aggregation

`Aggregation` is `Sum`, `Min`, `Max`, `Avg` or `Disabled`. `Disabled` means no aggregation at all, which suits dates, comments and text. A component can override the aggregation for its own view, per value type and per dimension. See [Workspaces and components](components.html#value-type-overrides).

### Spreading

When a user edits an aggregated cell, such as a yearly total over twelve months, the new value is spread down to the stored cells below it. The spread type decides how:

| Code | Name | Behaviour |
|---|---|---|
| 0 | `Proportional` | keeps the current proportions between the underlying cells (*Proportionell*) |
| 1 | `Even` | spreads evenly; the sum becomes the entered value |
| 3 | `SetValue` | sets the entered value on **every** affected cell; correct for averages and prices, but changes the total for summed value types |
| 4 | `Locked` | does not spread; the aggregate cannot be changed by spreading |

Code 2 is not used. The same codes are used for writeback expressions (see [below](#writeback)) and for component-level overrides.

!!! tip "Choose spread by what the number means"
    Use `Proportional` or `Even` for additive values such as `Amount` or `Headcount`. Use `SetValue` for rates and unit values such as `Price`, where typing 100 on a year should mean 100 in every month, not 100 in total.

### Dimensions removed by aggregation

A value type can have more dimensions than the dataset shows. The card then shows *Dimension removed by aggregation: <dimension>* (*Dimension borttagen genom aggregering*), and the dataset shows the value type aggregated over that dimension.

**Set default values** (*Sätt standardvärden*) gives a default code for each excluded dimension (`ExcludedDimensionValueDefaults`). The defaults are used when data is spread on updates, so that entered values have a code for the dimensions the dataset does not show.

### Value type condition

A value type can be restricted with a **condition**: a Kex filter that limits which rows of that value type the dataset loads. Open the card menu (⋯) and choose **Set filter** (*Ställ in filter*). A filtered card carries the chip **Filtered** (*Filtrerad*).

The panel has two views of the same condition:

- a **guided form**: field, operator and value;
- a **code view** behind **Show advanced** (*Visa avancerat*), where the condition is written as Kex.

The two views convert both ways. Writing `Amount > 100000` in the code view shows up in the form as *Value · Greater than · 100000*.

The condition is the body of a Kex `where` clause, without the keyword:

```kex
Amount > 100000 && Account in {"4010", "4020"}
```

Rules:

- **No subqueries.**
- **Scoped to the value type.** The condition can reference the value type and its own dimensions only, not other value types.
- **Type-checked at validation.** An invalid condition fails with `InvalidValueTypeCondition` and a path to the failing part of the configuration.

The panel has a **Preview** (*Förhandsgranska*) that runs the query against real data. It only updates once the condition has been applied in the panel.

## The dimensions matrix

The dimensions matrix lists the dataset's dimensions with their properties nested below them, followed by a **Global** section with three rows: **Value types** (*Värdetyper*), **References** (*Referenser*) and **Plan data** (*Plandata*). The Global rows are used as sources for initial values and locked values (see below).

The columns are:

| Column | Swedish label | Purpose |
|---|---|---|
| Initial values | *Initiala värden* | expansion rules, organised in sets |
| Locked values | *Låsta värden* | lock rules, organised in sets |
| Data filter | *Datafilter* | dataset filter |
| Expressions | *Uttryck* | computed dimension values |
| Display as | *Visa som* | display format |
| Sort by | *Sortera efter* | sort key and method |

A cell shows a badge with the number of selected values, or **+** when empty. The checkbox in the left column controls whether a property is exposed as a column in the dataset. It is independent of the rule columns: a property can drive initial values or locks without being visible to the user.

### Display as

**Display as** sets how dimension values are labelled. The default is *Inherited from dimension* (*Ärvd från dimension*) or *Inherited from property* (*Ärvd från egenskap*), that is, the format set on the dimension itself. Display formats are configurable per tenant. The four standard formats are:

| ID | Format |
|---|---|
| 1 | `{Code} - {Name}` |
| 2 | `{Name} - {Code}` |
| 3 | `{Code}` |
| 4 | `{Name}` |

### Sort by

**Sort by** chooses the sort key, **Code** or **Name**, and the method: `Natural` (default; "Month 2" before "Month 10") or `Alpha` (plain character order). The GUI shows, for example, *Name (natural)* (*Namn (naturlig)*). Value types and data types have their own sort settings.

## Initial values

A value type only contains the dimension combinations that have data, sometimes none at all. To plan, users need rows to type into before data exists. **Initial values** (*Initiala värden*) define how data is expanded, cartesian when several dimensions are involved, to create these placeholder rows.

Initial values are a **generating** mechanism, not a filter. Valid dimension combinations are not maintained as a separate register. They are borrowed from data that already exists (the dataset's own value types, a reference or another value type) and expanded against the dimensions you want to plan in.

Placeholder cells are shown with the line state *Initial values* (*Initialvärde*) in the Inspect panel until someone enters a value. Presentation rules can style them separately.

### Sets

Initial values are organised in **sets** (*SET 1*, *SET 2* …; add more with **+**). Each set is a separate rule:

- **Within a set**, the result is expanded as a cartesian product across dimensions. Several selections within the same dimension (for example two properties, or a property and dimension values) give the intersection of those selections. The editor marks this with an `&` badge and the tooltip *"An intersection of the selected values in the dimension will be used"* (*Ett snitt av de valda värdena i dimensionen kommer att användas*).
- **Across sets**, each set is expanded on its own and the result is the union of all sets.

### Value sources per dimension

For each dimension or property in a set, the values can come from three sources. In the panel, choose explicit values, or **All values in** (*Alla värden i*) with the option **Property** (*Egenskap*) or **Dataset**.

| Source | Configuration | Values used |
|---|---|---|
| Specified values | `SpecifiedValues` | exactly the selected codes |
| All values in dimension or property | `AllValuesInDimension` | every value in the dimension or property, whether or not it occurs in the dataset |
| Values in dataset | `ValuesInDataset` | only the values that occur in the dataset's data |

Expanding over *all* values of a large dimension can create many rows.

### Global sources

The three Global rows add sources of dimension combinations to a set:

| Row | Meaning |
|---|---|
| **Value types** | which of the dataset's value types get placeholders; ⚹ means all value types (`ExpandAllValueTypes`) |
| **References** | expand from a reference: *"reference data as a set of initial values based on the reference's own plan data, shown in addition to data from this dataset"* |
| **Plan data** | expand from other value types outside the dataset; each selected value type must contain **all** dimensions of the dataset |

### Example

A dataset has `Amount` on `Account` × `Department` × `Month`. The dimension `Month` has a property `OpenForPlanning`, and there is a reference with last year's actuals.

| Set | Selections | Result |
|---|---|---|
| SET 1 | Value types = `Amount`; References = actuals; `Month.OpenForPlanning` = `1` | one input row for every `Account` × `Department` combination found in the actuals, in every month open for planning |
| SET 2 | Value types = `Amount`; `Account` = `4010`, `4020`; `Department` = all values in dimension; `Month.OpenForPlanning` = `1` | rows for two specific accounts in every department, in every open month |

The app shows the union of both sets, without anyone maintaining a list of valid combinations.

### Dimensions a set does not name

A set expands only the dimensions it names. Every other dimension is taken from the **combinations that already exist in the data**, not from all values of the dimension. For example, a set that names three accounts in a dataset with two existing department-and-month pairs gives 3 × 2 rows. Adding *All values in dimension* for `Month` to the set expands months fully as well.

Rows created by initial values are placeholders: they show 0 and are not stored until a user enters a value.

### Initial values and performance

Initial values do not change how much data is loaded from the server. The expansion runs in the client and is fast, even for very large datasets. What initial values do change is the **size of the dataset the tables are built from**: every placeholder row is a row in the client. Large expansions therefore slow down rendering somewhat and increase memory use considerably.

!!! tip "Keep expansion sets tight"
    Expand only the combinations users actually plan in. Scope sets with properties and flags (active employees, open months, projects in planning) rather than *All values in dimension* on large dimensions.

## Locked values

**Locked values** (*Låsta värden*) mark cells that users cannot edit. They use the same set structure as initial values, without the expansion sources: within a set the selections combine (intersection within a dimension), and each set locks its own cells. The result is the union of the cells all sets point to.

Example:

| Set | Selections | Locks |
|---|---|---|
| SET 1 | `Month.Closed` = `1` | all closed months, for every value type |
| SET 2 | Value types = `AmountActual`, `HeadcountActual` | the two actuals value types, in every month |

Locked cells are shown with a grey background, and hovering or right-clicking shows *"This value is read-only (Locked by Locked Values)"*. Reference data is always read-only in apps, so a data-type selection in a lock rule is ignored. See [Using apps](end-user.html#why-a-cell-is-read-only).

## Dataset filter

The **data filter** (*Datafilter*) restricts the dataset to selected dimension values or property values, for example only departments in one region. Data outside the filter is not part of the dataset, so the filter applies to every component that uses it, unlike component filters. See [Workspaces and components](components.html#component-filters).

## Expressions

An **expression** (*Uttryck*) creates a computed dimension value in the dataset. It can be placed on any dataset dimension:

- on the value type dimension, it adds a computed "value type" row or column such as `Amount per head`;
- on `Month`, it adds a computed period such as `Q1` = January + February + March;
- on a property, it adds a computed property value.

The expression returns a value for every combination of the other dimensions. Structurally, expression rows equal stored rows.

### Expression settings

| Setting | Swedish label | Configuration | Values |
|---|---|---|---|
| Code | *Kod* | `DatasetDimensionValueCode` | code of the computed value |
| Name | *Namn* | `DatasetDimensionValueName` | display name |
| Format | *Format* | `Format` | see table below |
| Decimals | *Decimals* | `Precision` | decimals shown |
| Aggregation | *Aggregering* | `Aggregation` | `Sum` · `Min` · `Max` · `Avg` · `Quot` · `Disabled` |
| Omit null rows | *Utelämna null-rader* | `OmitNullRows` | when on, rows or columns where the expression is null are removed from the grid structure |

| Format code | Meaning |
|---|---|
| 1 | Number |
| 2 | Percent |
| 3 | Date |
| 4 | Date and time |
| 6 | Text |

`Quot` exists only for expressions. It is intended for ratios, which cannot be summed up a hierarchy. The GUI shows it as *Weighted average* (*Viktat snitt*). On subtotal and total rows, `Quot` divides the aggregated numerator by the aggregated denominator: for price = amount / quantity, a total is sum(amount) / sum(quantity), not the sum or average of the row ratios.

### Standard and LegacyQuot

| Type | Meaning |
|---|---|
| `Standard` | a Kex Lite expression (v2 plans) |
| `LegacyQuot` | a numerator/denominator pair from v1 plans; in v2 the same thing is written as a `Standard` expression |

### Kex Lite and the generated context

Expressions are written in **Kex Lite**, the expression form of Kex. See the [Kex Lite reference](https://acme.konstrukt.app/api/documentation/kex-lite). The editor shows a collapsed `// Context:` block at the top. Expanded, it shows how the expression is evaluated:

```kex
// Context:
dim DataType   = $slice.DataType   limit 1
dim Department = $slice.Department limit 1
dim Month      = $slice.Month      limit 1

var ValueType = pivot($slice, "ValueType")

if (ValueType.Headcount == 0, null, ValueType.Amount / ValueType.Headcount)
```

- `$slice` is the cell's slice of the data.
- Every other dimension is bound to a single value (`limit 1`), including `DataType` (plan data or a reference).
- The dimension the expression sits on is **pivoted**, so its members become named fields (`ValueType.Amount`). On `Month`, the pivot would be over `Month` instead.

You write only the last line. The context is generated.

On a dimension other than the value type, members are referenced the same way, with the code in brackets. A computed quarter on `Month`:

```kex
Month.[202601] + Month.[202602] + Month.[202603]
```

### Writeback

By default, an expression cell is read-only. A **writeback expression** makes it editable by describing how an entered value maps back to a stored value type:

| Setting | Configuration | Meaning |
|---|---|---|
| Expression | `Expression` | Kex Lite expression evaluated on the entered value, `$input` |
| Destination | `DatasetDimensionValueCode` | the stored value type (or dimension value) the result is written to |
| Spread | `SpreadOverride` | spread type for the write, see [Spreading](#spreading) |
| Edit precision | `EditPrecisionOverride` | decimals accepted on input |

Example: the dataset stores `Price` and `Quantity`. An expression `Revenue` is defined as

```kex
ValueType.Price * ValueType.Quantity
```

with the writeback expression

```kex
$input / ValueType.Price
```

and destination `Quantity`, spread `Proportional`. When a user types a revenue figure, the system calculates the corresponding quantity, writes it to `Quantity` and spreads it proportionally. `Revenue` itself is never stored. Other expressions that depend on `Quantity`, and all totals, are recalculated in the view.

If no spread is given, the writeback inherits the spread of the input value type. Expressions on the value type dimension create a virtual value type of their own and should always have an explicit spread.

Writeback is turned off with **Turn off writeback expression** (*Stäng av writeback-uttryck*). A cell from an expression without writeback shows *"This value is read-only (The expression has no writeback)"*.

## Row-level access

Each dataset has its own row-level access, set under **Settings → App access** (*Inställningar → Appåtkomst*):

| Mode | Configuration | Meaning |
|---|---|---|
| Inherit | `Inherit` with `EnableDimensionBasedAccess` | use the app's access list as is; `EnableDimensionBasedAccess` requires the dataset to contain all dimensions configured in the app's access list, when that list uses dimension-based access |
| Limit | `Limit` with `AccessListId` | use a specific access list for this dataset |

Both modes have `ReadOnly`, which makes the whole dataset read-only (*Read access* / *Read and write access*, *Läsåtkomst* / *Läs- och skrivåtkomst*). The GUI offers *Inherit from app*, *Full* and *Choose list* (*Ärv från app* / *Full* / *Välj lista*). Without explicit settings, the dataset inherits with dimension-based access if the app has an access list, and otherwise gives full access.

The GUI choices map to the configuration as follows:

| GUI | Configuration |
|---|---|
| *Läs- och skrivåtkomst* + *Full* | `{"$type": "Inherit", "EnableDimensionBasedAccess": false, "ReadOnly": false}` |
| *Läsåtkomst* | the same, with `"ReadOnly": true` |
| *Ärv från app* | `Inherit` with `EnableDimensionBasedAccess: true`. The choice is disabled when the app has no access list. |
| *Välj lista* | `Limit` with `AccessListId` |

## Related

- [Workspaces and components](components.html)
- [Using apps](end-user.html)
- [App configuration (XML/JSON)](app-config.html)
- [Data model](data-model.html)
- [Kex](kex.html)
