SSAS converts NULL values into string 'NULL'

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » SSAS converts NULL values into string 'NULL'

SSAS converts NULL values into string 'NULL'

Rate Topic

Display Mode

Topic Options

Author

Message

SQLnlPro

Posted Tuesday, February 05, 2013 12:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:46 AM
Points: 2, Visits: 90

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?

Post #1415619

s_osborne2

Posted Tuesday, February 05, 2013 2:42 AM

SSChasing Mays

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 612, Visits: 1,868

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

Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Post #1415665

SQLnlPro

Posted Tuesday, February 05, 2013 3:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:46 AM
Points: 2, Visits: 90

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.

Post #1415672

mmartin1

Posted Wednesday, April 24, 2013 11:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192, Visits: 640

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

Post #1446152

« Prev Topic | Next Topic »

Permissions