Skip to Content
author's profile photo Former Member
Former Member

Nulls VS 0.00 in reports

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 05, 2007 at 02:19 PM

    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])

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 06, 2007 at 01:01 PM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 06, 2007 at 06:18 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 06, 2007 at 07:13 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2007 at 12:17 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2007 at 02:25 PM

    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!

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.