Solved

What is the equivalent function on NULLIF in Vb script function to use in SSRS reports?

Posted on 2008-06-24
5
751 Views
Last Modified: 2011-10-03
what is the equivalent function of NULLIF(t-sql) in Vb script ?.I am trying to display 0 if  value2 is zero in the expression value1/value2.For this,I need a function to use in the EXpression in SSRS.can anyone tell me what function can i use in Expression in SSRS to display 0 in report if the denominator is 0?
0
Comment
Question by:mshan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21855930
try this:

IIF(isnull(value2) = true, 0, value1/value2)
0
 

Author Comment

by:mshan
ID: 21856175
I tried IIF(isnull(value2) = true, 0, value1/value2),its not working.Iam getting an ERROR "The value Expression for the textbox contains an error.[bc30451] . Name 'isnull is not declared' "
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21856236
How are you using it?  I just tried it and it worked fine for me...
0
 
LVL 12

Accepted Solution

by:
jgv earned 500 total points
ID: 21856276
The equivalent is IsNothing(). SSRS does not recognize "isnull". Checking if the denominator is null or 0 in an IIF statement also won't work because both the true and false portions are evaluated by the compiler (you would have to try it to understand). I use a custom function to do the evaluation and division. It's more flexible than trying to handle all the checks and calculations within the expression.  

From the menu: Report >> Report Properties >> Code tab
Paste this into the custom code window:

Function CalcPercentage(ByVal Numerator, ByVal Denominator) As Double

     ' Test if either input parameter is null or empty
    If String.IsNullOrEmpty(Numerator) OrElse String.IsNullOrEmpty(Denominator) Then
        Return 0
    End If

     ' Test if either input parameter is non-numeric
    If Not Double.TryParse(Numerator, 0) OrElse Double.TryParse(Denominator, 0) Then
        Return 0
    End If

    If Denominator = 0 Then
        Return 0
    Else
        Return Numerator / Denominator
    End If
End Function

Use this to call the function from an expression in the report (change the value1 and value2 to the proper field names)::
=Code.CalcPercentage(Fields!value1.Value, Fields!value2.Value)
0
 

Author Closing Comment

by:mshan
ID: 31470142
It works fine.Thanks
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question