LiveReports formulas

You can use formulas within the cells of your LiveReports to customize your report content. Formulas can use constant values, data fields, the information from other cells, or a combination of any of these data sources. To open the page, use the path(s) in the Security and navigation section above.

Formula guidelines

LiveReports formulas follow a structure similar to Microsoft Excel formulas. All formulas must begin with an equals = sign.

EXAMPLE   The formula ="Company" would display the text Company in the cell.

You can use functions in your formulas. For more information about supported functions, refer to LiveReports functions for formulas. Functions are not case sensitive and you can use more than one function within a cell.

EXAMPLE  The formula =SUM(3, 2) would display the sum of 5 in the cell.

In any formula that contains the Hyperlink function, the HyperLink function MUST be the outermost function.

EXAMPLE  =HyperLink({Task Or Ticket.Url},{Task Or Ticket.Task or Ticket Number}&" / "&{Task Or Ticket.Task or Ticket Title})

You should use curly braces { } around variables that access data fields. Data fields should follow the naming structure {Data Category.Data Field}.

EXAMPLE  The formula ={Company.CompanyName} & "-Delinquent" would display Company ABC-Delinquent in the cell, where Company ABC represents the Company's name.

You should use square brackets [ ] around variables that access other cells within the report. Cell letters should be capitalized.

EXAMPLE   The formula =[A1]+[A2] would display the sum of cells A1 and A2.

You should use double quotes " " around static text that you want to appear within the cell. In addition, you can concatenate text and variables by using the ampersand & symbol.

EXAMPLE   The formula ="Name: " & {Resource.First Name} & " " & {Resource.Last Name} would display the text Name: and then the first and last name of the resource, all within the same cell. Note the " " added between two ampersands (&) to add a space between the first and last names.

NOTE  When concatenating text with a numeric, date, or date/time variable, add the GlobalNumericFormat, GlobalDateFormat, or GlobalDateTimeFormat function to ensure that the variable displays in your preferred format.

EXAMPLE  = "Contract Start Date: " & GlobalDateFormat({Contract.Start Date})

Autotask has three special variables that will insert useful data in your reports: @pageNumber@, @reportName@, and @reportFullName@.

IMPORTANT  There are a number of entities in Autotask that you can select a different term for. This selection is made on the > Admin > Features & Settings > Application-wide (Shared) Features > Local Terms & Symbols page. In LiveReports and in the Data Dictionary, these terms are surrounded by "@" symbols, to indicate that in your Autotask instance, you may well have chosen a different term. You could consider them "label formulas". Example: @Account@ & "Name" will appear as Organization Name on the report, if you selected that term on the Local Terms & Symbols list.

EXAMPLE  The formula ="pg. " & @pageNumber@ would display the text pg. 2 on the second page of the report.

NOTE  You can use either @pageNumber@ or PageNumber() to add a page number on your report. To ensure that the page number is formatted correctly, either concatenate it with text as in the example above or change the cell formatting to Numeric and set the number of decimal places to 0.

NOTE  Because of limitations in the RTF output, pagination will only work if the @pageNumber@ variable is the only content in its cell.

Using formulas

To use a formula in your report:

1. Navigate to > Reports > LiveReports > LiveReports Designer.
2. Create a new report or edit an existing report. For more information on creating and/or editing reports, refer to LiveReports and Formatting and editing LiveReports.
3. In the report editing view, click a cell and enter your formula. If you are using a formula in a report sort, click Add Formula to open the Formula Editor.
4. Be sure to click Save when you have finished.

NOTE  If the report will be exported to HTML, PDF, Word, or CSV and the intended formula output includes a time value, it may be necessary to modify the date/time formatting on the field to ensure the time value appears. To do so, right-click on the field and select Format Cells. Then select Date. Choose the appropriate date/time format. If it is not listed, you can type it directly into the Date/Time Format field. Click Ok to save your changes.

Using functions in formulas

Refer to LiveReports functions for formulas.