Column Functions Reference

The Calculated Columns feature of MITS Report provides the ability to create a column that displays the result of a date/time processing, arithmetic, or text processing function run against one or more pre-existing columns from a report source. There are three types of column functions available:

• Date/Time Processing functions provide options for creating columns that show different variations on the values in columns that are configured as DATE columns in the report source.
• Mathematical Processing functions provide options for creating columns that show the result of an arithmetic function performed against one or more numeric columns in the report source.
• Text Processing functions provide options for creating columns that show the result of some text processing function performed against a text column in the report source or a specified string.

Date/Time Processing Functions

Year and Month

Calculation Description: Provides the 4-digit year and 2-digit month from a date

Calculation Details: Year and month from date

Output Format: YYYY-MM

Example:

Column Value: 07/21/2009

Calculated Column Result: 2009-07

 

Numeric Month

Calculation Description: Provides the month number from a date

Calculation Details: Numeric Month from date

Output Format: M

Example:

Column Value: 07/21/2009

Calculated Column Result: 7

 

Year

Calculation Description: Provides the year from a date

Calculation Details: Year from date

Output Format: YYYY

Example:

Column Value: 07/21/2009

Calculated Column Result: 2009

 

Day of Week

Calculation Description: Provides the named day of the week from a date

Calculation Details: Weekday from date

Output Format: (Weekday Name)

Example:

Column Value: 07/21/2009

Calculated Column Result: Tuesday

 

Day of Month

Calculation Description: Provides the numeric day of the month from a date

Calculation Details: Day-of-month from date

Output Format: ##

Example:

Column Value: 07/21/2009

Calculated Column Result: 21

 

Days Elapsed Between

Calculation Description: Determines the number of days that have elapsed between two dates

Calculation Details: Days between from_date and to_date, including from_date

Output Format: ##

Example:

"from_date" Column Value: 12/17/2009

"to_date" Column Value: 12/19/2009

Calculated Column Result: 2

Most Recent Date

Calculation Description: Horizontally compares the dates in two or more columns and provides the most recent date

Calculation Details: MostRecentDate(column1, column2, column3)

Output Format: MM/DD/YYYY

Example:

Column Value 1: 07/21/2009

Column Value 2: 01/05/10

Column Value 3: 12-29-2009

Calculated Column Result: 01/05/2010

 

Oldest Date

Calculation Description: Horizontally compares the dates in two or more columns and provides the oldest date

Calculation Details: OldestDate(column1, column2, column3)

Output Format: MM/DD/YYYY

Example:

Column Value 1: 07/21/2009

Column Value 2: 01/05/10

Column Value 3: 12-29-2009

Calculated Column Result: 07/21/2009

 

Mathematical Processing Functions

Add

Calculation Description: Adds two numbers together

Calculation Details: number1 + number2

Example:

Column Value 1: 42

Column Value 2: 5 

Calculated Column Result: 47

 

Subtract

Calculation Description: Subtracts one number from another

Calculation Details: number1 - number2

Example:

Column Value 1: 42

Column Value 2: 5 

Calculated Column Result: 37

 

Multiply

Calculation Description: Multiplies two numbers

Calculation Details: number1 * number2

Example:

Column Value 1: 42

Column Value 2: 5

Calculated Column Result: 210

 

Divide

Calculation Description: Divides one number by another

Calculation Details: number1 / number2

Example:

Column Value 1: 42

Column Value 2: 5

Calculated Column Result: 8.4

 

Average

Calculation Description: Horizontally averages the values in two or more columns (arithmetic mean)

Calculation Details: (column1 + column2 + ... + columnN) / N

Example:

Column Value 1: 42

Column Value 2: 5

Calculated Column Result: (42 + 5) / 2 = 23.5

 

Sum Multiple Columns

Calculation Description: Adds together two or more columns

Calculation Details: column1 + column2 + ... + columnN

Example:

Column Value 1: 42

Column Value 2: 5

Column Value 3: 1290

Calculated Column Result: 42 + 5 + 1290 = 1337

 

Rate of Change

Calculation Description: Provides the ratio of increase or decrease from one value to the next

Calculation Details: (value - previous_value) / previous_value

Example:

Column Value 1 (value): 42

Column Value 2 (previous_value): 5

Calculated Column Result: (42 - 5) / 5 = 7.4

 

Power

Calculation Description: Raises the base value to the power of another value (exponentiation)

Calculation Details: (base) ^ power

Example:

Column Value 1 (base): 42

Column Value 2 (power): 5

Calculated Column Result: 42 ^ 5 = 130,691,232

 

Absolute Value

Calculation Description: Makes all values positive

Calculation Details: absolute value of number

Example #1:

Column Value 1: 42
Calculated Column Result: 42

Example #2:

Column Value 2: -5

Calculated Column Result: 5

 

Highest Number

Calculation Description: Horizontally compares the values in two or more columns and provides the highest number

Calculation Details: HighestNumber(column1, column2, column3, ...)

Example:

Column Value 1: 42

Column Value 2: 5 

Calculated Column Result: 42

 

Lowest Number

Calculation Description: Horizontally compares the values in two or more columns and provides the lowest number

Calculation Details: LowestNumber(column1, column2, column3, ...)

Example:

Column Value 1: 42

Column Value 2: 5 Calculated Column Result: 5

 

Text Processing Functions

Uppercase

Calculation Description: Converts all letters to uppercase

Calculation Details: text to uppercase

Example:

Column Value: Blue

Calculated Column Result: BLUE

 

Lowercase

Calculation Description: Converts all letters to lowercase

Calculation Details: text to lowercase

Example:

Column Value: Blue

Calculated Column Result: blue

 

Substring

Calculation Description: Provides X characters beginning Y characters from the start

Calculation Details: length characters from text starting at character begin_index

Example:

text (column value): Blue720

begin index: 2

length: 4

Calculated Column Result: lue7

 

Field

Calculation Description: Splits delimited text into pieces and provides the Nth piece. (The first piece is numbered as 1.)

Calculation Details: field piece_number of text when split on separator

Example:

text: Blue 3/4"

Organic separator: /

piece number: 2

Calculated Column Result: 4" Organic

(if "piece number" had been 1, the calculated column result would have been Blue 3)

 

Replace

Calculation Description: Replaces all occurrences of one value with another value

Calculation Details: Replaces text with other-text

Example:

text: Blue find: lue
replace with: rown
Calculated Column Result: Brown

 

First X Characters

Calculation Description: Provides the first X characters of text

Calculation Details: first number_of_characters characters of text

Example:

text: Organic
number of characters: 3
Calculated Column Result: Org

 

Last X Characters

Calculation Description: Provides the last X characters of text

Calculation Details: last number_of_characters characters of text

Example:

text: Replacement005

number of characters: 3

Calculated Column Result: 005

 

Strip First X Characters

Calculation Description: Removes the first X characters of text

Calculation Details: text - first number_of_characters characters

Example:

text: Replacement005

number of characters: 11

Calculated Column Result: 005

 

Strip Last X Characters

Calculation Description: Removes the last X characters of text

Calculation Details: text - last number_of_characters characters

Example:

text: Replacement005

number of characters: 3

Calculated Column Result: Replacement

 

Add Prefix

Calculation Description: Adds characters to the beginning of text

Calculation Details: ’prefix’ + text

Example:

text: 42 prefix: Blue

Calculated Column Result: Blue42

 

Add Suffix

Calculation Description: Adds characters to the end of text

Calculation Details: text + ’suffix’

Example:

text: 42 suffix: Blue

Calculated Column Result: 42Blue