Discussions
Insight (OBIEE) Tips: How to force blank values to display as 0
You may have noticed metrics in your Eloqua Insight reports showing blanks as well as 0s.
Here is an example email analysis:
The blanks actually represent a NULL - meaning there is no value for that metric. In the case of bouncebacks you will only see values for hard and soft bouncebacks if there was a bounceback (i.e email delivery is less than 100%). On the other hand, Total Bouncebacks does show 0s. This is because the metric is using an aggregated table. An aggregated table stores the pre-coumuted results of Total Hard and Total Soft bouncebacks. So in the case of an aggregated table, you will see 0 instead of a NULL value.
Get rid of the NULLs
In many cases, your report consumers might want to see 0s instead of NULL values. So, here is how you can customize Insight to show a 0.
Start by customizing the column formula:
- Open your report for editing.
- On the Criteria tab, click the Settings icon next the column you want to update and click Edit Formula.
Then choose one of these SQL functions to handle the NULL:
- IFNULL() function
- CASE statement
Option 1: IFNULL() function
Technical Definition: Tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression.
Syntax: IFNULL(expr, value)
Example:
IFNULL("Activities"."Total Hard Bouncebacks",0)
Option 2: CASE statement
This form of the CASE statement evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression. If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system will automatically add an ELSE NULL.
Syntax: CASE WHEN request_condition1 THEN expr1 ELSE expr2 END
Example:
CASE WHEN "Rates"."Hard Bounceback Rate" IS NULL THEN 0 ELSE "Rates"."Hard Bounceback Rate" END
Alternate: Use binning
Alternatively to building a CASE statement, you can use the Bins tab to bin the results. Here is an example:
Final thoughts
- If NULL values are going to cause confusion with your report consumers, consider using the custom formulas to replace NULLs with 0.
- Each column will need to be customized to force 0s, so consider doing this once in a template report that you’ll use to build your final reports.
Comments
-
How do you explain when a report shows blank for Total Send?
-
For example: You could have instances where you have a forward for example. One contact was sent the message from the campaign, they forwarded it, and the new recipient performed other activities on the email. Or even the same person with multiple contact records... if they are switching around devices where Eloqua cookies are, they may have activities attributed to them.
