Oracle Fusion OTBI: Column Filter Does Not Filter Out Null Values (Doc ID 2059966.1)

Last updated on JANUARY 11, 2016

Applies to:

Oracle Fusion Sales Cloud Service - Version 11.1.10.0.0 and later
Information in this document applies to any platform.

Goal

On : 11.1.10.0.0 version, Technology Management - Reports/Analytics


We have created a report that is designed to provide a list of all accounts that do not have a specific activity created within a defined date (which is prompted for the user to enter). Once a user enters a date into the prompt, the report should show all customers that do not have the specific activity (based on filter criteria) within that timeframe. The report is correctly filtering based on the dates entered. However, we also need to display the last activity date that this event occurred, if there was one.

Our report is built out with all columns from the Customer table, except for one column which has an in column filter to avoid the inner join issue we would face by doing direct filters against activity:

FILTER((MAX("Activity"."Actual Start Date" by "Customer"."Customer Row ID")) USING "Activity"."Activity Function Name" = 'Appointment' AND "Activity"."Status Code" = 'COMPLETE' AND ("Activity"."Activity Type Code" IN ('CALL', 'CBM_PHONE_CALL', 'EMAIL_CONTACT', 'HYBRID', 'MERCH') OR "Activity Extension"."Extension Attribute LOV 010" = 'Prof'))

The filter works correctly except for the "Activity"."Status Code" = 'COMPLETE'. In this case, it passes true if the status is COMPLETE or NULL. We have tried to filter out NULL in the column filter but it does not make any difference. So what ends up happening is there are future dated (and complete) activities that are of the same type, etc, which when max date is applied display the not completed activity record start date instead of the expected Completed record start date.

 
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms