LiveReports 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
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:
- Navigate to > Reports > LiveReports > LiveReports Designer.
- 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.
- 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.
- 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.