Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS converts NULL values into string 'NULL' Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 12:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 25, 2013 2:44 AM
Points: 2, Visits: 93
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
Posted Tuesday, February 5, 2013 2:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:51 AM
Points: 702, Visits: 2,176
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




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1415665
Posted Tuesday, February 5, 2013 3:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 25, 2013 2:44 AM
Points: 2, Visits: 93
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
Posted Wednesday, April 24, 2013 11:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 354, Visits: 869
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 »

Add to briefcase

Permissions Expand / Collapse