Post reply

NULL gets displayed as 0 for measures

  • June 7, 2006 at 9:34 pm

    #117930

    Hi,

    i am using analysis services 2000.

    i want the measure values in the cube to display 0 and NULL from database(SQL server 2000) to be displayed as 0 and NULL respectively.

    here is a couple of things i tried and the results for it.

    the measures in the cube displays the NULL values from database as 0. so i tried a calculated measure with the formula

    1) Iif([measures].[myMeasure]=NULL,NULL,[measures].[myMeasures]).

    the problem with the above formula is that it treats even 0 as NULL i.e., it displays 0 as NULL.

    so i tried another formula which is like this

    2) Iif(measures].[myMeasure]=0,0,

    Iif([measures].[myMeasure]=NULL,NULL,[measures].[myMeasures]))

    there was a problem with this formula too... for 0/NULL the first condition gets evaluated to true resulting in 0 for 0/NULL.

    is there a way to sort it out this.

    Any help in this regard is highly appreciated.

    thanks and regards

    chethan

  • June 12, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • June 21, 2006 at 4:05 am

    #644973

    Hi...

    I have the same issue and this is what I've discovered

    Null Fact Value

    Gets Displayed as 0

    MDX : measure = null -> False

    MDX : IsEmpty(measure) -> True

    0 Fact Value

    Gets Displayed as 0

    MDX : measure = null -> False

    MDX : IsEmpty(measure) -> False

    No Fact Row for Intersect

    Gets Displayed as Null

    MDX : measure = null -> True

    MDX : IsEmpty(measure) -> True

    So basically evaluating against null will evaluate whether a fact row exists or not. To distinguish between a null value fact and a real value you use IsEmpty() function to test whether the cell contains null or not...

    tis a pain since I have to wrap all my measures with MDX IsEmpty() expressions.

    since surely Microsoft should realise that 0 and Null means two different things to a business!

  • July 25, 2006 at 4:57 am

    #651298

    Hi Shaun,

    Thanks..

    I am a newbie to AS.

    By MDX do you mean the cubes in AS 2000?

    I remember tring out IsEmpty function and it gave me the same result as IsNull function.

    I will check it out once again and let you know soon..

    regards

    chethan

  • July 28, 2006 at 5:42 am

    #652098

    Hi all,

    I have the same problem with Chethan, but I worked around it in my SSIS package's SQL command, that way I never have NULL values in my Fact tables.

    What's the most appropriate Data Flow Transformation that can replace NULL values?

    Thanks guys.

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

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