Apps
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.
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) and for component-level overrides.
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:
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:
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
InvalidValueTypeConditionand 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.
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.
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.
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 asQ1= 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. The editor shows a collapsed // Context: block at the top. Expanded, it shows how the expression is evaluated:
// 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)
$sliceis the cell's slice of the data.- Every other dimension is bound to a single value (
limit 1), includingDataType(plan data or a reference). - The dimension the expression sits on is pivoted, so its members become named fields (
ValueType.Amount). OnMonth, the pivot would be overMonthinstead.
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:
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 |
| Edit precision | EditPrecisionOverride |
decimals accepted on input |
Example: the dataset stores Price and Quantity. An expression Revenue is defined as
ValueType.Price * ValueType.Quantity
with the writeback expression
$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 |