This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

Configure pivot table calculated field

It is possible to add formula-based dimension or measure in the cube configuration form ("Cube → Edit Configuration") with a special "Expression" cube member type. These expressions are calculated after data aggregation by SeekTable engine and they don't depend on the data source:

dimension formula may use another dimensions as arguments.
measure formula may use another measures as arguments, and even take into account cell's dimension value(s).

If you're looking for row-level calculations (that are evaluated for each input raw data row, before aggregation) they are also possible:

for dimension with Type=Field you can specify DB-specific expression as a first value in dimension's "Parameters"
for measures with Type=Sum/Average/Min/Max you can specify SQL expression instead of column name (in "Parameters"), or use measure Type=First Value to define custom SQL aggregate expression (if it is available for the concrete DB connector)

Row-level calculations are included into the data source query and thus they are very specific to concrete data source connector; please check appropriate documentation page exactly for your database type for more details.

Calculated dimension

Lets assume that the cube has "Year" and "Quarter" dimensions, and we need a new dimension that combines year and quarter - something like "2018 Q1".

Add new dimension with Type=Expression
Set unique Name. For example, YearAndQuarter
Set user friendly Label (optional). For example, Year + Quarter
In Parameters add the following values:

Value 1:

Year + " Q" + Quarter (first value is an expression)

Value 2:

Year (argument, refers to an existing dimension name)

Value 3:

Quarter (argument, refers to an existing dimension name)

Add expression-based dimension

Calculated measure

Lets assume that the dataset has "Quantity" and "ItemPrice" columns, and cube has "SumOfQuantity" and "SumOfItemPrice" measures, and we need to calculate total price.

Add new measure with Type=Expression
Set unique Name. For example, TotalAmount
Set user friendly Label (optional). For example, Total Amount
In Parameters add the following values:

Value 1:

SumOfQuantity * SumOfItemPrice (first value is an expression)

Value 2:

SumOfQuantity (argument, refers to an existing measure name)

Value 3:

SumOfItemPrice (argument, refers to an existing measure name)

Add expression-based measure

Formula Expressions Syntax

Cube member expressions are evaluated by PivotData microservice (SeekTable's reporting engine). These expressions can contain:

math operations (+, -, *, /)
comparison operators (==, !=, <, <=, >, >=)
boolean operators (and, or, true/false constants)
string "value" or number 5 or boolean true/false constants
ternary conditional operator bool_condition_expression ? true_expression : false_expression. Can be used in a sequence, for example:
```
a>10 ? "green" : a<0 ? "red" : "white"
```
creation of an array new[] { value1, value2, value3 }
creation of a dictionary (key-value map) new dictionary { {key1, value1}, {key2, value2} }
local variables var a = value_expression; main_expression

A complex expression can be multi-line to improve readability.

Evaluation context variables and special functions calls:

Function	Description
`Parameter["param_name"]`	Value of specified parameter in the current report.
`IfNull(<value>, <value_if_null>)`	returns first argument if it is not null, otherwise second argument is returned
`IsEmpty(<value>)`	returns `true` if value is: null, empty string, zero-length array/list.
`Format(<fmt_string>, new[] { <value1>, <value2> } )`	formats a string with .NET String.Format method. For example, fmt_string=`${0:0.##}` can be used to get output like "$5.27". In addition to standard number/DateTime format specifiers the following SeekTable-specific syntax is supported: `{0:MMM}` → format month number (1-12) as short month name (Jan, Feb etc) `{0:MMMM}` → format month number (1-12) as full month name (January, February etc) `{0:0.#\|k}` → if number>1000 shorten it with "k" suffix `{0:0.#\|M}` → if number>1000000 shorten it with "M" suffix `{0:0.#\|kMB}` → shorten large number with appropriate "k"/"M"/"B" suffix
`SortBy(<displayValue>, <sortValue>)`	specifies a different value for sorting purposes. This function should be top-most and affects only pivot tables (in flat-table reports "Expression"-type dimensions are always ordered by first argument that corresponds to the real column in the dataset).
Type conversion functions
`Convert.ToInt32(<some_value>)`	converts to integer data type
`Convert.ToDecimal(<some_value>)`	converts to decimal data type (can contain fraction part)
`Convert.ToDateTime(<some_value>)`	converts to DateTime data type
`Convert.ToTimeSpan(<some_value>)`	converts to Time data type from strings like "hh:mm:ss"
`Convert.ToString(<some_value>)`	convert value of any type to string representation
Date functions
`Date.Year(<date_value>)`	returns year value
`Date.Quarter(<date_value>)`	returns quarter value: 1, 2, 3 or 4
`Date.Month(<date_value>)`	returns month value
`Date.MonthName(<month_number>)`	returns month name (January, February etc) by month number (1-12)
`Date.MonthNameShort(<month_number>)`	returns short month name (Jan, Feb etc) by month number (1-12)
`Date.Day(<date_value>)`	returns day value
`Date.DayOfWeek(<date_or_int_value>)`	returns day-of-week name: Monday, Tuesday etc. If argument is a number it should be in the range [0..6].
`Date.DayOfWeekShort(<date_or_int_value>)`	returns day-of-week short name: Mon, Tue etc. If argument is a number it should be in the range [0..6].
`Date.FirstDayOfWeek()`	returns first day-of-week according to the engine's formatting culture (can be customized on SeekTable installations): a number in the range [0..6] where 0 = Sunday, 1 = Monday etc.
`Date.Week(<date_value>)`	returns week number
`<datetime_value>.DayOfWeek`	returns date's day-of-week number value in the range [0..6] (0 = Sunday, 1 = Monday etc).
`<datetime_value>.AddYears(<integer_value>)`	returns a new DateTime that adds the specified number of years.
`<datetime_value>.AddMonths(<integer_value>)`	returns a new DateTime that adds the specified number of months .
`<datetime_value>.AddDays(<integer_value>)`	returns a new DateTime that adds the specified number of days.
`Date.Now()`	returns a <datetime_value> that is set to current date/time value (in server's timezone).
`Date.UtcNow()`	returns a <datetime_value> that is set to current date/time value, expressed as the Coordinated Universal Time (UTC).
`Date.TimezoneUtcOffsetHours(<linux_tz_name>)`	returns offset in hours for the specified timezone name.
`Date.ISOWeek.GetWeekOfYear(<date_value>)`	calculates the ISO week number of a given Gregorian date (a number between 1 and 53).
`Date.ISOWeek.GetWeeksInYear(<year_value>)`	calculates the number of weeks (52 or 53) in a given ISO week-numbering year.
`Date.ISOWeek.GetYear(<date_value>)`	calculates the ISO week-numbering year (also called ISO year) mapped to the input Gregorian date (a number between 1 and 9999).
`Date.ISOWeek.GetYearStart(<year_value>)`	returns a <datetime_value> that is the Gregorian date at which the week-numbering year will start.
`Date.ISOWeek.GetYearEnd(<year_value>)`	returns a <datetime_value> that is the Gregorian date at which the week-numbering year will end.
String functions
`String.Concat(<array_of_values>)`	concatenates several string values into one resulting string. In case of non-string values they are converted to string type.
`String.Join("separator", <array_of_values>)`	Concatenates all the elements of a string array, using the specified separator between each element. In case of non-string values they are converted to string type.
`String.Split("separator", <string_value>)`	Splits a string into substrings using a specified separator string to determine where to make each split. Result is an array of strings.
`String.Trim(<string_value>)`	Removes all leading and trailing white-space characters from the specified string.
`<string_value>.Replace("old_value","new_value")`	Replaces all occurrences of a specified string with new value.
`<string_value>.Substring(<start_index> [, <length> ])`	Retrieves a substring from this instance.
`<string_value>.Length`	Gets the number of characters in the string value.
Math functions
`Math.Round(<number_value>)`	Rounds a decimal value to the nearest integral value, and rounds midpoint values to the nearest even number.
`Math.Round(<number_value>, <decimals>)`	Rounds a decimal value to a specified number of fractional digits (valid value is 0..28), and rounds midpoint values to the nearest even number.
`Math.Floor(<number_value>)`	Returns the largest integral value less than or equal to the specified number.
`Math.Ceiling(<number_value>)`	Returns the smallest integral value that is greater than or equal to the specified number.
`Math.Sqrt(<number_value>)`	Returns the square root of a specified number.
`Math.Pow(<number_value>, <power>)`	Returns a specified number raised to the specified power.
Regex functions
`Regex.Replace(<input_string_value>, "pattern", "replacement")`	Replaces all strings that match a specified regular expression with a specified replacement string. Note: use .NET syntax of regular expressions.
`Regex.IsMatch(<input_string_value>, "pattern")`	Returns `true` if the regular expression finds a match in the input string.
HTML-related functions
`Json.Serialize(<value>)`	Serializes the specified object to a JSON string.
`Html.UrlEncode(<value>)`	Converts a string value into a URL-encoded string.
`Html.HtmlEncode(<value>)`	Converts a string to an HTML-encoded string.
`Html.HtmlDecode(<value>)`	Converts a string that has been HTML-encoded into a decoded string.
`Html.Link(<url>, <text> [, <bool_open_in_new_window> ])`	Hyperlinks table's cell. This affects HTML, PDF and Excel exports.
`Html.SetTextColor(<value>, <html_color>)`	Sets font color for the cell (`TH` or `TD` element); `value` can be a result of `Html.Raw`. This affects HTML, PDF and Excel exports.
`Html.SetBackgroundColor(<value>, <html_color>)`	Sets background color for the cell (`TH` or `TD` element); `value` can be a result of `Html.Raw`. This affects HTML, PDF and Excel exports.
`Html.Raw(<html_content>)`	Returns markup that is not HTML-encoded in the report. Use this function very carefully: incorrect HTML may break report layout and even affect SeekTable app functionality. Note: your expression should return result of this function, otherwise content will not be processed as HTML.
`Html.Raw(<html_content>,<non_html_value>)`	By providing 2-nd argument you can specify alternative value for non-HTML report exports (like CSV, Excel). Non-HTML value is also needed if you use custom HTML formatting for measure and want to display it in a chart. Also non-HTML value is used for sorting (unless `SortBy` is used).
`Html.SetReportParameters( <dictionary_param_values>, <text>)`	When user clicks on specified <text> apply specified report parameters. This function is useful for quick database-level filtering in reports.
Functions only for calculated dimensions
`Dimension["dimension_name"]`	returns value of the dimension with name="dimension_name" (it should be specified as formula argument in "Parameters"). If dimension name contains only alphanum and "_" its value can be accessed with just `dimension_name`.
`Cube("cubeId").Lookup(value, "keyDimension","lookupDimension")`	Resolve external lookup by specified value. keyDimension and lookupDimension are dimension names in the cube with ID=cubeId (you can get it from the URL).
Functions only for calculated measures
`Measure["measure_name"]`	value of the measure with name="measure_name" (it should be specified as a formula argument in "Parameters"). If measure name contains only alphanum and "_" its value can be accessed with just `measure_name`.
`PivotTable.GrandTotal("measure_name")`	grand total value of the measure with name="measure_name". This measure name should be declared as a formula argument in "Parameters".
`PivotTable.RowTotal("measure_name")`	row total value of the measure with name="measure_name". This measure name should be declared as a formula argument in "Parameters".
`PivotTable.ColumnTotal("measure_name")`	column total value of the measure with name="measure_name". This measure name should be declared as a formula argument in "Parameters".
`PivotTable.IsCellRowTotal()`	indicates whether this cell's value is a row total.
`PivotTable.IsCellColumnTotal()`	indicates whether this cell's value is a column total.
`PivotTable.IsCellGrandTotal()`	indicates whether this cell's value is a grand total.
`PivotTable.IsCellSubTotal()`	indicates whether this cell's value is a sub-total (including row/column/grand totals).
`Dimension.ContainsKey("dimension_name")`	Check if specified dimension is present in the evaluation context (used in the report).
`Dimension["dimension_name"]`	value of the dimension that corresponds to table cell for this particular measure value. Important note: if dimension is not used in the report accessing its value will cause an evaluation error.
`Cube("cubeId").Measure("measureName", dimMappingDictionary)`	Get the measure of the specified cube. dimMappingDictionary determines mapping between source and target cube dimension names; for example: new dictionary{ {"source_date_year", "target_date_year"}, {"source_date_month", "target_date_month"} } If dimension names used in report are the same in both cubes you can specify `null` value for the dimMappingDictionary. Important notes: 'external' measure can be used only in reports with dimensions that exist in both cubes. access of another cube leads to additional data source queries. This may cause additional delays in reports generation. avoid usage of 'external' measure in reports with many cells. it is possible to specify parameters in this way: `CubeP("cubeId", paramsDictionary)`. For example, to apply current report's parameter specify: `CubeP("cubeId", new dictionary { {"param1", Parameter["param1"]} } )`.
Functions only for parameter's expression
`Dimensions.Contains("dimension_name")`	determine whether current report contains a dimension with a specified name.
`Dimensions.ContainsPrefix("prefix")`	determine whether current report contains a dimension with a name that starts with a specified prefix.
`Dimensions.ContainsSuffix("suffix")`	determine whether current report contains a dimension with a name that ends with a specified suffix.
`Measures.Contains("measure_name")`	determine whether current report contains a measure with a specified name.
`Measures.ContainsPrefix("prefix")`	determine whether current report contains a measure with a name that starts with a specified prefix.
`Measures.ContainsSuffix("suffix")`	determine whether current report contains a measure with a name that ends with a specified suffix.
`Sql.Raw(Parameter["table"])`	use this function to insert parameter's value into SQL query 'as-is' (without quotes). In this way you can pass any custom SQL value and use it in the query. Note: this makes your SQL query vulnerable to SQL injections because end-user can include any SQL into the query. Raw SQL can be used in a safe manner only if parameter's value is passed to embedded report in the JWT payload; in all other cases SQL injections will be possible when reports are shared or published.

Calculated dimensions in flat table reports

Expression-type dimensions could be used in flat tables but you should be aware about some specifics related to the filtering and ordering:

rows are ordered by the 1-st argument of the expression-type dimension (not by expression evaluation result)
keyword filter is not applied to the expression-type columns. Use report parameters to overcome this limitation.