Docs

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: (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:

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.

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 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. 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)
  • $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:

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