Reporting 2.0 - Calculated Fields: Additional Validations

With calculated fields, you can generate aggregated reports based on totals of data. Report creators can generate aggregated reports to create simple, custom calculations on their reporting data without having to export and manipulate that data in a different tool.

Prior to this enhancement, Reporting 2.0 sometimes generated validation errors for reports containing calculated fields when users saved or downloaded the reports. As the reason for these errors was not known, it resulted in confusion.

With this enhancement, additional validations are performed upfront in the Calculated Field Editor to ensure correctness and successful generation of reports with calculated fields. Calculated field validations have existed previously. This feature enhancement expands the types and scope of validations performed to make the validation functionality more robust and to reduce errors when running reports.

  • If column names are put between quotes, then the calculated field considers those as plain text or string, causing errors. Additional validation is required to check string to date conversion. For example, the validation of this formula DATEVALUE("[Training].[Training Start Date]") will fail in the future, as the field is incorrectly converted to text due to the addition of quotes.
  • Some DATEVALUE fields are incorrectly used in formulas, but the previously existing validations passes the formula as a success. The following is an example of this validation:
    • Previously, DATEVALUE(“los angeles”) would generate as a "valid" formula due to the DATEVALUE function only validating that a single argument was provided, and the calculated field would be created successfully. However, when this calculated field is added to a report and the report attempts to render the data from the server, the report would fail to generate because the text “los angeles” cannot be converted to a date value or Datetime function.
    • With additional validations, the Calculated Field Editor validates that a valid value is entered, such as DATEVALUE("03/08/2023") or DATEVALUE(TEXT(Transcript Due Date). The report creator is alerted of the identified error without successfully generating the calculated field until the formula is rectified.

How Does this Enhancement Benefit My Organization?

The enhancement comes with the following benefits:

  • Reduces the number of errors received for reports containing calculated fields.
  • Provides additional validations on correctness of the calculated field formula.

Example Scenario

Previously, DATEVALUE(“los angeles”) would generate as a "valid" formula due to the DATEVALUE function expecting a single argument, and the calculated field would be created successfully. However, when this calculated field is added to the report and the report attempts to render the data from the server, the report would fail to generate because the text “los angeles” cannot be converted to Datetime.

With additional validations, the report creator is alerted of the identified error without successfully generating the calculated field until the formula is rectified.

Implementation

This enhancement is automatically enabled for all organizations.

Permissions

For more information about Reporting 2.0 permissions, see the following:

  • Permissions in Reporting 2.0 - This provides detailed information about Reporting 2.0 permissions.
  • Permissions List - This provides the list of permissions and their relationships so that you know which permissions users need to have to create and view reports.