In Answers, I have a report that uses a UNION.

For the first part of the union I select sum(z_weight) (z_weight is a numeric field that stretches out to about 8 decimal places).

In the 2nd part of the union I select NULL for the z_weight field (by just putting the word NULL in my formula for this column).

The issue I am having is that when OBIEE sums up z_weight, it seems to cut off the last few decimal places before it does the summing -- which causes my totals to be off. We have found that this is caused because I am setting the z_weight to NULL in the 2nd part of my union. For some reason, when OBIEE generates the query to send to the database, it casts the NULL z_weight column as a REAL data type. We found that if we put 'CAST(NULL as NUMERIC)' into the formula (instead of just NULL) then it casts the null field as numeric and our totals are calculated correctly.

This would not be a big issue if this was the only time we had to do this -- but we have many business users that create many of these types of reports (that use NULL fields in unions).

My qiestion is...

Is there a way (maybe in one of the config files or something) to tell OBIEE to always default the NULL fields to NUMERIC (instead of REAL)?

Database version: SQL Server 2008 R2

OBIEE version: 11.1.1.9.0