Nulls VS 0.00 in reports

[Former Member]

Post Author: ROMZILBER

CA Forum: WebIntelligence Reporting

Hello

I am new to BO so may be this is a question that can be answered very simple.

We have outer joint on some of the tables. Let say we have policies and payments activities, not all policies have activity. When we ran the report we could not come up with totals, so we have added nvl on all amounts (we are using Oracle), but now report returns 0.00 even if there is no activity for the policy, how can we display null when we display the report.

Thank you

[Former Member]

Post Author: jsanzone

CA Forum: WebIntelligence Reporting

Romzilber,

Good show on discovering the use of NVL within your database (I'm assuming that you set this via when building the object in your universe). As you're discovering, certain BI tools handle "things" differently once the data is returned from the database server. It seems that Webfocus knew how to handle the null (as is the case with several other leading BI tools), however, Business Objects didn't seem to do the same, which you discovered through trials and testing. Trial and testing is a good thing, but now you've also learned a lesson of never assuming that the reporting tool is handling everything for you and also that you should be prepared to "fix" those things that it is not handling via alternative means. Good luck moving forward!

[Former Member]

Post Author: ROMZILBER

CA Forum: WebIntelligence Reporting

thank you for all your responses, we decided to use as is, because it is actually an DB issue, we have to use NVL option for calculations as field + field2. If you do not put NVL on all fields SQL returns Null if one of the fields is null.

Thank you

[Former Member]

Post Author: Ranjit

CA Forum: WebIntelligence Reporting

I would have done it this way:

1 While displaying values in detail section display your column as is. So it will display 0 where there are 0, if there is a null, it wont display anything.

2 Create a formula

if isnull(Column) then 0 else column

3 To display total of the column, create a running total on formula in step 2.

Regards

Ranjit

[Former Member]

Post Author: ROMZILBER

CA Forum: WebIntelligence Reporting

Hello

Thank you for suggestions, but the problem is there are valid nulls as well. Also I have just found out that even if record exist it may be a mix in amounts, some can be nulls and some can have value. We are converting from WebFocus and this creates a problem as reports do not match. Webfocus has option to dispaly nulls as nulls and also just ignore them in calculations. We were able to make some progress on a records that do not exist by using NVL2 option in oracle, but the second part I have no idea.

Thank you

[Former Member]

Post Author: InfluentialSoftware

CA Forum: WebIntelligence Reporting

Beyond the suggestion already offered, I don't know but maybe you can do it more elegantly within the object format option of the object in question within the Universe?

[Former Member]

Post Author: jsanzone

CA Forum: WebIntelligence Reporting

You can try to create a local variable in your report using the if() function:

=if([pymt]=0;"null";[pymt])