CODEWITHSUNDEEP
reporting-servicesssrs-2008
Crazy Cucumber
Crazy Cucumber

Reputation: 479

SSRS Expression - #Error in an obviously correct expression

I have an SSRS expression with this in it:

=IIF(
    Fields!CUT.Value.ToString().Length > 1
    ,IIF(
        Fields!CUT.Value.ToString().Contains(","), 
        Fields!CUT.Value.ToString().Substring(0, Fields!CUT.Value.ToString().IndexOf(",")),
        Fields!CUT.Value.ToString()
    )
    ,""
)

Translation:
If the field contains a ,, Get me the substring of the field from the first position to where the , is. If the field does not contain ,, just get me the entire field.

Probelm:
I get a #Error in all the pages where there is NO COMMA. If there is a COMMA, it works as expected. One of the fields, the value is 4. In a different scenario, the value is 17/64" Corrugated. I get an error for both of those.

What I've tried:
I've tried a few different approaches:

  1. Used LIKE instead of Contains 

    =IIF(
    IsNothing(Fields!CUT.Value)
    ,""
    ,IIF(
        Fields!CUT.Value LIKE "*,*", 
        Mid(Fields!CUT.Value, InStr(Fields!CUT.Value, ",") + 1,  (LEN(Fields!CUT.Value) - InStr(Fields!CUT.Value, ",") - 1))
        Fields!CUT.Value
    )
)

  2. I added a new field to my dataset that replaces , with XXXX. I thought, maybe SSRS is not able to understand that I am talking about a , as a part of the string. And then changed the query to see if there is an XXXX in it. Still got the same #Error

  3. Delete the .data file, cleaned the project, and rebuilt the project. Because, this is so obviously not incorrect. So I thought maybe it is not looking at the latest version of the report.

Nothing seems to do it! I am really close to doing something bad! This thing is driving me insane.

Questions:

  1. Anyone see anything wrong with either of my expressions?
  2. Is there a better way to do to what I am trying to accomplish? If there is a ,, give me everything before the ,. If not, give me everything.

Edit 1:
Worth noting that, in a different field, I am doing something very similar to this:

IIF(
    IsNothing(Fields!CUT.Value)
    ,""
    ,IIF(
        Fields!CUT.Value LIKE "*,*"
        ," | Length: " & Mid(Fields!CUT.Value, InStr(Fields!CUT.Value, ",") + 1,  (LEN(Fields!CUT.Value) - InStr(Fields!CUT.Value, ",") - 1))
        ,""
    )
)

What this is doing is, if there is a , in that very same field, get me everything AFTER the , and append it to | Length:. If there is no ,, just show an empty space "". This works perfectly fine. In situations where there is a , and where there is NO ,. So I've concluded that Microsoft is using SSRS to make me go crazy.

Upvotes: 1

Views: 2080

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

You could do this using SWITCH and test for no comma first but there is a much easier way assuming you only ever want to get the first part of the string up to the point of the comma.

This just uses the split function and takes the first element.

=(Split(Fields!CUT.Value, ",")).GetValue(0)

Nice and simple :)

Upvotes: 1

Related Questions