LiveReports functions for formulas

SECURITY  Users with security levels that allow the creation of LiveReports. Any security level in Autotask can be configured with this permission. Refer to Configure access to LiveReports Designer.

NAVIGATION   > Admin > Features & Settings > LiveReports > LiveReports Designer

About formulas

You can add formulas to LiveReports manually or by selecting the Formula Editor from the Designer toolbar. The Formula Editor enables you to create your formula by selecting the desired functions from options provided. To open the page, use the path(s) in the Security and navigation section above.

LiveReport Designer offers an array of functions to customize your reports. Because there is no error checking for functions or formulas until the reports run, there are a few rules to bear in mind:

  • Equals sign =: Begin the formula with an equals sign.
  • Curly Braces {}: Include braces around all field names. If you drag and drop the field names onto your report, Autotask will automatically insert braces for you.
  • Brackets []: Include brackets around all cell names when referencing a cell in a formula or function.
  • Sums: We recommend that you only use a sum function in the footer of a report.

Here is an example that uses curly braces with field names, which multiplies the Work Entry Rate with the Work Entry Offset Hours.

EXAMPLE  = {Work Entry.Rate} * {WorkEntry.Offset Hours}

Here is an example with brackets around cell names, which multiplies cell D4 and cell D5.

EXAMPLE  = [D4] * [D5]

Functions

NOTE   When you are summing a column, use the AutoSum button. For more information on the AutoSum button, refer to Summing columns.

Function Name Description Arguments Example
AGGAVG Returns the average of the values in a field. =AGGAVG(Value)

Value: The reference cell or data field that contains the values that are averaged.		 =AGGAVG({*Ticket.Charge}) returns the average of all aggregate values for the Ticket Charge data field.
AGGCOUNT Counts the aggregate number of cells or rows of a data field.

NOTE   AGGCOUNT is an aggregate function and should be used to count the values for multiple rows of data from the same cell or for the same data field, for example =AGGCOUNT([A3]) or =AGGCOUNT({*Ticket.Ticket Number}).

 =AGGCOUNT(Value)

Value: The reference cell or data field that contains the values that are counted.		 =AGGCOUNT({*Ticket.Ticket Number}) returns the count of all aggregate values for the Ticket Number data field.
AGGDISTINCTCOUNT Returns the number of unique values in a data field. =AGGDISTINCTCOUNT(Value)

Value: The reference cell or data field that contains the values that are counted.		 =AGGDISTINCTCOUNT({*Ticket.Ticket Number}) returns the count of all aggregate unique values for the Ticket Number data field.
AGGMAX Returns the maximum value in a field. =AGGMAX(Value)

Value: The reference cell or data field that contains the values used to find the maximum value.		 =AGGMAX({Ticket.Charge}) returns the maximum value for the Ticket Charge field.
AGGMEDIAN Returns the median, or the middle value, of a data set. =AGGMEDIAN(Value)

Value: The reference cell or data field that contains the values used to find the median value.		 =AGGMEDIAN({Ticket.Charge}) returns the median value for the Ticket Charge field.
AGGMIN Returns the minimum value in a field. =AGGMIN(Value)

Value: The reference cell or data field that contains the values used to find the minimum value.		 =AGGMIN({Ticket.Charge}) returns the minimum value for the Ticket Charge field.
AGGMODE Returns the mode, or the value that appears most often, of a data set. =AGGMODE(Value)

Value: The reference cell or data field that contains the values used to find the mode value.		 =AGGMODE[{Ticket.Charge}] returns the mode value for the Ticket Charge field.

NOTE  The results of the AggMode function will appear in square brackets.
AGGSTANDARDDEVIATION Returns the standard deviation (SD) of the values in a field. SD is used to quantify the spread of the values in a data set. A lower SD indicates that values are close to the mean, and a higher SD indicates that values are more spread. =AGGSTANDARDDEVIATION(Value)

Value: The reference cell or data field that contains the values that are added.		 =AGGSTANDARDDEVIATION({*Ticket.Charge}) returns the standard deviation of all aggregate values for the Ticket Charge data field.
AGGSUM Returns the sum of the values in a field. =AGGSUM(Value)

Value: The reference cell or data field that contains the values that are added.		 =AGGSUM({*Ticket.Charge}) returns the sum of all aggregate values for the Ticket Charge data field.
AGGVARIANCE Returns the sum of the values in a field. Returns the variance of the values in a field. Variance is used to quantify the spread of the values in a data set, and is equal to the square of the standard deviation. Variance weighs outliers more heavily than standard deviation. =AGGVARIANCE(Value)

Value: The reference cell or data field that contains the values that are added.		 =AGGSUM({*Ticket.Charge}) returns the variance of the Ticket Charge data field.
MAX This function was retired. See aggMAX, above.    
MIN This function was retired. See aggMIN, above.    
RunningSum Returns a running total of the input field =RunningSum(Value)

Value: The data field that contains the values that you want to sum.
Takes one or two input:
1 is the data field that you want to sum
2 (optional) is a Data Field or Category. The running sum will reset to 0 whenever there is a new value for this second Data Field or Category		 = RunningSum({Employees.Salary}) returns running total of all the employee’s salary.
=RunningSum({Employees.Salary}, {Employees.Region}) returns a running total of employee’s salary for each region.
=RunningSum({Employees.Salary}, {Company}) – returns a running total of employee’s salary for each Company.
Function Name Description Arguments Example
ABS Returns the absolute value of a number (the number without its positive or negative sign). =ABS(Number)

Number: The real number for which you want to find the absolute value.		 =ABS(-2) returns "2".
ACOS Returns the arccosine (or inverse cosine) of a number. The returned angle is given in radians in the range 0 to PI.
 =ACOS(Number)

Number: The cosine of the angle you want. The number must be from -1 to 1.

NOTE  If you want to convert a number from radians to degrees, then multiply it by 180/PI().

 =ACOS(-1) returns "3.14159..." (or PI).

To convert to degrees:
=ACOS(-1)*(180/PI()) returns "180".
ACOSH Returns the inverse hyperbolic cosine of the given number. =ACOSH(Number)

Number: Any real number equal to or greater than 1.		 =ACOSH(10) returns "2.99322...".
ASIN Returns the arcsine (or inverse sine) of a number. The returned angle is given in radians in the range -PI/2 to PI/2. =ASIN(Number)

Number: The sine of the angle you want. The number must be from -1 to 1.

NOTE   If you want to convert a number from radians to degrees, then multiply it by 180/PI().

 =ASIN(-1) returns "-1.57079...".

To convert to degrees:
=ASIN(-1)*(180/PI()) returns "-90".
ASINH Returns the inverse hyperbolic sine of the given number. =ASINH(Number)

Number: Any real number.		 =ASIN(-2.5) returns "-1.64723...".
ATAN Returns the arctangent (or inverse tangent) of a number. The returned angle is given in radians in the range -PI/2 to PI/2. =ATAN(Number)

Number: The tangent of the angle you want. The number must be from -1 to 1.

NOTE   If you want to convert a number from radians to degrees, then multiply it by 180/PI().

 =ATAN(1) returns "0.78539...".

To convert to degrees:
=ATAN(1)*(180/PI()) returns "45".
ATAN2 Returns the arctangent (or inverse tangent) of the specified x- and y-coordinates. The returned angle is given in radians in the range -PI to PI, excluding -PI. =ATAN2(X, Y)

X: The x-coordinate of the point.

Y: The y-coordinate of the point.

NOTE  If you want to convert a number from radians to degrees, then multiply it by 180/PI().

 =ATAN2(1, 1) returns "0.78539...".

To convert to degrees:
=ATAN2(1, 1)*(180/PI()) returns "45".
ATANH Returns the inverse hyperbolic tangent of the given number. =ATANH(Number)

Number: Any real number between 1 and -1.		 =ATANH(0.5) returns "0.54930...".
CEILING Rounds the specified number up according to the specified rounding multiple. =CEILING(Number, Rounding Multiple)

Number: The number that will be rounded.

Rounding Multiple: The nearest multiple to which the Number will be rounded up.		 =CEILING(4.42, 0.05) returns "4.45".

=CEILING(2.5, 1) returns "3".
COS Returns the cosine of the given angle. =COS(Number)

Number: The angle in radians for which you would like to find the cosine.

NOTE   If you want to convert a number from radians to degrees, then multiply it by 180/PI().

 =COS(1.047) returns "0.50017...".

To convert to degrees:
=COS(1.047)*(180/PI()) returns "28.65769...".
COSH Returns the hyperbolic cosine of a number. =COSH(Number)

Number: Any real number.		 =COSH(4) returns "27.30823...".
EVEN Returns a number rounded up to the nearest even integer. =EVEN(Number)

Number: Any real number. If a decimal is entered, EVEN will round the decimal up to the next closest even integer.		 =EVEN(1) returns "2".

=EVEN(2.5) returns "4".
EXP Returns e raised to the power of a number. The constant e equals 2.71828182845904 (the base of the natural logarithm).

EXP is the inverse of LN.		 =EXP(Number)

Number: The exponent applied to the base e.

NOTE  To calculate powers of other bases, use ^ (the exponential operator).

 =EXP(1) returns "2.71828..." (the approximate value of e).

=2^2 returns "4".
FIXED Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. =FIXED(Number, Decimals)

Number: The number you want to round and convert to text.

Decimals: An optional argument that indicates the number of digits to the right of the decimal point that you would like to round. If you omit Decimals, FIXED will round to two decimal places.
 =FIXED(1234.567) returns "1,234.57".

=FIXED(1234.567, 1) returns "1,234.6".

EXAMPLE  Use the FIXED function to get a number to display as two decimals when using the number in a formula, like when you are combining a number with a string. For example: =FIXED({Revenue/Cost Item.Profitability},2) & “%”
FLOOR Rounds the specified number down according to the specified rounding multiple. =FLOOR(Number, Rounding Multiple)

Number: The number that will be rounded.

Rounding Multiple: The nearest multiple to which the Number will be rounded down.		 =FLOOR(4.42, 0.05) returns "4.4".

=FLOOR(2.5, 1) returns "2".
GlobalNumericFormat

NOTE: This function is for use with string concatenation formulas. If used in other formula types, it will not return the expected result.		 Returns a Numeric value whose format is based on the session format =GlobalNumericFormat(Value)

Value: The data field for which you would like the numeric value displayed according to your session number format.		 = "Estimated Hours: " & GlobalNumericFormat({*Ticket.Estimated Hours})

returns the ticket estimated hours in the number format of the current session.

NOTE: the results may not be as expected when used with another function.
INT Rounds the number down to the nearest integer. =INT(Number)

Number: The real number you would like to round down to an integer.		 =INT(8.9) returns "8".
LN Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).

LN is the inverse of EXP.		 =LN(Number)

Number: The positive real number for which you want to find the natural logarithm.		 =LN(86) returns "4.45434...".
LOG Returns the logarithm of a number to the base you specify. =LOG(Number, Base)

Number: The positive real number for which you want to find the logarithm.

Base: An optional argument that indicates the base of the logarithm. If no Base is specified, 10 is used.		 =LOG(10) returns "1".
LOG10 Returns the base-10 logarithm of a number. =LOG10(Number)

Number: The positive real number for which you want to find the base-10 logarithm.
 =LOG10(86) returns "1.93449...".
MOD Returns the remainder after a number is divided by a divisor. The result has the same sign as the divisor. =MOD(Number, Divisor)

Number: The number for which you want to find the remainder when divided by Divisor.

Divisor: The number by which you want to divide Number. If Divisor is 0, then MOD returns the #DIV/0! error value. 		=MOD(25, 8) returns "1".
ODD Returns a number rounded up to the nearest odd integer. =ODD(Number)

Number: Any real number. If a decimal is entered, ODD will round the decimal up to the next closest odd integer.		 =ODD(2) returns "3".

=ODD(1.5) returns "3".
PI Returns the number value for PI (3.14159...) =PI()

No arguments are needed.		 =PI() returns "3.14159..."
POWER, or ^ Returns the value of the first argument raised to the power of the second argument. =POWER(Number, Power), or
Number ^ Power

Number: The base number, which can be any real number.

Power: The exponent to which the base number is raised.		 =POWER(5, 2) returns "25".

=5^2 returns "25".
PRODUCT Multiplies all the numbers given as arguments and returns the product.

=PRODUCT(Number 1, Number 2, etc.)

Number 1, Number 2, etc.:Numbers, arrays of numbers, cell references to numbers, or text representations of numbers that you would like to multiply. You can enter up to 30 numbers. Text, logical values, or empty cells will be ignored.

 =PRODUCT(5, 2) returns "10".
QUOTIENT Returns the integer portion of a division (discards the remainder). =QUOTIENT(Number, Divisor)

Number: The base number you are going to divide.

Divisor: The number by which you will divide the base Number.		 =QUOTIENT(5, 2) returns "2".
RAND Returns an evenly-distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the report is run.

To generate a random real number between a and b, use =RAND()*(b-a)+a.		 =RAND()

No arguments are needed.		 =RAND() returns a random number between 0 and 1 (varies).

=RAND()*(5-1)+1 returns a random number between 1 and 5 (varies).
ROUND Rounds a number to a specified number of digits. =ROUND(Number, Digits)

Number: The number that you would like to round.

Digits: The number of digits to the right of the decimal point that you would like to round Number. You can use 0 to round to the nearest integer and negative numbers to round to the left of the decimal point.		 =ROUND(15.71, 1) returns "15.7".

=ROUND(15.71, 0) returns "16".

=ROUND(15.71, -1) returns "20".
SIN Returns the sine of the given angle. =SIN(Number)

Number: The angle in radians for which you would like to find the sine.

NOTE  If you want to convert a number from radians to degrees, then multiply it by 180/PI().

 =SIN(1.047) returns "0.86592...".

To convert to degrees:
=SIN(1.047)*(180/PI()) returns "49.61394...".
SINH Returns the hyperbolic sine of a number. =SINH(Number)

Number: Any real number.		 =SINH(4) returns "27.28991...".
SQRT Returns a positive square root. =SQRT(Number)

Number: The number for which you would like to find the square root. If Number is negative, SQRT returns the #NUM! error value.		 =SQRT(16) returns "4".
TAN Returns the tangent of the given angle. =TAN(Number)

Number: The angle in radians for which you would like to find the tangent.

NOTE   If you want to convert a number from radians to degrees, then multiply it by 180/PI()

.		 =TAN(1.047) returns "1.73126...".

To convert to degrees:
=TAN(1.047)*(180/PI()) returns "99.19394...".
TANH Returns the hyperbolic tangent of a number. =TANH(Number)

Number: Any real number.		 =TANH(4) returns "0.99932...".
TRUNC Truncates a number to an integer by removing the decimal part of the number. =TRUNC(Number, Digits)

Number: The number you want to truncate.

Digits: An optional argument specifying the precision of truncation. If Digits is unspecified, the default value is 0.		 =TRUNC(8.9) returns "8".

=TRUNC(8.9, 1) returns "8.9".
Function Name Description Arguments Example
DataRowCount Returns the number of rows retrieved from the data source when executing the report. =DataRowCount()

 =DataRowCount()

For example, if a report is run to retrieve basic information on 10 employees, then DataRowCount() should return 10.
DBNULL Returns DBNULL. =DBNULL()

No arguments are needed.		 =DBNULL() returns an empty (null) cell.
ISEVEN Returns whether or not a value is an even number. =ISEVEN(Value)

Value: The value you are checking to determine whether or not it is even.		 =ISEVEN(2) returns "True".
ISLOGICAL Returns whether or not a value is a logical value (TRUE or FALSE). =ISLOGICAL(Value)

Value: The value you are checking to determine whether or not it is logical.		 =ISLOGICAL(ISEVEN(2)) returns "True".
IsNoDataQualified Returns true or false depending on whether any data qualified for a specific report execution    
ISNONTEXT Returns whether or not a value is not text (blank cells are not text). =ISNONTEXT(Value)

Value: The value you are checking to determine whether or not it is not text.		 =ISNONTEXT(2) returns "True".
ISNULL Returns True if the argument is NULL or DbNULL. Otherwise returns False. =ISNULL(Value)

Value: The value you are checking to determine whether or not it is null.		 =ISNULL() returns "True".

=ISNULL("data") returns "False".
ISNUMBER Returns whether or not a value is a number. =ISNUMBER(Value)

Value: The value you are checking to determine whether or not it is a number.		 =ISNUMBER(2) returns "True".

=ISNUMBER("Not a number") returns "False".
ISODD Returns whether or not a value is an odd number. =ISODD(Value)

Value: The value you are checking to determine whether or not it is odd.		 =ISODD(3) returns "True".
ISTEXT Returns whether or not a value is text. =TEXT(Value)

Value: The value you are checking to determine whether or not it is text.		 =ISTEXT("Text") returns "True".
NULL Returns a null value. =NULL()

No arguments are needed.		 =NULL() returns null.
TYPE Returns the type of a value. =TYPE(Value)

Value: The value you are checking to determine its type.

Types:
1 = Number
2 = Text
4 = Logical Value (TRUE or FALSE)
16 = Error Value (i.e. #VALUE!)
64 = Array		 =TYPE(2) returns "1".

=TYPE("This is text.") returns "2".

IMPORTANT  Normal comparison operators (=, <>, >, <) may not return consistent results when applied to dates in formulas in LiveReports. For the most reliable solution, use the DATEDIFF function to set up date comparisons in formulas.
For example:
IF(DATEDIFF(“d”,{*Ticket.Create Date}, Today()) <>0, 1, 0)
With this formula, the result from DATEDIFF is positive if the first date is before the second date,
and negative if the first date is later than the second,
and equal to zero if the dates are equal.

NOTE   If your date format is DD/MM/YYYY and you wish to have two date functions, you will need to add the word 'false.' For example, MONTH(NOW(false)). Otherwise, a singular date function can be written as below.

Function Name Description Arguments Example
DATE Returns the date and time entered as arguments in Gregorian calendar format, according to the server's local time zone.

NOTE  This function should be used to represent a date to other functions instead of representing a date as text, as text dates can lead to problems

.		 =DATE(Year, Month, Day)

Year: One to four digits between 1 and 9999, representing the year.

Month: One to two digits between 1 and 12, representing the month.

Day: One to two digits between 1 and the number of days in the month specified.

NOTE   Values entered that are outside of any of the arguments' legal ranges will return an error.

IMPORTANT  =IF({date1}<{date2},True(),False()) DOES NOT WORK. You have to use the DateDiff function =IF(DATEDIFF(“d”,{date1},{date2}),True(),False()).

=DATE(2025, 2, 22) returns "02/22/2025 12:00:00 AM".

If used as part of another function, =YEAR(DATE(2025, 2, 22)) returns "2025".
DATEADD Returns the calculated date after a specified time interval has been added. =DATEADD(Interval, Number, Start Date)

Interval: A string expression that represents the time interval you would like to add. Options include:

Years = "yyyy"
Quarters = "q"
Months = "m"
Weeks = "ww"
Days = "d" or "y" (day of year) or "w" (weekday)
Hours = "h"
Minutes = "n"
Seconds = "s"

NOTE  You must include quotation marks "" around your interval.


Number: The number of intervals used to advance the start date. Use positive numbers to advance the date and negative numbers to get dates/times in the past. For hours, minutes, and seconds, you can use fractional intervals.

Start Date: The date to which you would like the time interval added or subtracted.

=DATEADD("yyyy", 1, DATE(2024, 2, 22)) returns "02/22/2025 12:00:00 AM"
(advances the date by one year).

=DATEADD('d',-1,Today()) returns yesterday's date
DATECUSTOMFORMAT Modifies a date value to be output in a specified format. Defaults to MM/dd/yy format if no alternative format is specified. Cells that use this function must be formatted as Text. =DATECUSTOMFORMAT(Date,Format)

Date: The system value of a date (non-text). Use the DATE function or other functions to convert the date to be used in this function.		 =DATECUSTOMFORMAT(DATE (2025, 2, 21),""MM-dd-yy") returns the string "02-21-25".
DATEDIFF Returns a whole number representing the number of time intervals between two date values.

NOTE  The results of the DATEDIFF function may appear with two decimal places if the cell is formatted as a General field. To display DATEDIFF as an integer, right-click on the cell and select Format Cells > Number. In Decimal Places, select "0" and click OK.

=DATEDIFF(Interval, Start Date, End Date, First Day of Week, First Week of Year)

Interval: A string expression that represents the time interval you would like to use when calculating the difference. Options include:

Years = "yyyy"
Quarters = "q"
Months = "m"
Weeks of the Year= "ww"
Weeks = "w"
Days = "d" or "y"
Hours = "h"
Minutes = "n"
Seconds = "s"

NOTE   You must include quotation marks "" around your interval.


Start Date: The date the interval starts, which will be subtracted from the End Date.

End Date: The date the interval ends, which will have the Start Date subtracted from it.

First Day of Week: An optional argument that indicates the first day of the week, with 1 representing Sunday and 7 representing Saturday. If unspecified, Sunday will be used. If 0 is used, the first day of the week will be configured according to the local server's settings.

First Week of Year: An optional argument that indicates the first week of the year, where:
1 = January 1st
2 = The week having at least four days
3 = The first full week

If unspecified, January 1st is used. If 0 is used, the first week of the year will be configured according to the local server's settings.
 =DATEDIFF("m", DATE(2024, 2, 22), DATE(2025, 2, 22)) returns "12" (the number of months between the two dates).
DATEVALUE Returns the date and time value from a text representation of that date.

NOTE  Use DATEVALUE to convert a date represented in text to a date format that can be used by other functions.

 =DATEVALUE(Date Text)

Date Text: The text representing a date. Use quotation marks "" around string formats (i.e. "22-Feb-2023").

The day can only appear before the month if the month is spelled out (non-numerical).

Any time information entered in the Date Text is ignored. The time returned will always represent midnight (in the server's local time).

If the year portion of Date Text is omitted, DATEVALUE uses the current year on the server.

NOTE  If you receive a #VALUE! error, that means the Date Text appears out of range.

 =DATEVALUE("22-Feb-2025") returns "02/22/2025 12:00:00 AM".
DAY Returns the day portion of a date as a whole number. =DAY(Date)

Date: The system value of a date (non-text). Use the DATE function or other functions to convert the date to be used in this function.		 =DAY(DATE(2025, 2, 22)) returns "22".
DAYS360 Returns the number of days between two dates based on a 360-day year (with 12 30-day months).

NOTE  This function is useful with some accounting applications. Use it to compute payments if your accounting system is based on 12 30-day months.

 =DAYS360(Start Date, End Date, Method)

Start Date: The date the interval starts, which will be subtracted from the End Date. If the start date occurs after the End Date, DAYS360 will return a negative number.

End Date: The date the interval ends, which will have the Start Date subtracted from it.

NOTE  Use the DATE function or other functions to convert the date to be used in this function. Any time component is interpreted in the server's local time.



Method: An optional argument that indicates whether to use the European method of computation instead of the American (US NASD) method. Each produces slightly different answers when either the Start Date or End Date occurs on the 31st day of the month. When this argument is omitted, the default value is "FALSE".
 =DAYS360(DATE(2024, 2, 22), DATE(2025, 2, 22)) returns "360".
GLOBALDATEFORMAT

NOTE: This function is for use with string concatenation formulas. If used in other formula types, it will not return the expected result.		 Returns a date with a format based on the session format.

 GLOBALDATEFORMAT(Date)

Date: The date that you would like displayed according to your session date format. This date would typically be a data variable.		 ="Contract Start Date: " & GLOBALDATEFORMAT({Contract.Start Date})
returns the contract start date in the date format of the current session.
GLOBALDATETIMEFORMAT

NOTE: This function is for use with string concatenation formulas. If used in other formula types, it will not return the expected result.		 Returns a date/time value with a format based on the session format. =GLOBALDATETIMEFORMAT(DateTime)

DateTime: The date/time that you would like displayed according to your session date/time format. This date/time parameter would typically be a data variable.		 =”Ticket Create Time: “ & GLOBALDATETIMEFORMAT({*Ticket.Create Date/Time})returns the ticket creation date and time in the date/time format of the current session.
GlobalNumericFormat

NOTE: This function is for use with string concatenation formulas. If used in other formula types, it will not return the expected result.		 Returns a Numeric value whose format is based on the session format =GlobalNumericFormat(Value)

Value: The data field for which you would like the numeric value displayed according to your session number format.		 = "Estimated Hours: " & GlobalNumericFormat({*Ticket.Estimated Hours})

returns the ticket estimated hours in the number format of the current session.

NOTE: the results may not be as expected when used with another function.
HOUR Returns the hour of a time value. The hour is given as a whole number (rounded down), ranging from 0 (12:00 AM) to 23 (11:00 PM). Hours past noon are always returned based on a 24-hour clock. =HOUR(Time)

Time: The time value of the hour you would like to find. Times may be entered as text strings within quotation marks (for example, "5:55 PM") or received from other formulas and functions (for example, TIMEVALUE("5:55 PM")).		 =HOUR("2:50:05 PM") returns "14" (2 + 12 for military time).
MINUTE Returns the minutes of a time value. The minutes are given as a whole number (rounded down), ranging from 0 to 59. =MINUTE(Time)

Time: The time value of the minutes you would like to find. Times may be entered as text strings within quotation marks (for example, "5:55 PM") or received from other formulas and functions (for example, TIMEVALUE("5:55 PM")).		 =MINUTE("2:50:05 PM") returns "50".
MONTH Returns the month portion of a date as a whole number, ranging from 1 (January) to 12 (December). =MONTH(Date)

Date: The system value of a date (non-text). Use the DATE function or other functions to convert the date to be used in this function.		 =MONTH(DATE(2025, 2, 22)) returns "2".
MONTHNAME Returns the name of the month for a given date. =MONTH(Date)

Date: Accepts a full date or date formatted string, or simply an integer representing the month.

The month name is returned in the current language.

 =MONTHNAME("2/21/25") returns "February".

=MONTHNAME("2") returns "February".
NOW Returns the current date and time (in the server's local time). =NOW(Format)

Format: An argument that indicates whether the date should be formatted based on the session's date/time format.

You should use the value 'false' or 0 for this argument, especially if you are using nested date/time functions (for example, =MONTH(TODAY(‘false’))).		 =NOW('false') or =NOW(0) returns "02/22/2024 10:48:15 AM" (assuming the function was executed on February 22, 2024 at 10:48 AM).
QUARTERNAME Returns the fiscal quarter for a given date, as text from Q1 to Q4. =QUARTERNAME(Date)

Date: Accepts a full date or date formatted string.		 =QUARTERNAME("02/22/2024") returns Q1
QUARTERNUMBER Returns the fiscal quarter for a given date, asa number from 1 to 4. =QUARTERNUMBER(Date)

Date: Accepts a full date or date formatted string.		 =QUARTERNUMBER("02/22/2024") returns 1
SECOND Returns the seconds of a time value. The seconds are given as a whole number (rounded down), ranging from 0 to 59. When a time omits seconds, 0 is assumed. =SECOND(Time)

Time: The time value of the seconds you would like to find. Times may be entered as text strings within quotation marks (for example, "5:55 PM") or received from other formulas and functions (for example, TIMEVALUE("5:55 PM")).		 =SECOND("2:50:05 PM") returns "5".
TIME Returns a time value for a particular time based on its number of hours, minutes, and seconds.

NOTE  Use TIME to create durations and time periods to be added to other date/time values.

 =TIME(Hour, Minutes, Seconds)

Hour: A number from 0 to 23 representing the hour in military time (any value greater than 12 is interpreted as being in the afternoon).

Minutes: A number from 0 to 59 representing the minutes past the hour.

Seconds: A number from 0 to 59 representing seconds past the minute.

NOTE  When the values for these arguments are outside the listed ranges, a #VALUE! error is returned.

 =SECOND(TIME(14, 50, 5)) returns "5".
TIMEFORMAT1 Returns the time value of a date/time parameter formatted as "hh:mm tt". This should be used to represent time to other functions rather than representing time as text. =TIMEFORMAT1(DateTime)

DateTime: The date/time for which you would like to return the time value.

NOTE  The cell containing this function should be formatted as Text. Cells formatted as General or Date may produce unreliable results.

 =TIMEFORMAT1({Appointment.DateTime}) returns the time value for the date/time parameter "Appointment.DateTime".
TIMEVALUE Returns the value for the time portion of a string text representing the time.

NOTE  Time values can be added to other time or date/time values.

 =TIMEVALUE(String Text)

String Text: A text string within quotation marks that represents a time. Acceptable formats include "5:55 PM" and "17:55".

If AM/PM is not present, AM is assumed unless the hour exceeds 12 (military time). Specification of seconds is optional. A time separator is mandatory ("17:00" is acceptable, "1700" is not). Do not use periods when specifying AM and PM (A.M. and P.M. will produce an error).

Any date information in String Text is ignored. Time values are a portion of a date/time value. When the return value from TIMEVALUE is treated as a date, it is based on the minimum date: January 1st, 1 AD.

NOTE  The cell containing this function should be formatted as Text. Cells formatted as General or Date may produce unreliable results.

 =HOUR(TIMEVALUE("17:00")) returns "17".
TODAY Returns today's date, represented in the server's local time.

NOTE  This function returns a date only, with no time component. If you would like date and time together, use the NOW function.

 =TODAY(Format)

Format: An argument that indicates whether the date should be formatted based on the session's date/time format.

You should use the value 'false' or 0 for this argument, especially if you are using nested date/time functions (for example, =MONTH(TODAY(‘false’))).		 =TODAY('false') or =TODAY(0) returns "02/22/2024" (assuming the function was executed on February 22, 2024).
YEAR Returns the year portion of a date as a whole number, ranging from 1 to 9999. =YEAR(Date)

Date: The system value of a date (non-text). Use the DATE function or other functions to convert the date to be used in this function.

Values exceeding the year 9999 will result in an error.		 =YEAR(DATE(2024, 2, 22)) returns "2024".
Function Name Description Arguments Example
DB Returns the depreciation of a device for a specified period using the fixed-declining balance method.

DB computes depreciation at a fixed rate using the following formulas:

(cost - total depreciation from prior periods) * rate

rate = 1 - ((salvage / cost) ^ (1 / life)), rounded to three decimal places

Depreciation for the first and last periods are special cases. For the first period, DB uses this formula: cost * rate * month / 12. For the last period, DB uses this formula: ((cost - total depreciation from prior periods) * rate * (12 - month)) / 12.		 =DB(Cost, Salvage, Life, Period, Month)

Cost: The initial cost of the device.

Salvage: The value at the end of the depreciation.

Life: The number of periods over which the device is being depreciated.

Period: The period for which you want to calculate the depreciation (must use the same units as Life).

Month: An optional argument for the number of months in the first year. If Month is omitted, 12 is used.		 =DB(1000000, 100000, 6, 1, 7) returns "186,083.33".
DDB Returns the depreciation of a device for a specified period using the double-declining balance method, or some other method you specify.

DDB computes depreciation at an accelerated rate using the following formula:

((cost - salvage) - total depreciation from prior periods) * (factor / life).		 =DDB(Cost, Salvage, Life, Period, Factor)

Cost: The initial cost of the device.

Salvage: The value at the end of the depreciation.

Life: The number of periods over which the device is being depreciated.

Period: The period for which you want to calculate the depreciation (must use the same units as Life).

Factor: The rate at which the balance declines. If Factor is omitted, 2 is used.

NOTE  All five arguments must be positive numbers.

 =DDB(2400, 300, 10, 1, 2) returns "480.00".
FV Returns the future value of an investment based on periodic, constant payments and a constant interest rate. =FV(Rate, Number of Periods, Payment Amount, Present Value, Payment Due)

Rate: The interest rate per period.

Number of Periods: The total number of payment periods in an annuity.

Payment Amount: An optional argument that indicates the payment amount made each period, which cannot change over the life of an annuity. The Payment Amount typically contains principal and interest, but no other fees or taxes. If Payment Amount is omitted, you must include the Present Value argument (see below).

Present Value: An optional argument that indicates the lump-sum amount that a series of future payments is worth right now. If Present Value is omitted, 0 is assumed and you must include the Payment Amount argument (see above).

Payment Due: An optional argument indicating when payments are due by using the number 0 or 1. If Payment Due is omitted, 0 is assumed. Zero means the end of the period, and 1 means the beginning of the period.		 =FV(.06/12, 10, -200, -500, 1) returns "2581.40".

The example above assumes a 6% annual interest rate, 10 payments, -$200 per payment, -$500 for the present value, and payments being due at the beginning of the period (1).
INTRATE Returns the interest rate for a fully invested security.

NOTE  Dates should be entered by using the DATE function, or as results of other formulas or functions.

 =INTRATE(Settlement Date, Maturity Date, Investment, Redemption Value, Basis)

Settlement Date: The date a buyer purchases a security, coupon, or bond.

Maturity Date: The date when a security expires.

Investment: The amount invested in the security.

Redemption Value: The amount to be received at maturity.

Basis: The type of day count basis to use.		 =INTRATE(DATE(2022, 2, 15), DATE(2022, 5, 15), 1000000, 1014420, 2) returns "0.05768" or "5.77%".
IPMT Returns the interest payment for a given period of an investment based on periodic, constant payments and a constant interest rate. =IPMT(Rate, Period, Number of Periods, Present Value, Future Value, Payment Due)

Rate: The interest rate per period.

Period: The period for which you want to find the interest. This number must be between 1 and Number of Periods.

Number of Periods: The total number of payment periods in an annuity.

Present Value: The lump-sum amount that a series of future payments is worth right now.

Future Value: An optional argument that indicates the future value or cash balance you want to attain after the last payment is made. If Future Value is omitted, 0 is assumed (as though you had paid off a loan).

Payment Due: An optional argument indicating when payments are due by using the number 0 or 1. If Payment Due is omitted, 0 is assumed. Zero means the end of the period, and 1 means the beginning of the period.
 =IPMT(.1/12, 3, 3, 8000) returns "-22.41".

The example above assumed 10% annual interest, the third period, a three-year loan, and an $8,000 present value.
NPER Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. =NPER(Rate, Payment Amount, Present Value, Future Value, Payment Due)

Rate: The interest rate per period.

Payment Amount: The payment made each period, which cannot change over the life of an annuity. The Payment Amount typically contains principal and interest, but no other fees or taxes.

Present Value: The lump-sum amount that a series of future payments is worth right now.

Future Value: An optional argument that indicates the future value or cash balance you want to attain after the last payment is made. If Future Value is omitted, 0 is assumed (as though you had paid off a loan).

Payment Due: An optional argument indicating when payments are due by using the number 0 or 1. If Payment Due is omitted, 0 is assumed. Zero means the end of the period, and 1 means the beginning of the period.		 =NPER(.12/12, -100, -1000, 10000, 1) returns "60".

The example above assumes a 12% annual interest rate, $100 payments, -$1,000 present value, $10,000 future value, and that payment is due at the beginning of the period.
NPV Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). =NPV(Rate, Value 1, Value 2, etc.)

Rate: The rate of discount over the length of one period.

Value 1, Value 2, etc.: Up to 29 arguments representing the payments and income. These arguments must be equally spaced in time and occur at the end of each period. The order of these arguments indicates the order of cash-flows, so sequence is important.		 =NPV(.1, -10000, 7000, 5000) returns "450.79".

The above example assumes an annual discount rate of 10%, an initial cost of investment of $10,000, a return of $7,000 one year later, and a return of $5,000 one year after that.
PMT Calculates the payment for a loan based on constant payments and a constant interest rate. =PMT(Rate, Number of Payments, Present Value, Future Value, Payment Due)

Rate: The interest rate for the loan.

Number of Payments: The total number of payments for the loan.

Present Value: The lump-sum amount that a series of future payments is worth right now.

Future Value: An optional argument that indicates the future value or cash balance you want to attain after the last payment is made. If Future Value is omitted, 0 is assumed (as though you had paid off a loan).

Payment Due: An optional argument indicating when payments are due by using the number 0 or 1. If Payment Due is omitted, 0 is assumed. Zero means the end of the period, and 1 means the beginning of the period.		 =PMT(.08/12, 10, 10000) returns "-1037.03".

The example above assumes an 8% annual interest rate, 10 total payments, and a $10,000 loan.
PPMT Returns the payment on the principal for a given period of an investment based on periodic, constant payments and a constant interest rate. =PPMT(Rate, Period, Number of Periods, Present Value, Future Value, Payment Due)

Rate: The interest rate for the period.

Period: The period for which you would like to find the payment, ranging from 1 to Number of Periods.

Number of Periods: The total number of payment periods in an annuity.

Present Value: The lump-sum amount that a series of future payments is worth right now.

Future Value: An optional argument that indicates the future value, or cash balance you want to attain after the last payment is made. If Future Value is omitted, 0 is assumed (as though you had paid off a loan).

Payment Due: An optional argument indicating when payments are due by using the number 0 or 1. If Payment Due is omitted, 0 is assumed. Zero means the end of the period, and 1 means the beginning of the period.		 =PPMT(.1/12, 1, 2*12, 2000) returns "-75.62".

The example above assumes a 10% annual interest rate, you are looking for a payment for one month of the loan, the loan is over a two-year period, and the amount of the loan is $2,000.
PV Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender. =PV(Rate, Number of Periods, Payment Amount, Future Value, Payment Due)

Rate: The interest rate per period.

Number of Periods: The total number of payment periods in an annuity.

Payment Amount: An optional argument that indicates the payment amount made each period, which cannot change over the life of an annuity. The Payment Amount typically contains principal and interest, but no other fees or taxes. If Payment Amount is omitted, you must include the Future Value argument (see below).

Future Value: An optional argument that indicates the future value or cash balance you want to attain after the last payment is made. If Future Value is omitted, 0 is assumed and you must include the Payment Amount argument (see above).

Payment Due: An optional argument indicating when payments are due by using the number 0 or 1. If Payment Due is omitted, 0 is assumed. Zero means the end of the period, and 1 means the beginning of the period.		 =PV(.08/12, 12*20, 500, 0, 0) returns "-59777.15".

The example above assumes an annual interest rate of 8%, a 20-year loan, $500 per month payments, and payments due at the end of the period.
RATE Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. =RATE(Number of Periods, Payment Amount, Present Value, Future Value, Payment Due)

Number of Periods: The total number of payment periods in an annuity.

Payment Amount: The payment made each period, which cannot change over the life of an annuity. The Payment Amount typically contains principal and interest, but no other fees or taxes.

Present Value: The lump-sum amount that a series of future payments is worth right now.

Future Value: An optional argument that indicates the future value or cash balance you want to attain after the last payment is made. If Future Value is omitted, 0 is assumed (as though you had paid off a loan).

Payment Due: An optional argument indicating when payments are due by using the number 0 or 1. If Payment Due is omitted, 0 is assumed. Zero means the end of the period, and 1 means the beginning of the period.		 =RATE(4*12, -200, 8000) returns "0.008" or "1%".

The example above assumes a four-year loan, $200 monthly payments, and an $8,000 loan.
SLN Returns the straight-line depreciation of a device for one period. =SLN(Cost, Salvage, Life)

Cost: The initial cost of the device.

Salvage: The value at the end of the depreciation.

Life: The number of periods over which the device is being depreciated.		 =SLN(30000, 7500, 10) returns "2250".
SYD Returns the sum-of-years' digits depreciation of a device for a specified period. =SYD(Cost, Salvage, Life, Period)

Cost: The initial cost of the device.

Salvage: The value at the end of the depreciation.

Life: The number of periods over which the device is being depreciated.

Period: The period for which you want to calculate the depreciation (must use the same units as Life).		 =SYD(30000, 7500, 10, 1) returns "4090.91".
Function Name Description Arguments Example
AND Returns TRUE if all arguments are TRUE. Returns FALSE if one of more arguments are FALSE. =AND(Logical Value 1, Logical Value 2, etc.)

Logical Value 1, 2, etc.: Arguments that evaluate to a logical value (TRUE or FALSE). The arguments can include arrays.

If the arguments contain text or empty cells, those values will be ignored.

Non-logical values will return a #VALUE! error value.		 =AND(ISEVEN(2), ISODD(3)) returns "True".

=AND(ISEVEN(2), ISODD(2)) returns "False".
FALSE Returns the logical value FALSE. =FALSE()

No arguments are needed.		 =FALSE() returns "False".
IF Returns one value if a condition you specify evaluates to TRUE, and another value if the condition evaluates to FALSE.

You can use IF on values and formulas. You can also use nested IF functions.		 =IF(Condition, If True, If False)

Condition: The logical test that can be evaluated to either TRUE or FALSE (i.e. [A10]=100).

If True: The value that will be returned if the Condition evaluates to TRUE.

If False: The value that will be returned if the Condition evaluates to FALSE.		 =IF(ISEVEN(2), "It's even.", "It's odd.") returns "It's even."
NOT Reverses the value of its argument.

You can use NOT to make sure a value is not equal to one particular value.		 =NOT(Logical Value)

Logical Value: A value or expression that evaluates to either TRUE or FALSE.		 =NOT(ISEVEN(2)) returns "False" (the opposite of the returned logical value).
OR Returns TRUE if any argument is TRUE. Returns FALSE if all arguments are FALSE. =OR(Logical Value 1, Logical Value 2, etc.)

Logical Value 1, 2, etc.: Arguments that evaluate to a logical value (TRUE or FALSE). The arguments can include arrays.

If the arguments contain text or empty cells, those values will be ignored.

Non-logical values will return a #VALUE! error value.		 =OR(ISEVEN(2), ISODD(3)) returns "True".

=OR(ISEVEN(2), ISODD(2)) returns "True".

=OR(ISEVEN(1), ISODD(2)) returns "False".
SWITCH An alternative to nested If() functions where you have multiple conditions to evaluate =Switch
Takes any even number of arguments.
The first argument is the test value to compare to, the second argument is the default value. Each subsequent pair of arguments represents a condition and a return value.
For each subsequent pair, the condition argument is compared to the test value (first argument). If the condition value is equal to the test value, the return value (second argument of the pair) is returned. Otherwise, the next pair of arguments are evaluated. If no condition arguments match, the default value is returned.		 Switch({*Opportunity.Projected Close Month},"Fourth Quarter","1","January","2","February","3","March","4","April",
"5","May","6","June","7","July","8","August","9","September")
TRUE Returns the logical value TRUE. =TRUE()

No arguments are needed.		 =TRUE() returns "True".
Function Name Description Arguments Example
& Concatenates (joins) strings, cell reference, or another function. The text strings, cell reference or function you would like to join.

To add a space between the strings, add " " as an argument between the text strings.		 ="Autotask" & " " & "LiveReport" returns "Autotask LiveReport".
+ Addition operator No arguments are needed. =2+2 returns "4".
- Subtraction operator No arguments are needed. =3-2 returns "1"
* Multiplication operator No arguments are needed. =3*2 returns "6"
/ Division operator No arguments are needed. =6/3 returns "2"
Function Name Description Arguments Example
CONCATENATE, or & Joins several text strings into one text string. =CONCATENATE(String 1, String 2, etc.), or
=String 1 & String 2

String 1, 2, etc.: The text strings that you would like to join. You can add spaces between strings by adding " " as an argument between the strings.

NOTE  If you are passing an actual string and not a cell reference or other function, be sure to use quotation marks "" before and after the string.

 =CONCATENATE("Live", " ", "Reports") returns "Live Reports".

="Live" & " " & "Reports" returns "Live Reports".
CONTAINS Returns a value indicating whether or not the text in the second argument can be found within the text in the first argument. =Contains(String 1, String 2) =CONTAINS("LiveReports","Report") returns "TRUE"
LEFT Returns the first (leftmost) character or characters in a text string, based on the number of characters you specify. =LEFT(Text, Number)

Text: The text string that contains the characters you want to extract. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.

Number: The number of characters you want to extract, from left to right. This number must be greater than or equal to 0. If the number is greater than the total length of Text, LEFT returns all characters in Text. If Number is omitted, 1 is assumed.		 =LEFT("Sale Price", 4) returns "Sale".

=LEFT("Sweden") returns "S".
LEN Returns the number of characters in a text string. =LEN(Text)

Text: The text string for which you would like to find the length. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.		 =LEN("1234") returns "4".
LOWER Converts all uppercase letters in a text string to lowercase. =LOWER(Text)

Text: The text string that you would like to change to lowercase. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.		 =LOWER(M.A. MacLean) returns "m.a. maclean".
MID Returns a specific number of characters from a text string, starting at the position you specify and based on the number of characters you specify. =MID(Text, Start, Number)

Text: The text string that contains the characters you want to extract. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.

Start: The position of the first character you want to extract. The first character in Text has a position number of 1. If Start is greater than the text length, MID returns empty text (""). If Start is less than 1, MID returns a #VALUE! error.

Number: The number of characters you want MID to extract from the text. If Start plus Number exceeds the length of text, MID returns all characters beginning at Start and through the end of Text. If Number is negative, MID returns a #VALUE! error.		 =MID("Live Reports", 6, 3) returns "Rep".
NewLine Inserts a line break into a cell, works for any export type (use in place of e.g. '\n' or HTML 'br')   ={Account.Account Name} & NewLine() & {Account.Address 1}
REPLACE Replaces part of a text string, based on the number of characters you specify, with a different text string. =REPLACE(Old Text, Start, Number, New Text)

Old Text: The text containing the characters you would like to replace. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.

Start: The position of the first character in Old Text that you want to replace with New Text. The first character in Old Text has a position number of 1.

Number: The number of characters in Old Text that you want to replace with New Text.

New Text: The text with the characters that you would like to insert into Old Text. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.		 =REPLACE("Total Company Revenue", 7, 7, "Customer") returns "Total Customer Revenue".
REPLACE2 The function takes the target string as the first argument and replaces the text in argument 2 with that text in argument 3. Replace2(,,) Replace2({Organization.Organization Name},"A1","A2")
where the organization name is "A1 Facilities Management" returns "A2 Facilities Management"
RIGHT Returns the last (rightmost) character or characters in a text string, based on the number of characters you specify. =RIGHT(Text, Number)

Text: The text string that contains the characters you want to extract. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.

Number: The number of characters you want to extract, from right to left. This number must be greater than or equal to 0. If the number is greater than the total length of Text, RIGHT returns all characters in Text. If Number is omitted, 1 is assumed.		 =RIGHT("Sale Price", 5) returns "Price".

=RIGHT("Sweden") returns "n".
TRIM Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing (such as spaces before or after the text). =TRIM(Text)

Text: The text from which you would like the spaces removed. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.		 =TRIM(" Company Manager ") returns "Company Manager".
UPPER Converts all lowercase letters in a text string to uppercase. =UPPER(Text)

Text: The text string that you would like to change to uppercase. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.		 =UPPER("total") returns "TOTAL".
VALUE Converts a text string that represents a number to a number. =VALUE (Text)

Text: The text you would like to convert. You can use cell references or other functions as your text, but if you enter a string, be sure to use quotation marks "" around the string.		 =VALUE("$1,000") returns "1000" (as a number).
Function Name Description Arguments Example
Bold Applies the bold formatting to the input. =Bold(value) =Bold('this is bold') returns
this is bold
FormatCurrency Applies the currency formatting from your Autotask instance to the LiveReport. Takes 3 arguments:
  • The first parameter is the numeric value or field to be formatted.
  • The optional second parameter can be a data field such as {Organization.Currency Display Symbol} or currency abbreviation or symbol that you provide, such as 'USD' or '$'.
  • The optional 3rd parameter must be the 3-letter ISO code of an active currency in your Autotask instance. You can use the {Organization.Currency} field for this value. This is used to apply the positive and negative number formatting for customer currencies.

If your Autotask instance is single-currency, the number (amount) is formatted according to the positive and negative currency format specified for your Autotask instance. The second and third parameters are intended for multi-currency Autotask instances.

NOTE  The majority of our customers have a single-currency Autotask instance and do not need to use the second and third parameters.

NOTE  Multi-currency customers should use the second and third parameters to display the customer currency symbol and apply the correct formatting to the amount.

NOTE  Autosum will not work with this function. FormatCurrency must be the outer function, as in =FormatCurrency(AggSum({fieldname}))

 

 Single currency:

=FormatCurrency({Invoice.Total Billable Amount})

Multi-currency:
=FormatCurrency({*Posted Billing Item.Billed Amount},{Organization.Currency Display Symbol},{Organization.Currency})
Italic Applies the italic formatting to the input. =Italic(value) =Italic('this is italic') returns
this is italic
Underline Applies the underline formatting to the input. =Underline(value) =Underline('this is underlined') returns
this is underlined

 

Function Name Description Arguments Example
EXCELFORMULA Passes the input text through as an excel formula when exporting to Excel.

IMPORTANT  When the LiveReport is exported to Excel, this function does not update cell references which can lead to unintended values appearing in reports. For example, in repeated detail rows, the cell references will be for the first row and not updated for the following rows.

 =EXCELFORMULA('Excel function') =EXCELFORMULA('A1*A2') returns the value of A1 * A2.

Note the use of apostrophes around the excel function, and that there is no equals sign within the argument.
EXPORTTYPE Returns the format the report is being exported as. This is useful for conditionally suppressing report sections by report type. =EXPORTTYPE()
 =EXPORTYPE() will return the type of report being exported. For example, HTML, PDF, RTF or Excel.
FILTERVALUE Returns the current value of a filter. =FILTERVALUE(Index1, Index2)

Index1: Optional argument that indicates the index of the filter. Indexes begin with 1.

Index2: Optional argument that indicates the sub-index for filters that contain multiple values (i.e. "between" or "one of"). Indexes begin with 1.

Index3: Optional. A true/false indicator that determines whether the value, when it is a date or numeric value, should be formatted using the logged in user's preferred DATE or NUMBER format.
If 1 the value should be formatted according to the user's settings. If 0 (zero), or if no value is provided, the value should not be formatted. 		If the filter summary has two filters:

1. OrderDetail.UnitPrice > "3.6"
2. Products.ProductName is one of ("Server", "Desktop")

Then =FILTERVALUE(2, 2) returns "Desktop" (the second value of the second filter).

If the filter summary has 3 filters:
1. Organization.OrganizationType ="Client"
2. Organization.Last Activity Date is between 01/01/2021 and 12/31/2021

3. =FILTERVALUE(2,2,1) returns "12/31/2021" formatted in the user's preferred date format.
HYPERLINK

This function creates a hyperlink that opens an external website. When used with Autotask data fields, the link can open an item in Autotask, like a ticket or the Organization page.

If the user is not already logged into Autotask, the login page will open first.

NOTE  The hyperlink works with all LiveReport output options. The Excel and Word output, however, may require some users to apply a minor fix. For details, refer to this article.

NOTE  For HTML Report output only: Some sub-categories in LiveReports include a field that functions as a hyperlink to open an Autotask item, for example, Ticket Number Link. Ticket Number Link displays the ticket number returned by the report and acts as a hyperlink to the associated ticket detail page. Check the Data Field Dictionary to find link fields. Link fields work only in HTML output.

NOTE  When a hyperlink or linked field opens an item in Autotask, the data availability respects the security level permissions of the logged in user.

 =HyperLink(Url, display text)

Url: A sub-category and associated Url. Options include:

Company.Url
Company Team Member.Email Url
Contact.Email Url Contract.Url
Employee.Email Url
Installed Product.Url
Opportunity.Url
Project.Resource.Email Url
Task.Url
Task Or Ticket.Url
Ticket.Url
Work Entry.Url

Display text: the text to display in the cell.		 =Hyperlink({*Ticket.Url},{*Ticket.Ticket Number})
will display the Ticket Number as an active link. On click, the ticket detail page opens.
ISBLANK Returns whether or not a value refers to an empty cell. =ISBLANK(Cell)

Cell: The cell you are checking to determine whether or not it is empty.		 =ISBLANK([A3]) returns "True" if cell A3 contains "=DBNULL()" (an empty cell).

=ISBLANK([A4]) returns "False" if cell A4 contains data.
ISNULL Returns whether or not a value is null. =ISNULL(Value)

Value: The value you are checking to determine whether or not it is null.		 =ISNULL(2) returns "False".
LOADIMAGE Loads a server-side image in the cell based on the input path. =LOADIMAGE(ImgPath)

ImgPath: The server-side path for the image.		 =LOADIMAGE("c:/MyPhoto.jpg") will display the photo named MyPhoto stored on your C drive.
PAGENUMBER Returns the current page number for HTML, PDF, and RTF Express reports.

It is equivalent to @pageNumber@.

 =PAGENUMBER()
 =PAGENUMBER() will return the appropriate page number on each page of the report output.

ResourceAvailability

Returns the total resource availability for a given date range based on their individual daily availability.

=ResourceAvailability(Start Date, End Date,{Resource.Availability - Sunday},{Resource.Availability - Monday},{Resource.Availability - Tuesday},{Resource.Availability - Wednesday},{Resource.Availability - Thursday},{Resource.Availability - Friday},{Resource.Availability - Saturday})

Start Date - The start date of the date range. A Date field or FilterValue can be used. If entering a calendar date, enter it in "yyyy-mm-dd" format.

End Date - The end date of the date range. A Date field or FilterValue can be used. If entering a calendar date, enter it in "yyyy-mm-dd" format.

Arguments 3 - 9 must be the following, even if your location's First Day of the Week is Monday:

{Resource.Availability - Sunday},{Resource.Availability - Monday},{Resource.Availability - Tuesday},{Resource.Availability - Wednesday},{Resource.Availability - Thursday},{Resource.Availability - Friday},{Resource.Availability - Saturday})

A resource is available 8 hours Monday - Thursday and 4 hours on Friday.

=ResourceAvailability("2021-09-12","2021-09-18",{Resource.Availability - Sunday},{Resource.Availability - Monday},{Resource.Availability - Tuesday},{Resource.Availability - Wednesday},{Resource.Availability - Thursday},{Resource.Availability - Friday},{Resource.Availability - Saturday}) returns 36 hours.

=ResourceAvailability("2021-09-01","2021-09-30",{Resource.Availability - Sunday},{Resource.Availability - Monday},{Resource.Availability - Tuesday},{Resource.Availability - Wednesday},{Resource.Availability - Thursday},{Resource.Availability - Friday},{Resource.Availability - Saturday}) returns 160 hours
STRIPHTMLTAGS Removes any HTML tags from the input string. =STRIPHTMLTAGS(InputString)

InputString: A string of html text, including tags.		 =STRIPHTMLTAGS("<h1> This is a heading. </h1>") returns "This is a heading."