SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 ROUND function to show blank/nothing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pvong
Yak Posting Veteran

52 Posts

Posted - 11/27/2012 :  16:34:17  Show Profile  Reply with Quote
I have an expression that I just want it to show blank/nothing if it's negative from a ROUND function. Is this possible?

ROUND(IIF(a<b,"",4.2))

In my Test, if a > b, I get 4 rounded and that's great but if a<b, it shows an error. I understand it's because you can not round blank/nothing but is there a way around this? I can put a value like 0 but I really want it to show nothing.

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

LarryC74
Yak Posting Veteran

94 Posts

Posted - 11/28/2012 :  13:11:31  Show Profile  Reply with Quote
Why not use:

Round(IIF(a>b,4.2,""))

Or

Round(IIF(a>b,4.2,Nothing))

Or

ROUND(IIF(a<b,Nothing,4.2))

Everyday life brings me back to reality
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/28/2012 :  13:38:44  Show Profile  Reply with Quote
Use the expression with ROUND outside IIF and use format to show blanks for nulls.

ROUND(IIF(a<b,Nothing,4.2))
Format string to whatever you want, for example like this:

###;,-###""


Alternatively, use the Round function inside

=IIF(a<b,Nothing,ROUND(4.2))

Edited by - sunitabeck on 11/28/2012 13:39:29
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000