Expressions Reference PivotData Microservice Documentation

PivotData microservice supports 2 types of calculations:

  • row-level expressions: when calculation is evaluated before data aggregation. The syntax of the row-level expression depends on the data source: for example in case of SQL source this will be SQL expressions; you can use functions and syntax that is supported by your database. If you need this type of calculations see documentation on your data source for more details.
  • formula over aggregated values (or calculated dimension): these expressions are evaluated after data aggregation by PivotData microservice engine. This kind of calculations is defined with expression-type cube members. The syntax of these expressions is the same for all types of data sources.

How to define expression-type dimension

"Dimensions": [
    "Name": "OrderDate_year_and_quarter",
    "LabelText": "Order Date (Year+Quarter)",
    "Type": "Expression",
    "Params": [ 
      "OrderDate_year+\" Q\"+OrderDate_quarter",  // this is an expression
	  "OrderDate_year",  // next entries are names of dimensions used in the expression

How to define expression-type measure

"Measures": [ 
    "Name": "Profit",
    "Type": "Expression",
    "Params": [ 
      " SumOfIncomeValue - SumOfExpensesValue ",   // expression
      "SumOfIncomeValue",  // next entries are names of measures used in the formula

Expressions cannot use another expression-type members as arguments.

Expressions syntax

  • math operations (+, -, *, /)
  • comparison operators (==, !=, <, <=, >, >=)
  • boolean operators (and, or, true/false constants)
  • string "value" or number 5 constants
  • ternary conditional operator (bool_condition_expression ? true_expression : false_expression)
  • creation of array new[] { value1, value2, value3 }
  • creation of dictionary (key-value map) new dictionary { {keyExpr, valueExpr} }

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.
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.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> ]) 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.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 a chart.
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.