Converting null to zero, or zero to null values

Question

How do I convert null values in reports to zero? How do I convert zero values into null values?

Answer

In certain cases, your reports might use sql expressions where you add or multiply calculated values, and one value is null. With sql, multiplying or adding a non-null value to a null value will improperly result in an answer that is a null value. The solution to this problem is to insert sql statements that ensure that null values are always converted into zero values, thereby resulting in a non-null answer when that is the proper result.

1. Navigate to Admin>Reports>Edit the report you want to modify
2. Create a SQL Expression reporting column by clicking on the + button, scrolling to the bottom of the report column list and select <<Add SQL Expression>>.
3. When creating a SQL Expression you can wrap that statement inside an ISNULL or NULLIF statements.
4. Use ISNULL to convert null values to zeros. Use NULLIF to prevent divide by zero errors.
5. Here is example sql from an employee percent allocation report:
   
   ISNULL( 100* (SELECT SUM(te.workhrs) FROM t_timeentry as te    WHERE te.strt>=$P{startDate}    AND te.strt<$P{finishDate}    AND te.userid=t_timeentry.userid) / NULLIF( (SELECT SUM(te.hours) FROM t_timeentry as te    WHERE te.strt>=$P{startDate}    AND te.strt<$P{finishDate}    AND te.userid=t_timeentry.userid) ,0) ,0)
   
   
6. This is taken from a master knowledgebase report: "Employee Hours Percent Allocation." You can request more details on the report example by submitting a request.