Adding Calculated Measure with Contains Function to Ad Hoc Crosstab Failes

Issue Description

Customer created Ad Hoc View Crosstab based on Oracle datasource.

Then, they created a Calculated Field using expression like Contains("field_name",'string_value') and made it available in Measures panel ('Use as Measure' context menu).

If they try to add that measure into Crosstab Rows or Columns, it failes with error:

"An error occured while performing the previous request"[/code]

Resolution

For any measure, added to Ad Hoc Crosstab, the engine tries to perform Summary Calculation.

By default, for that custom created calculated measure, CountAll Summary Calculation is used,

it can be checked in 'Edit Calculated Measure' window, 'Summary Calculation' tab.

As a result, generated SQL is incorrect from Oracle database point of view:

select * from 
( select count( regexp_like("product1"."BRAND_NAME",'Best', 'i')) as "CountAll_Contains_product1_BRA",
.....
 ) where ROWNUM <= 1000

and failes with error

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "REGEXP_LIKE": invalid identifier[/code]

as REGEXP_LIKE is a conditional operator which returns TRUE or FALSE and can't be used in Count() aggregation.

A possible solution here is to manually specify custom Summary Calculation expression, for example:

CountAll(IF(Contains("Brand", 'Best'), 1, 0))

This expression returns count of records where Brand field contains Best.

As a result, a correct SQL is generated:

select * from 
    ( select 
        count(case when ( regexp_like("product1"."BRAND_NAME",'Best', 'i')) 
                   then (1) else (0) end) as "CountAll_IF_Contains_product1_",
        Sum("sales_fact_1998_1"."STORE_SALES") as "Sum_sales_fact_1998_1_STORE_SA",
        "product1"."BRAND_NAME" as "product1_BRAND_NAME"
    from "FOODMART"."SALES_FACT_1998" "sales_fact_1998_1"
    inner join "FOODMART"."PRODUCT" "product1" 
            on (("product1"."PRODUCT_ID" = "sales_fact_1998_1"."PRODUCT_ID"))
    group by "product1"."BRAND_NAME"
    order by "product1_BRAND_NAME"
     ) where ROWNUM <= 1000

Solution tested with TIBCO JasperReports^® Server v.6.4.0, 6.4.2

Ref. Case 01567069