How to Resolve “Cannot Function Ship Evaluate” Errors in Analytics

Introduction

Oracle Analytics (including Oracle BI and DV) offers advanced capabilities for customizing reports using powerful SQL-based functions like EVALUATE and EVALUATE_AGGR. These functions allow users to inject raw SQL into their analysis or build complex aggregations not natively supported in the semantic layer. However, users often encounter a frustrating error:

“Cannot function ship the following expression: Evaluate”

This article explains why this error occurs and how to resolve it using best practices and alternative approaches.

What Causes the “Cannot Function Ship Evaluate” Error?

This error typically indicates that Oracle Analytics is unable to push (or “function ship”) the SQL expression defined within the EVALUATE or EVALUATE_AGGR function to the database layer. This limitation is often triggered by:

• The context in which the function is used (e.g., filters or prompts).
• Incompatibility between the SQL inside the function and the logical query model.
• Use of these functions in Data Visualization (DV) datasets, where native support may be limited.

Understanding EVALUATE and EVALUATE_AGGR

• **EVALUATE** is used to inject custom SQL expressions.
• **EVALUATE_AGGR** is used for SQL-based aggregation, such as comma-separated lists using LISTAGG.

These functions require the proper Evaluate Support Level setting in the system configuration:

• Level 0: No support
• Level 1: Only administrators can use it
• Level 2: All users can use EVALUATE and EVALUATE_AGGR

If this setting is correctly configured (e.g., set to 2) and you’re still seeing the error, it means the issue lies in where and how the function is used, not in permission levels.

Best Practices for Using EVALUATE_AGGR Without Errors

To avoid the function-shipping error:

1. Use EVALUATE_AGGR in Logical Columns
   Define the custom aggregation logic in the semantic model (RPD) like so:

   EVALUATE_AGGR('LISTAGG(%1, '','') WITHIN GROUP (ORDER BY %1)', "YourTable"."YourColumn")
   

2. Avoid Using in Filters or Prompts
   These contexts often require full logical query pushdown and do not support custom SQL evaluation.
3. Test in Classic Analyses (not DV)
   Data Visualization (DV) tools may have stricter constraints, and not all custom SQL functions are supported in DV datasets.

Workaround: Use a Database View

If you need to use EVALUATE_AGGR in a DV dataset and keep getting the error:

• Create a database view or materialized view that performs the required aggregation.
• Import the view as a dataset into Oracle Analytics or DV.
• Use it in your analyses without triggering the function-shipping restriction.

Summary

The “Cannot function ship Evaluate” error in Oracle Analytics is a common hurdle when working with advanced SQL functions like EVALUATE_AGGR. By understanding the limitations of the semantic model and query engine, and applying workarounds such as using logical columns or database views, you can continue building powerful custom reports and dashboards.