CODEWITHSUNDEEP
reporting-services
Sean
Sean

Reputation: 105

How to get around '#Error' when a function in MS Report Builder is evaluating a null value?

I hope my title is clear enough. I'm working in MS Report Builder, using a function that applies a regular expression to a queried value in order to get back a certain substring. The regex works fine, so I'll demonstrate a simpler version here to make this less wordy. Here's the gist of my equation:

=IIF(Len(Fields!CourtLocation.Value) < 1, "none",System.Text.RegularExpressions.Regex.Match(Fields!CourtLocation.Value, "(?:[A-Z]{2,4})").Value))

The main purpose is to get that substring, but I added the IIF so that on those occasions when the CourtLocation.Value is empty (I tried Is Nothing in my expression as well), the function returns "none" rather than "#Error"

I've been looking around for a solution, but nothing has worked; it seems like most other people who talk about this are using a mathematical equation rather than trying to get a string. Can anyone help me get rid of that stupid "#Error" value?

Upvotes: 0

Views: 506

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21683

You could try this (untested)

=System.Text.RegularExpressions.Regex.Match(
    IIF(
        Len(Fields!CourtLocation.Value) < 1,
        "none",
         Fields!CourtLocation.Value
        )
       , "(?:[A-Z]{2,4})"
      ).Value

This way the IIF is performed on the string that you want to pass to the regex function, so it always gets a valid value to process

Upvotes: 1

OwlsSleeping
OwlsSleeping

Reputation: 1570

Iif evaluates both sides, so you can nest two Iif statements to avoid the error.

Did you already read this one? https://sqldusty.com/2011/08/01/ssrs-expression-iif-statement-divide-by-zero-error/

I'll copy the text into the answer if that solves it for you.

Upvotes: 0

Related Questions