• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

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

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
mshan
Asked:
mshan
  • 2
  • 2
1 Solution
 
chapmandewCommented:
try this:

IIF(isnull(value2) = true, 0, value1/value2)
0
 
mshanAuthor Commented:
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
 
chapmandewCommented:
How are you using it?  I just tried it and it worked fine for me...
0
 
jgvCommented:
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
 
mshanAuthor Commented:
It works fine.Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now