Solved

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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