Equation Functions
Equation Tools
The Equation Editor helps you use function and gives you previews of the results.
The Equation Selector helps you choose a function to work with.
List of Equation Functions by Category
Date and Time
Function | Description |
DATE | Returns the date of the given year, month, and day. |
DATESPAN | Can be added to a date to offset it. |
NOW | Returns the current date and time formatted as a date and time. |
TIME | Returns the time of the given hour, minute, and second. |
TIMESPAN | Can be added to a time to offset it |
Math and Trig
Function | Description |
ABS | Returns the absolute value of a number, a number without a sign. |
POWER | Returns the result of a number raised to a power. |
SUM | Adds all the numbers in a range of values. |
Statistical
Function | Description |
AVERAGE | Returns the average (arithmetic mean) of its arguments, which can be numbers of names, arrays, or reference that contain numbers. |
COUNT | Counts the number of values in a range that contains numbers. |
MAX | Returns the largest value in a set of values. Ignores logical values and text. |
MIN | Returns the smallest value in a set of values. Ignores logical values and text. |
Database
Function | Description |
DATA | Returns a dataset using the text. |
DATEDATA | Returns a date type dataset using the text, using input as the date format. |
NULL | Returns a null value. Primarily used to compare against the value of DATA. |
VALUE | Return a number converted from text. |
Text
Function | Description |
CONTAINS | Returns whether one text string contains another text string. |
INDEXOF | Returns the first index of one text string within another text string. |
LASTINDEXOF | Returns the last index of one text string within another text string. |
MID | Returns the characters from the middle of a text string, given a starting position and length. |
SUBSTRING | Returns the characters from the middle of a text string, given a starting position and an ending position. |
TRIM | Returns the text with the whitespace removed from the beginning and the end. |
UPPER | Returns the text with all letters in uppercase. |
LOWER | Returns the text with all letters in lowercase. |
LEN | Returns the length of the text. |
Logical
Function | Description |
FALSE | Returns the logical value FALSE. |
IF | Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. |
TRUE | Returns the logical value TRUE |
List of Equation Functions
ABS function
Description
The ABS function takes the absolute value of a number (the same number without a sign).
For example:
=ABS(-3)
would return 3.
Syntax
ABS(number)
- number - Required. The number to take the absolute value of.
AVERAGE function
Description
The AVERAGE function returns the average (arithmetic mean) of its arguments, which can be numbers of names, arrays, or references that contain numbers.
For example,
=AVERAGE(4,2,3)
would return 3.
If the average is not an integer, you will get a long decimal number. Use the 'display' attribute in an out tag to set how many digits to display.
Syntax
AVERAGE(number1,number2,...)
- number - At least one is required. These are the values to average.
CONTAINS function
Description
The CONTAINS function returns whether one text string contains another text string.
For example,
=CONTAINS("This is a test string.", "test")
would return true.
Syntax
CONTAINS(within_text,find_text)
- within_text - Required. The text containing the text you want to find.
- find_text - Required. The text you want to find.
COUNT function
Description
The COUNT function returns the count of the number of values in a range that contains numbers.
For example,
=COUNT(4,2,3)
would return 3.
Syntax
COUNT(number1,number2,...)
- number - At least one is required. These are the values to count
DATA function
Description
The DATA function returns a dataset from a data source.
For example,
=DATA("select dbo.Categories.CategoryName from dbo.Categories")
when using the SQL Northwind database would return "Beverages".
Note that this is heavily dependent on the data source you are reading from.
Syntax
DATA(text)
- text - Required. The query string to use against the data source.
DATEDATA function
Description
The DATEDATA function returns a date-type dataset from a data source. It uses a pattern that is passed to SimpleDateFormat (see Java documentation) to read in non-standard or un-typed date data.
Note that this is heavily dependant on the data source you are reading from.
Syntax
DATEDATA(text,input)
- text - Required. The query string to use against the data source.
- input - Required. The pattern to be passed to SimpleDateFormat to read in the data.
DATE function
Description
The DATE function returns the date of the given year, month, and day.
For example,
=DATE(2010,1,2)
represents January 2, 2010.
There are two major uses:
- You need a date object to add a DATESPAN (you want to offset a date by a number of years, months, and/or days).
- You want to use the Blueprint tag 'format' attribute to output the date in a specific format.
Syntax
DATE(year,month,day)
- year - Required. The year for the specific date.
- month - Required. The month for the specific date.
- day - Required. The day for the specific date.
DATESPAN function
Description
The DATESPAN function represents a time span.
For example,
=DATESPAN(3,2,1)
represents a timespan of 3 years, 2 months, and 1 day.
There is one major use:
- can be added to a date to provide an offset of a given number of years, months, and/or days. A date can be obtained using the DATE, DATA, and DATEDATA functions.
Syntax
DATE(year,month,day)
- year - Required. The number of years to span.
- month - Required. The number of months to span.
- day - Required. The number of days to span.
FALSE function
Description
The FALSE function returns the logical value FALSE. This can used in comparisons.
For example,
=(1 == 2) = FALSE()
would return true.
Syntax
FALSE()
IF function
Description
The IF function checks whether a condition is met. If TRUE, it returns one value. If FALSE, it returns another value.
For example,
=IF(1==2, "One equals two.", "One does not equal two.")
would return "One does not equal two".
Syntax
IF(logical_test,value_if_true,value_if_false)
- logical_test - Required. Any value of expression that can evaluated to TRUE or FALSE.
- value_if_true - Required. The value that is returned if Logical_text is TRUE.
- value_if_false - Required. The value that is returned if Logical_text is FALSE.
INDEXOF function
Description
The INDEXOF function returns the first index of one text string within another text string.
For example,
=INDEXOF("This rocks.", "is")
would return 2.
Syntax
INDEXOF(within_text,find_text)
- within_text - Required. The text containing the text you want to find.
- find_text - Required. The text you want to find.
LASTINDEXOF function
Description
The LASTINDEXOF function returns the index of the last occurance of one text string within another text string.
For example,
=LASTINDEXOF("This is great.", "is")
would return 5. "is" can be found at both index 2 and 5, bit 5 is later in the string so 5 is returned.
Syntax
LASTINDEXOF(within_text,find_text)
- within_text - Required. The text containing the text you want to find.
- find_text - Required. The text you want to find.
LEN function
Description
The LEN function returns the length of the text.
For example,
= LEN("some text")
returns the number 9.
Syntax
LEN(text)
- text - Required. The string whose length you wish to know.
LOWER function
Description
The LOWER function returns the text with all letters in lowercase.
For example:
=LOWER("This is SOME text")
returns "this is some text".
Syntax
LOWER(text)
- text - Required. The text to convert to lowercase.
MAX function
Description
The MAX function returns the largest value in a set of values, ignoring logical values and text.
For example,
=MAX(4,2,3)
would return 4.
Syntax
MAX(number1,number2,...)
- number - At least one is required. These are the values to find the maximum of.
MID function
Description
The MID function returns the characters from the middle of a text string, given a starting position and length.
For example,
=MID("This is a text.", 3, 5)
would return "is is".
Syntax
MID(text,start_num,num_chars)
- text - Required. The text from which you want to extract characters.
- start_num - Required. The position of the first character you want to extract. The first character in Text is 1. (Note: this is different from SUBSTRING).
- num_chars - Required. This specifies how many characters to return from Text.
MIN function
Description
The MIN function returns the smallest value in a set of values, ignoring logical values and text.
For example,
=MIN(4,2,3)
would return 2.
Syntax
MIN(number1,number2,...)
- number - At least one is required. These are the values to find the minimum of.
NOW function
Description
The NOW function returns the current date and time. Note that this is evaluated when the report is generated, not when the tag is created.
This can be added to a DATESPAN or TIMESPAN.
Syntax
NOW()
NULL function
Description
The NULL function returns a null value. This can then be compared against a value returned for DATA or DATEDATA.
Syntax
NULL()
POWER function
Description
The POWER function returns a number raised to a power.
For example,
=POWER(4,2)
would return 4 taken to the second power (16).
Syntax
POWER(number,power)
- number - Required. This is the base number.
- power - Required. This is the exponent, to which the base number is raised.
SUBSTRING function
Description
The SUBSTRING function returns the characters from the middle of a text string, given a starting position and an ending position.
For example,
=SUBSTRING("This is a test.",0,4)
would return "is".
Syntax
SUBSTRING(text,start_num,end_num)
- text - Required. The text from which you want to extract characters.
- start_num - Required. The position of the first character you want to extract. The first character in Text is 0. (Note: this is different from MID).
- end_num - Optional. This specifies the position of the last character you want to extract.
SUM function
Description
The SUM function adds all the numbers in a range of values.
For example,
=SUM(4,2,1,-1)
would return 6.
Syntax
SUM(number1,number2,...)
- number - At least one is required. This is the value to sum.
TIME function
Description
The TIME function returns the time of the given hour, minute, and second.
For example,
=TIME(14,5,0)
represents 2:05PM.
There are two major uses:
- You need a time object to add a TIMESPAN (you want to offset a time by a number of hours, minutes, and/or seconds).
- You want to use the Blueprint tag 'format' attribute to output the date in a specific format.
Syntax
TIME(hour,minute,second)
- hour - Required. The hour for the specific time.
- minute - Required. The minute for the specific time.
- second - Required. The second for the specific time.
TIMESPAN function
Description
The TIMESPAN function represents a time span.
For example,
=TIMESPAN(3,2,1)
represents a timespan of 3 hours, 2 minutes, and 1 second.
There is one major use:
- can be added to a time to provide an offset of a given number of hours, minutes, and/or seconds. A time can be obtained using the TIME, DATA, and DATEDATA functions.
Syntax
TIMESPAN(hours,minutes,seconds)
- hours - Required. The number of hours to span.
- minutes - Required. The number of minutes to span.
- seconds - Required. The number of seconds to span.
TRIM function
Description
The TRIM function returns the text with the whitespace removed from the beginning and the end.
For example,
=TRIM(" This is a test. ")
would return "This is a test".
Syntax
TRIM(text)
- text - Required. The text from which you want remove starting and ending whitespace.
TRUE function
Description
The TRUE function returns the logical value TRUE. This can used in comparisons.
For example,
=(1 == 2) = TRUE()
would return false.
Syntax
TRUE()
UPPER function
Description
The UPPER function returns the text with all letters in uppercase.
For example,
=UPPER("This is some text")
returns "THIS IS SOME TEXT".
Syntax
UPPER(text)
- text - Required. The text to convert to uppercase.
VALUE function
Description
The VALUE function returns a number from text.
For example,
=VALUE("100")
Converts the text "100" into the number 100. The number can then be used in arithmetic and aggregations functions, as well as comparisons with other numbers.
Syntax
VALUE(text)
- text - Required. The text value to convert to a number.
