0

I've got a problem with this piece of code inside a parameter in SSRS2012.

=IIF(InStr(Parameters!P1.Value,"@")=0,
"missing @", 
Left(Parameters!P1.Value(InStr(Parameters!P1.Value,"@")-1)))

My aim is to check if the parameter P1 contains a "@" if it doesn't i will print "missing @" otherwise i want to remove from that parameter what's after the "@" ("@" inculded).

example:
P1 = 123456 --> print "missing @"
P1 = 123@56 --> print 123

The problem is that SSRS drops the error: "Argument 'length' must be greater or equal to zero" when i insert a value with no "@". (when i insert a value with an "@" it goes all well)

It seems like the IIF resolves the 2 expressions first (rather than one is true or false) and after that it gives you the correct one based on the evaluation of the first condition.

Looking for some help. Thanks!

Luca Rusin
  • 81
  • 1
  • 10
  • Possible duplicate of [Does the iif function compute both paths in SSRS or is it short-circuited?](http://stackoverflow.com/questions/1204179/does-the-iif-function-compute-both-paths-in-ssrs-or-is-it-short-circuited) – Flakes Jan 25 '17 at 10:10
  • not a duplicate, tried the switch function and it's the same – Luca Rusin Jan 25 '17 at 11:12
  • Could it be that the parameter only contains the '@' symbol and nothing else? – SS_DBA Jan 25 '17 at 14:33
  • not really, for example this parameter --> 123@56 generates the error too – Luca Rusin Jan 26 '17 at 11:24

1 Answers1

1

it is a weird bug, here is one solution 

=replace(replace(IIF(InStr(Fields!P1.Value,"@")<> 0,
left(Fields!P1.Value,InStr(Fields!P1.Value,"@")),"missing"),"@",""),"missing","missing @")
Kostya
  • 1,567
  • 1
  • 9
  • 15