Solved

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

Posted on 2008-06-24
5
666 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now