Post reply

Replacing NULL measure with a value or zero

  • March 8, 2012 at 10:41 am

    #150839

    Hi,

    SS2005 Standard edition

    Two measures have data on different dates. on some dates one measure has data but other dont and in that case the measure which do not have data shows as (null).

    If i browse individual measure with date then no problem, of-course! However i need both in a report against same date but in case of null i want to change that "(null)" to zero.

    -- Current Output

    ReportDate Col1Col2

    2007-12-01 00:00:00.0003691368

    2007-12-02 00:00:00.000(null)1824

    2007-12-03 00:00:00.000615(null)

    Instead of handling this in SSRS with IsNothing() in every report, Can i use any general option to change such cases to zero?

    -- Desired Output

    ReportDate Col1Col2

    2007-12-01 00:00:00.0003691368

    2007-12-02 00:00:00.00001824

    2007-12-03 00:00:00.0006150

    Any option while processing cube OR in SSRS?

    Any new option is SSRS 2008?

    Thanks.

  • March 8, 2012 at 10:50 am

    #1456700

    Well you could add an ISNULL to your query.

    ISNULL(col1, 0) as col1

    Or is that what you are trying to avoid? You have to either handle it in your query or in your ddl.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 8, 2012 at 4:50 pm

    #1456886

    I am trying to make SSRS report based on SSAS cube.

    Questions are:

    I want to Avoid IsNothing() because in my actual report then i need to use that too much?

    How to use IsNULL in MDX?

    Any other SSAS Cube processing option OR builtin SSRS report option to change such cross section NULLS to zero?

    Thanks.

  • March 8, 2012 at 5:28 pm

    #1456893

    You don't necessarily want to default the cube to replace nulls with 0s because then it'll set your averages and counts to be inaccurate. Unless, of course, you WANT to adjust those rollups, in which case I'd change the feeder/source data.

    However, what you're dealing with is a display issue. As aggravating as it is, yes, your best bet is the IsNothing() all over creation in the report. There's no way I'm familiar with to have a report default all NULLs, in all columns, to be a certain value. If there's a way to do it on a single column I'm unfamiliar, it'd basically end up being the same as IsNothing() everywhere just better hidden.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 9, 2012 at 3:12 am

    #1457037

    OK. However, i am really not dealing with a display issue.

    In my actual complex calculation, i want to add col1+col2 but problem in doing this is then i need to handle IsNothing() everywhere in every calculation and eventually in every report.

    So what i was after to handle this is on a more general level handle this and then i dont need to care about handling this in every report.

    So anything just clicked in your mind?

    Thanks.

  • March 9, 2012 at 11:43 am

    #1457332

    MidBar (3/9/2012)

    So anything just clicked in your mind?

    Ah, so it's the sub-procedures to the reports you want to adjust for pre-calculations. Gotcha. Offhand, no. Most of the time either I've adjusted the base data or done those rollup calculations at the report level with field + field or the like, where the IsNothing() (ISNULL() in T-SQL) is converted and field+field takes the displayed value.

    I'll have to leave this for folks who are more familiar with cube output options. I don't know of a way to get that to happen for you on output without an input change to the cube data.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply