Expressions generate new columns from the source data.
const expressionFields = [
{
id: 'revenue',
isMeasure: false,
expression: {
operator: 'multiply',
inputs: [
{ id: 'sales.unitPrice' },
{ id: 'sales.quantity' },
],
},
},
];Expression fields are defined on the Data Source as an array of AgExpressionFieldDefinitions. Each expression field consists of an field definition (similar to a normal Field Definition), along with the expression itself.
See Below for the expression field API.
Calculated Columns & Measures Copy Link
An Expression Field can produce two different outputs:
- Calculated Column - For example
Profit = Revenue - Cost. - Measure - For example
Total Profit = SUM(Revenue - Cost).
Users can never edit an expression you define. The Field Panel shows a string expression read-only, and does not show an expression defined as an object. See Developer-Defined Expressions.
Calculated Columns Copy Link
Calculated columns produce multiple outputs for multiple inputs, and can therefore be aggregated in the UI.
You must specify isMeasure: false for Calculated Columns.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "pageA",
widgets: {
"calculated-columns-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "fullName" },
{ id: "revenue" },
{ id: "sales.unitCost" },
{ id: "profit" },
{ id: "isPremiumProduct" },
{ id: "daysSinceSale" },
],
},
sort: [
{
field: { id: "profit" },
direction: "desc",
},
],
format: {
title: {
enabled: true,
text: "Calculated Columns",
},
},
},
},
widgetLayout: {
"calculated-columns-grid": {
xTrack: 0,
yTrack: 0,
xSpan: 24,
ySpan: 25,
},
},
},
],
selectedPageId: "pageA",
panels: {
filters: {
collapsed: true,
},
edit: {
collapsed: true,
},
},
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
data: getData(),
};
let studioApi: AgStudioApi;
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
const expressionFields = [
{
id: 'profit',
isMeasure: false,
expression: {
operator: 'multiply',
inputs: [
{
operator: 'subtract',
inputs: [
{ id: 'sales.unitPrice' },
{ id: 'sales.unitCost' },
],
},
{ id: 'sales.quantity' },
],
},
},
// ...
]; Measures Copy Link
Measures produce a single output from multiple inputs, so the UI cannot aggregate them further.
The Total Row and Total Columns calculate each Measure again over all the rows they cover, rather than adding up the values in the table. A Measure that returns a weighted average, for example, shows the weighted average of every row in the Total Row.
You must specify isMeasure: true for Measures.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "pageA",
widgets: {
"measure-columns-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "sales.region" },
{ id: "sales.product" },
{ id: "totalProfit" },
],
},
sort: [
{
field: { id: "totalProfit" },
direction: "desc",
},
],
format: {
title: {
enabled: true,
text: "Measure Columns",
},
},
},
},
widgetLayout: {
"measure-columns-grid": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 25 },
},
},
],
selectedPageId: "pageA",
panels: {
filters: {
collapsed: true,
},
edit: {
collapsed: true,
},
},
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
data: getData(),
};
let studioApi: AgStudioApi;
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
const expressionFields = [
{
id: 'totalProfit',
isMeasure: true,
expression: {
operator: 'subtract',
inputs: [
{ id: 'sales.unitPrice', aggregation: 'sum' },
{ id: 'sales.unitCost', aggregation: 'sum' },
],
},
},
// ...
] Expression Types Copy Link
An expression is of one of three types:
- Function - A function applies an operator to one or more inputs.
- Value - A value is a fixed value (e.g. number, string, etc.).
- Field - A field refers to another field (either in the source data, or another expression field).
Function Expression Copy Link
const functionExpression = {
operator: 'multiply',
inputs: [
{ id: 'sales.unitPrice' },
{ value: 100 },
],
}; A function expression applies an operator to one or more inputs. Each input is another expression - a function, value or field.
|
See Below for the list of function expression operators.
Value Expression Copy Link
const valueExpression = {
type: 'number',
value: 100,
}; A value expression is a fixed value (e.g. number, string, etc.).
|
Field Expression Copy Link
const fieldExpression = {
id: 'sale.profit',
aggregation: 'sum',
}; A field expression refers to another field (either in the source data, or another expression field).
|
API Copy Link
Expression Field Definition Copy Link
The format type of the field (provides default formatting, etc.). If not provided, will be inferred from the expression.
|
Whether this expression creates a Measure or a Calculated Column. A Calculated Column produces a list of values, e.g. quantity × cost, even without a grouping. A Measure produces a single value e.g. SUM(quantity).
|
The expression for the field.
|
Field ID.
|
Display name.
|
Field description. Displayed in the Field Panel
|
Set to true to hide from being selected in the UI. Field can still be used for joins.
|
Controls whether the field can be edited in the UI.
|
Optional. How the field values will be serialized into state. Defaults to format serializer.
|
Optional. How the field values will be deserialized from state. Defaults to format deserializer.
|
Optional. Build a value formatter bound to the field's format options and the runtime API. Defaults to format factory.
|
Optional. How blank values will be displayed. Defaults to format blank value.
|
Optional. Will be passed to the value formatter.
|
Optional. An application-defined object carried on the hydrated field, and passed back on that field to callbacks such as a grid widget's createCellRenderer. Studio never reads it. Replaces any context set on the field's format.
|
Function Expression Operators Copy Link
Each operator is listed under the name a Function Expression uses for operator. Its description opens with the equivalent Typed Syntax, then lists the input types the operator accepts and the type it returns. Function names are case-insensitive, and some operators also have an infix form.
ADD(a, b), or a + b. a & b concatenates strings. Add two values together.['number', 'number'] => 'number' ['string', 'string'] => 'string' |
SUBTRACT(a, b), or a - b. Subtract the second value from the first.['number', 'number'] => 'number' |
MULTIPLY(a, b), or a * b. Multiply two values together.['number', 'number'] => 'number' |
DIVIDE(a, b), or a / b. Divide the first value by the second.['number', 'number'] => 'number' |
MODULO(a, b). Remainder of the first value divided by the second.['number', 'number'] => 'number' |
EQUALS(a, b), or a = b. Are the two values equal?['number', 'number'] => 'boolean' ['date', 'date'] => 'boolean' ['datetime', 'datetime'] => 'boolean' ['boolean', 'boolean'] => 'boolean' ['string', 'string'] => 'boolean' |
NOTEQUAL(a, b), or a <> b. Are the two values not equal?['number', 'number'] => 'boolean' ['date', 'date'] => 'boolean' ['datetime', 'datetime'] => 'boolean' ['boolean', 'boolean'] => 'boolean' ['string', 'string'] => 'boolean' |
LESSTHAN(a, b), or a < b. Is the first value less than the second?['number', 'number'] => 'boolean' |
GREATERTHAN(a, b), or a > b. Is the first value greater than the second?['number', 'number'] => 'boolean' |
LESSTHANOREQUAL(a, b), or a <= b. Is the first value less than or equal to the second?['number', 'number'] => 'boolean' |
GREATERTHANOREQUAL(a, b), or a >= b. Is the first value greater than or equal to the second?['number', 'number'] => 'boolean' |
BETWEEN(value, low, high). Is the value between the two bounds? Inclusive at both ends.['number', 'number', 'number'] => 'boolean' ['date', 'date', 'date'] => 'boolean' ['datetime', 'datetime', 'datetime'] => 'boolean' |
AND(a, b), or a && b. Are both the values true?['boolean', 'boolean'] => 'boolean' |
OR(a, b), or a || b. Are either of the values true?['boolean', 'boolean'] => 'boolean' |
NOT(a), or NOT a. Negates the value.['boolean'] => 'boolean' |
NEGATE(a), or -a. Negates the value.['number'] => 'number' |
IF(condition, whenTrue, whenFalse). If the first value is true then return the second value, else return the third value.['boolean', 'string', 'string'] => 'string' ['boolean', 'number', 'number'] => 'number' ['boolean', 'boolean', 'boolean'] => 'boolean' ['boolean', 'date', 'date'] => 'date' ['boolean', 'datetime', 'datetime'] => 'datetime' |
SWITCH(value, case1, result1, case2, result2, ..., defaultResult). Evaluates the first value against each case in order and returns the corresponding result for the first matching case. If no cases match, returns the default result. At least one case and its result are required.
|
IN(value, option1, option2, ...). Is the first value in any of the subsequent values?['string', ...'string'] => 'boolean' ['number', ...'number'] => 'boolean' ['boolean', ...'boolean'] => 'boolean' ['date', ...'date'] => 'boolean' ['datetime', ...'datetime'] => 'boolean' |
ISTRUE(value). Is the value true?['boolean'] => 'boolean' |
ISFALSE(value). Is the value false?['boolean'] => 'boolean' |
ISNULL(value). Is the value null?['string'] => 'boolean' ['number'] => 'boolean' ['boolean'] => 'boolean' ['date'] => 'boolean' ['datetime'] => 'boolean' |
COALESCE(value, ...). Returns the first non-null value from the list of arguments.
|
ISNOTNULL(value). Is the value not null?['string'] => 'boolean' ['number'] => 'boolean' ['boolean'] => 'boolean' ['date'] => 'boolean' ['datetime'] => 'boolean' |
DATEDIFF(unit, start, end). Return the number of units defined by the first value that are between the second and third values.'millisecond' | 'ms' 'second' | 'ss' | 's' 'minute' | 'mi' | 'n' 'hour' | 'hh' 'day' | 'dy' | 'y' 'week' | 'ww' | 'wk' 'weekday' | 'dw' | 'w' 'month' | 'mm' | 'm' 'quarter' | 'qq' | 'q' 'year' | 'yyyy' | 'yy' 'dayofyear' |
DATEADD(unit, date, amount). Add a signed integer number of units to a date or datetime.['string', 'date', 'number'] => 'date' ['string', 'datetime', 'number'] => 'datetime' 'year' | 'quarter' | 'month' | 'week' | 'day' (and 'hour' | 'minute' for datetime only). Month-end clamping applies: adding one month to Jan 31 yields Feb 28/29.
|
DATEFROMPARTS(year, month, day). Construct a date from integer year, 1-based month, and day components.['number', 'number', 'number'] => 'date' range endpoint.
|
DATETRUNC(unit, date). Truncate a date or datetime to the start of a given unit.['string', 'date'] => 'date' ['string', 'datetime'] => 'datetime' 'year' | 'quarter' | 'month' | 'week' | 'day' (and 'hour' for datetime only)
|
DATEEND(unit, date). Return the end of a date or datetime period for a given unit.['string', 'date'] => 'date' ['string', 'datetime'] => 'datetime' 'year' | 'quarter' | 'month' | 'week' | 'day' (and 'hour' for datetime only)
|
DATEEXTRACT(unit, date). Extract a single integer component from a date or datetime.['string', 'date'] => 'number' ['string', 'datetime'] => 'number' 'year' | 'isoYear' | 'quarter' | 'month' | 'week' | 'day' | 'hour' | 'dayOfYear' | 'dayOfWeek' | 'monthOfQuarter' | 'weekOfMonth' | 'weekend' | 'timeOfDay' | 'minute' | 'second' | 'dayOfMonth'
|
PERCENTILES(field, p1, p2, ...). Compute multiple percentiles of the grouped values of the target numeric field, producing one output column per p value. Each is computed as for percentile. |
PERCENTILE(field, p). Compute the value at percentile p (0-1) of the grouped values of the target numeric field. Null values are ignored. Of the n values in ascending order, counted from 0, the result is the one at position p * (n - 1), interpolated linearly between the two values either side when that position falls between them. A p outside 0-1 gives null.['number', 'number'] => 'number' |
MEDIAN(field). Shorthand for percentile(field, 0.5), so with an even number of values the result is the mean of the two middle values.['number'] => 'number' |
CURRENTDATE(). Returns the current date as a DATE value. Session-constant: all rows in a single query receive the same value.[] => 'date' |
CURRENTTIMESTAMP(). Returns the current date and time as a TIMESTAMP value. Session-constant: all rows in a single query receive the same value.[] => 'datetime' |
ABS(x). Returns the absolute value of a number.['number'] => 'number' |
MOD(a, b). Returns the remainder when the first number is divided by the second (modulo). The sign of the result follows the sign of the dividend.['number', 'number'] => 'number' |
FLOOR(x). Returns the largest integer less than or equal to the given number.['number'] => 'number' |
CEILING(x). Returns the smallest integer greater than or equal to the given number.['number'] => 'number' |
ROUND(x) or ROUND(x, scale). Rounds a number to the nearest integer at the specified scale. Uses round-half-away-from-zero semantics.['number', 'number'] => 'number' ['number'] => 'number' (scale defaults to 0) |
TRUNCATE(x) or TRUNCATE(x, scale). Truncates a number to the specified scale toward zero.['number', 'number'] => 'number' ['number'] => 'number' (scale defaults to 0) |
POWER(base, exponent), or base ^ exponent. Raises the base to the power of the exponent.['number', 'number'] => 'number' |
EXP(x). Returns e raised to the power of the given number.['number'] => 'number' |
SIN(x). Returns the sine of a number in radians.['number'] => 'number' |
COS(x). Returns the cosine of a number in radians.['number'] => 'number' |
TAN(x). Returns the tangent of a number in radians.['number'] => 'number' |
ASIN(x). Returns the arcsine of a number (inverse sine) in radians. Result is in the range [-π/2, π/2]. Input must be in the range [-1, 1].['number'] => 'number' |
ACOS(x). Returns the arccosine of a number (inverse cosine) in radians. Result is in the range [0, π]. Input must be in the range [-1, 1].['number'] => 'number' |
ATAN(x). Returns the arctangent of a number (inverse tangent) in radians. Result is in the range [-π/2, π/2].['number'] => 'number' |
ATAN2(y, x). Returns the arctangent of the quotient of two numbers (two-argument arctangent) in radians. Result is in the range [-π, π].['number', 'number'] => 'number' |
LN(x). Returns the natural logarithm (base e) of a number.['number'] => 'number' |
LOG(base, x). Returns the logarithm of the second number to the base specified by the first number.['number', 'number'] => 'number' |
LOG10(x). Returns the base-10 logarithm of a number.['number'] => 'number' |
GREATEST(a, b, ...). Returns the maximum value of two or more numbers. Returns NULL if any argument is NULL.['number', ...'number'] => 'number' |
LEAST(a, b, ...). Returns the minimum value of two or more numbers. Returns NULL if any argument is NULL.['number', ...'number'] => 'number' |
UPPER(text). Converts a string to uppercase.['string'] => 'string' |
LOWER(text). Converts a string to lowercase.['string'] => 'string' |
SUBSTRING(text, start) or SUBSTRING(text, start, length). Extracts a substring from a string.['string', 'number'] => 'string' (FROM start position to end of string) ['string', 'number', 'number'] => 'string' (FROM start position FOR length) |
POSITION(needle, haystack). Returns the 1-indexed position of the first occurrence of needle in haystack.['string', 'string'] => 'number' |
LTRIM(text, characters). Removes the specified characters from the left side of a string.['string', 'string'] => 'string' |
RTRIM(text, characters). Removes the specified characters from the right side of a string.['string', 'string'] => 'string' |
BTRIM(text, characters). Removes the specified characters from both sides of a string.['string', 'string'] => 'string' |
OVERLAY(text, replacement, start) or OVERLAY(text, replacement, start, length). Replaces a substring with another string at a specified position.['string', 'string', 'number'] => 'string' (FROM start, replace to end of replacement) ['string', 'string', 'number', 'number'] => 'string' (FROM start FOR length) |
LIKE(text, pattern), LIKE(text, pattern, escape) or LIKE(text, pattern, escape, matchCase). SQL LIKE wildcard pattern match: % matches any sequence of characters, _ matches any single character.['string', 'string'] => 'boolean' ['string', 'string', 'string'] => 'boolean' (third argument is the ESCAPE character) ['string', 'string', 'string', 'boolean'] => 'boolean' (fourth argument is matchCase) matchCase is false or null, when the match ignores case as the like filter does by default. To set matchCase without an escape character, pass a null escape. NULL text yields NULL. Use notLike for the negation.
|
NOTLIKE(text, pattern), NOTLIKE(text, pattern, escape) or NOTLIKE(text, pattern, escape, matchCase). The negation of like: true when the text does not match the pattern.['string', 'string'] => 'boolean' ['string', 'string', 'string'] => 'boolean' (third argument is the ESCAPE character) ['string', 'string', 'string', 'boolean'] => 'boolean' (fourth argument is matchCase) matchCase is false or null, as for like. NULL text yields NULL.
|
CONTAINS(text, search). Does the text contain the search text? Exact and case-sensitive.['string', 'string'] => 'boolean' |
NOTCONTAINS(text, search). Does the text not contain the search text? Exact and case-sensitive.['string', 'string'] => 'boolean' |
STARTSWITH(text, prefix). Does the text start with the prefix? Exact and case-sensitive.['string', 'string'] => 'boolean' |
ENDSWITH(text, suffix). Does the text end with the suffix? Exact and case-sensitive.['string', 'string'] => 'boolean' |
SIGN(x). Returns the sign of a number (-1, 0, or 1).['number'] => 'number' |