SSAS converts NULL values into string 'NULL'

  • SSAS converts empty fields into string text 'NULL'. I found out because my report shows NULL as string value. Running the MDX query in SSMS, it shows NULL as well, while other fields remain empty.

    I tried all the ppossibilitieswith the NullProcessing option, but with no results.

    In report server I covered the result by using the following expresion,=IIF(Fields!Direction.Value="NULL","",Fields!Direction.Value) but there must be a better way, am I missing something here?

  • Hi,

    I've handled Nulls in SSRS reports coming out of SSAS with this sort of expression.

    =Iif(IsNothing(Fields!ANYFIELD.Value),"The Field Is Null",Fields!ANYFIELD.Value)

    Found here:

    http://www.mredkj.com/vbnet/RSNullCheck.html

    Hope that helps.

    Simon



    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • Thanks, Simon.

    The problem is that the field is not empty in SSAS, it is filled with a string text 'NULL', while it should be empty.

    It can be solved in Report server, but SSAS adds the null value for some strange reason. I was wondering if I can change this behaviour of SSAS, because removing it from Report server is'nt a nice solution in my opinion.

  • I think SSAS does the proper thing by wanting to have all fields defined. In your dimensional database, It is common to put a -1 key to represent a null in your fact tables foreign key fields. SSAS does not like the whole idea of absence of value. Can you provide more specifics to your situation?

    Thanks

    ----------------------------------------------------

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

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