Solved

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

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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