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
      "OrderDate_quarter"
    ]
  }
]

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
      "SumOfExpensesValue"
    ] 
}

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
`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
`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"
`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> ])`	Returns <a> tag that is not HTML-encoded in the report.
`Html.SetTextColor(<value>, <html_color>)`	Sets font color for the cell (`TD` element); `value` can be a result of `Html.Raw`. This affects HTML, PDF, JSON and Excel exports.
`Html.SetBackgroundColor(<value>, <html_color>)`	Sets background color for the cell (`TD` element); `value` can be a result of `Html.Raw`. This affects HTML, PDF, JSON 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.
`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`.
`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).
`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.