Dataset Expressions can be found in the same place as you configure datasets.
What are expressions?
Expressions in DataSets allow you to compute and display data directly in the user's browser, without needing server-side calculations. They enable display of calculated data within a grid without a calculation that moves data between valuetypes. Therefore, the output from an expression is also not accessible outside the dataset and grids using it. Some other differences between calculatiosn and expressions are:
- Calculations have access to all the data in a plan whereas Expressions only work on the data that the current user has access to in the app, based on dataset filters and app access rule
Datasets as tables
Generally, you can think of expressions as adding data to the dataset. This data can then be displayed in a grid just like other data in the dataset. To understand this, it might be useful to dig a bit deeper into datasets. A dataset can be thought of as a big table with columns for:
- ValueTypeId
- DataType (Plan or Reference_X)
- One column each per dimension included in the dataset
- One column each per property included in the dataset (these are represented as DimensionId_PropertyId).
- Value of the data
How expressions are evaluated
Expressions are evaluated in the order they appear in the config. This means that you can use earlier expressions in later ones.
Different expression types
There are four types of expressions (highlighted below)
A - Dimension Expressions
B - DimensionProperty Expressions
C - DataType Expressions
D - ValueType Expressions
The sections below describe how these work.
A - Dimension Expressions
Dimension expressions add data with different values for one of the dimensions. As an example, this expression:
produce the following result creating a new ProjectActivity which holds the sum of Activity A and Activity B. To use a specific dimension value, the syntax is [DimensionId.DimensionValueCode]
B - DimensionProperty Expressions
DimensionProperty expressions are similar to Dimension expressions with a slight difference in the syntax. Here the pattern is instead [DimensionId_DimensionPropertyId.PropertyValueCode].
The DimensionPropertyId is found in the dimension editor:
C - DataType Expressions
DataType expressions instead create a new datatype (~ reference). For example, the expression could look like:
D - ValueType Expressions
Last but not least, we have ValueType Expressions. For example, an expression could look like:
This expression also illustrates a special kind of aggregation (quotient) which takes the ratio of two values. This aggregation type can be used for the other expression types as well and the expression above will give the following behavior in the app where Forecast vs Modified is the ratio between the valuetypes ProjectForecast and ProjectForecastModified.