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.
DATE​DATA 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.