Occasionally during the execution of Apex code, an error condition will occur and the Apex runtime engine will generate an exception. It is important for developers to understand the exceptions that could occur when their code is executing and what causes them so that they can make their code more robust. In this article I describe some of the exceptions that can occur when working with report filters in the Analytics API via Apex. I also note the methods that are part of the API that can be used to gain information that can be helpful in avoiding the exceptions.
Types of Report Filter Exceptions
The documentation on Report Exceptions describes two of the Exceptions as having a relationship to filtering: Reports.InvalidFilterException and Reports.InvalidReportMetadataException.
The Reports.InvalidFilterException is described as a “Filter validation error” and the Reports.InvalidReportMetadataException is described as “Missing metadata for filters”. Both Exceptions can be caused by multiple field filters during the same report run. The method getFilterExceptions() on Reports.InvalidFilterException returns a List<String> of error messages if there are multiple error messages. The error messages include which filter caused their error. For example, if the first filter caused the error the text “For the filter 1” is included in the message. If it was the second filter in the list it would read “For the filter 2”, and so on. Likewise, the Reports.InvalidReportMetadataException class has a method getReportMetadataErrors() that returns a List<String> of errors.
There are different ways that these errors can be generated. The rest of this article goes into detail on some of the ways that filter related errors can occur.
Column does not exist or is not filterable
The column of the Reports.ReportFilter must be a valid API name of a column. If it isn’t then a Reports.InvalidFilterException will be generated. An error such as the following will be generated: For the filter 1: Specify a valid filterable column because INVALID_API_NAME is invalid, where INVALID_API_NAME is not a valid column API name. The API names appear in multiple classes in the API as values and as keys in maps. You can get a list of the API names of the columns from the Reports.ReportMetadata.getDetailColumns(). This same error is generated if the column name is a valid API name, but the column is not filterable. For example, in an activity report the Due Time (API name of DUE_TIME) is not filterable. The method Reports.ReportTypeColumn.getFilterable() that returns a Boolean can be used to determine if a column is filterable or not.
The operator of the Reports.ReportFilter must be a valid operator API name. If it isn’t a Reports.InvalidFilterException will be raised. An error message such as the following will be generated: For the filter 1: Specify a valid condition because <invalid-operator> is invalid. If the operator is a valid operator but not for the data type of the column a more specific error message may be generated. For example, if the “starts with” operator is used with a currency field the following error message will be generated: Operators ‘starts with’, ‘contains’ and ‘does not contain’ do not work with the selected column. The API provides a way to determine the valid filter operators for each data type with the ReportManager.getDatatypeFilterOperatorMap() method which returns a Map<String, List<ReportsFilterOperator>>of all of the data types to their available filter operators.
Invalid date format
The date string must be in a specific format . If it is not an error such as the following will be generated: For the filter 1: Filter the date in the correct format. Accepted formats are yyyy-MM-dd’T’HH:mm:ss’Z’ and yyyy-MM-dd. There are methods in the Date and Datetime classes that can be used to manipulate date values.
If a currency field is being used in a filter the value must be a valid currency. For example if the filter is on the AMOUNT field of an Opportunity report, the value cannot be a non number such as ‘abc’. If it is a Reports.InvalidReportMetadataException will be generated and an error such as the following will be generated: For the filter 1: Invalid currency.
Picklist value does not exist
If a picklist field is being used in a filter the value must be in the picklist. If it isn’t a Reports.InvalidReportMetadataException will be thrown and an error such as the following will be generated: For the filter 1: Picklist value does not exist. It is possible to determine the available values at runtime, using the Analytics API. The available values for a given picklist field can be retrieved from the Reports.ReportTypeColumn.getFilterValues().
If a number field such as a percent has a non number specified a Reports.InvalidReportMetadataException will be generated. An error such as the following will be generated: For the filter 1: Invalid number.
If a checkbox field filter has a value specified that is not true or false a Reports.InvalidReportMetadataException will be raised. An error such as the following will be generated: For the filter 1: Use “False” or “True”. Although the valid values are clearly “False” or “True” as the error message indicates, the API provides a programmatic way to get the valid values though the Reports.ReportTypeColumn.getFilterValues() method.
If an ID field filter has a value specified that is not a valid ID a Reports.InvalidFilterException will be raised. An error such as the following will be generated: For the filter 1: The filter value for ID !0 is incorrect. Specify an ID that is 15 or 18 characters long, such as 006D000000CrRLw or 005U0000000Rg2CIAS.
Debugging techniques described in this article can be used to obtain information about the various objects in the API and can be helpful in determining how to get certain values from the API to address handling exceptions. In addition to filter related exceptions, there are other exceptions that can occur. See the documentation on Report Exceptions for more information on all exceptions related to the Analytics API in Apex. Check out the Analytics API via Apex section in the Apex Developer’s Guide and the reference section on the Reports namespace for more on the API in general.