This documentation page assumes that you already have a SeekTable account. You can create 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 special "Expression" type. These expressions are calculated after data aggregation, and they could use another dimensions (or measures) as arguments. If you're looking for row-level calculations (before data aggregation) this is also possible; these calculations are specific to the data source, see appropriate documentation page exactly for your database type.

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".

  1. Add new dimension with Type=Expression
  2. Set unique Name. For example, YearAndQuarter
  3. Set user friendly Label (optional). For example, Year + Quarter
  4. 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.

  1. Add new measure with Type=Expression
  2. Set unique Name. For example, TotalAmount
  3. Set user friendly Label (optional). For example, Total Amount
  4. 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

Expression syntax

Expressions are evaluated by PivotData microservice; they could contain

Evaluation context variables and special functions calls:

Function Description
IfNull(<value>, <value_if_null>) returns first argument if it is not null, otherwise second argument is returned
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"
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.Day(<date_value>) returns day value
Date.DayOfWeek(<date_value>) returns day-of-week value: Monday, Tuesday etc
Date.DayOfWeekShort(<date_value>) returns short day-of-week value: Mon, Tue etc
Date.Week(<date_value>) returns week number
<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.
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.
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> ]) Returns <a> tag that is not HTML-encoded in the report.
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.
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 formula argument in "Parameters"). If measure name contains only alphanum and "_" its value can be accessed with just measure_name.
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 mapping dictionary.
Note: you can use external measure only in reports with dimensions that exist in both cubes.

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: