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.

Expression-type dimensions can be used in both pivot table and flat table report types. However, in case of flat table keyword filter is applied differently, and it doesn't work for dimensions with Type=Expression. Use report parameters for data filtering to handle situations like this.

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 math operations (+, -, *, /), boolean operators (and, or, true/false constants), ternary conditional operator (<condition> ? <true_expression> : <false_expression>) 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. 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"
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
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.