Prolem with IIF statement in SSRS

Question

The Setup

I have an expression that converts all caps values to proper case. Some of the values have an extra bit preceded by a hyphen, I want everything before that. Sadly a LEFT(INStr expression errors on instances without a hyphen in.

=IIF(
      INstr(Fields!Introducer_Title.Value, "-") = 0, 
      StrConv(Fields!Introducer_Title.Value,vbStrConv.ProperCase), 
      "nope"            
      )

The above works just fine: if there is no hyphen then the string is converted to proper case, otherwise it just says nope.

The Problem

=IIF(
      INstr(Fields!Introducer_Title.Value, "-") = 0, 
      StrConv(Fields!Introducer_Title.Value,vbStrConv.ProperCase), 
      Left(StrConv(Fields!Introducer_Title.Value, VbStrConv.ProperCase), INstr(StrConv(Fields!Introducer_Title.Value, VbStrConv.ProperCase), "-")-1)        
      )

The above works in so far as that if there is a hyphen in the value it returns the text before it in proper case, but now the values without hyphens error. The logic hasn’t changed.

It's as if instead of going IIF(A=TRUE,This,That) is somehow converted to always do That when I replace the Otherwise "nope" with a nested expression.

The error is just #Error, no other information.

Am I missing something obvious? I have a feeling this is some quirk of SSRS.

Updates
This is getting stranger the more I look into it:

Wrapping the function in an ISERROR is creating an error on the bad rows, rather than returning true.
Using an InStrRev function is returning the same value as an InStr function even though the position of the hyphen in the first row is not in the middle (I checked the values in Excel)

Anup Agrawal · Accepted Answer · 2014-06-10T17:11:37.990

IF(A=TRUE, This, That). SSRS will always evalute this and that irrespective of value of A.

In other terms IIF conditions in SSRS are not short circuited.

Try something like this.

Method 1:

=Left(StrConv(Fields!Introducer_Title.Value, VbStrConv.ProperCase), 
       IIF(
            INstr(Fields!Introducer_Title.Value, "-") = 0, 
            LEN(Fields!Introducer_Title.Value), 
            INstr(StrConv(Fields!Introducer_Title.Value, VbStrConv.ProperCase), "-")-1
          )      
        )

Method 2:

=IIF(
      INstr(Fields!Introducer_Title.Value, "-") = 0, 
      StrConv(Fields!Introducer_Title.Value,vbStrConv.ProperCase), 
      Left(StrConv(Fields!Introducer_Title.Value, VbStrConv.ProperCase), 
       IIF(
         INstr(Fields!Introducer_Title.Value, "-") = 0, 
         0, 
         INstr(StrConv(Fields!Introducer_Title.Value, VbStrConv.ProperCase), "-")-1
          )        
       )
    )

Note: The above code is not tested. Check for brackets and length of strings to make sure you get the correct result.

Thanks for the response Anup. The code provided doesn't work, but as you say, it wasn't tested. I'm currently playing about with it using the understanding you've provided but I'm not having much luck. One thing I don't get is why the same thing happens if I use a `SWICTH` function instead of an `IIF` — CLockeWork, Jun 11 '14 at 08:23
Interesting... it also errors if I wrap it in an `ISERROR`. So the first statement in my question works, the second function works in general, but put them together and it errors on the ones without hyphens, *and* if you wrap the whole thing in an `ISERROR` it *still* errors on those ones! — CLockeWork, Jun 11 '14 at 08:26

score 0 · Answer 2 · answered Jun 11 '14 at 09:57

The issue is the -1 at the end of the left statement: in instances without a hyphen -1 would be -1 on 0, causing an error.

To fix this I have used this expression,

=StrConv(
    Trim(
      Replace(
            IIF(
               INstr(Fields!Introducer_Title.Value, "-") <> 0, 
               Left(Fields!Introducer_Title.Value, INstr(Fields!Introducer_Title.Value, "-")),
               Fields!Introducer_Title.Value
               ),
            "-",
            nothing
            )
        ),
      VbStrConv.ProperCase
      )

Note that while this solves my problem, at the heart of the problem is the fact that SSRS is trying to execute all functions before checking which one to return, and so erroring when it hits one it cannot execute. This is why I have marked the other answer as correct, because that was the true cause of the problem.

Prolem with IIF statement in SSRS

2 Answers2