semaphore_error

Understanding Analytics API Filter Exceptions

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.

Invalid Operator

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.

Invalid currency

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().

Invalid number

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.

Invalid Boolean

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.

Invalid ID

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.  

Additional Information

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.

 

8 thoughts on “Understanding Analytics API Filter Exceptions

  1. Hi Peter – Fantastic list, thanks.

    The behaviour i’m facing when I put in an analytics:reportChart is weird – when i put the following into a VF page onto the opp page layout – the VF renders once without the chart and then redirects into a ‘URL no longer exists’…

    I tried a variety of variations, then moved just this bit to a separate VF page with its own custom controller – the page simply hangs refreshing every now and then. the report by itself works fine with the chart. I am not sure if I am doing something wrong. Any help will be appreciated.

    -R

  2. Hi there! I just wanted to ask if you ever have
    any problems with hackers? My last blog (wordpress) was
    hacked and I ended up losing several weeks of hard work due to no data backup.
    Do you have any solutions to protect against hackers?

  3. Hi Peter, good stuff here!

    Just one question, do you know if it’s possible to get all the available fields to use for filtering in a report, like you can do when you add a filter trough the stantard report? you get a picklist within all fields available.

    Thanks,
    Pablo.

    1. Hi Pablo. Thanks. :)

      There are ways to get programmatically get all filterable fields. Check out the Apex Developer’s Guide page “Filter Reports”.

      • The ReportTypeColumn.getFilterable method tells you whether a field can be filtered.
      • The ReportTypeColumn.filterValues method returns all filter values for a field.
      • The ReportManager.dataTypeFilterOperatorMap method lists the field data types that you can use to filter the report.
      • The ReportMetadata.getReportFilters method lists all filters that exist in the report.

      You can get the ReportTypeColumns from the ReportTypeColumnCategory.getColumns() and can get the ReportTypeColumnCategories from the ReportTypeMetadata.getCategories().

  4. Hi Peter,
    great insights. Bookmarked!
    Have you ever experienced, that errors occur non repetitively?
    At the moment I run reports through a VF page to display part of the result for a later selection. *checkboxes*.
    Before the report is run, I add an owner filter using Apex/ReportMetaData.
    Now, I can run it for a couple of times, no issue. But suddenly I get the wrong operator error message.

    Did you ever experience sth like that

    regards,
    stan

Leave a Reply

Your email address will not be published. Required fields are marked *