46

I need to write an SSRS expression to check and replace NULL field value with another field value. Can this be done?

PiotrWolkowski
  • 8,408
  • 6
  • 48
  • 68
Nite Cat
  • 533
  • 2
  • 8
  • 12

2 Answers2

68
=iif(isNothing(Fields!FV1.Value), Fields!FV2.Value, Fields!FV1.Value)
Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
40

If you have to do it a bunch of times, you can also make a reusable function to avoid a lot of typing. Here's a solution modeled off of SQL's ISNULL function:

  1. Right click on the Report Document and go to Report Properties.

    Report Properties

  2. Navigate to the Code tab and add the following function:

    Public Function IsNull(input As Object, defaultValue As Object) As Object
  Return IIf(input Is Nothing, defaultValue, input)
End Function

    Report Properties > Code

    Note - Even though the custom code is expecting valid VB.NET code, you have to use the IIF Ternary operator.

  3. Then you can use it in an expression like this: 

    =Code.IsNull(Fields!MyField.Value,0)

    Field Expression

KyleMit
  • 30,350
  • 66
  • 462
  • 664
  • 2
    Although both answers are correct, I very much like the potential for reuse with this solution. Especially if you have a complex expression where you're aggregating a field. In that instance this is a much more readable and reusable solution. – Conor Gallagher Aug 13 '15 at 14:10
  • 2
    3 years after - but i found this to be very useful. – MISNole Jul 06 '17 at 03:40