SSRS SPLIT function breaking IIF statment

Question

In my dataset i have two fields X and Y that contains strings, but sometimes Y is null. the strings is structured like this "(variable length code) code description" eks: (ab1234) some event

I want Y to be used most of the time, but when Y is null i need to use X and i have been trying to use this code for it.

=IIF(isNothing(Fields!Y.Value), 
    Split(Fields!X.Value,")").GetValue(1), 
    Split(Fields!Y.Value,")").GetValue(1)
)

This works fine when Y is not null, but in the cases it is the filed returns #ERROR I have tried a alot of stuff to circumvent this but it breaks as long as Split(Fields!Y.Value,")").GetValue(1) line exists. Can anyone tell my why this is, and a efficient way to solve this problem.

This returns as expected

=IIF(isNothing(Fields!Y.Value), 
    1, 
    0
)

This returns as expected

=IIF(isNothing(Fields!Y.Value), 
    Split(Fields!X.Value,")").GetValue(1), 
    0
)

This returns #ERROR for instead of 1

=IIF(isNothing(Fields!Y.Value), 
    1, 
    Split(Fields!Y.Value,")").GetValue(1)
)

Same as above

=IIF(NOT isNothing(Fields!Y.Value),  
    Split(Fields!Y.Value,")").GetValue(1),
        1
)

the MID string command works but due to the variable lenght code the result is messy

Answer 1

The problem with your expression is that both parts of the IIF are evaluated, even if they are not returned. So, when for example Y is NULL, SSRS still tried to evalute SPLIT() which will return on empty array and then GetValue(1) tries to return the second element, which does not exist.

The simplest way to fix this is to simply append a ")" to the end of the string when it's being evaluated. The result is never returned anyway so it does not matter.

So..

=IIF(
    IsNothing(Fields!Y.Value)
    , Split(Fields!X.Value & ")", ")").GetValue(1)
    , Split(Fields!Y.Value & ")", ")").GetValue(1)
)

will fix the issue.

When the SPLIT is being evaluated, given Y as "(ab1234) some event" is will split the string "(ab1234) some event)" instead but still return the 1st element " some event". When the NULL value gets evaluated it will attempt to SPLIT the string ")" instead of NULL and return an empty string, we never actu8ally return this value but the function can return a valid value which is all we need.

based on the following dataset I used in my example..

DECLARE @t TABLE(X varchar(100), Y varchar(100))
INSERT INTO @t VALUES 
('(ab1234) some event R1X', '(ab1234) some event R1Y'),
(NULL, '(xy9876) some other event R2Y'),
('(zz999) final event R3X', NULL)

SELECT * FROM @t

These are the results.