1

I want to generate my string in the following format:

Vancouer should be displayed as -> ( V) anc-ouer
Roberts Cut-Off Road -> Roberts Cu(t-O) ff-Road ... etc So for the last 4 charaters I am using:

Right(Fields!NAME.Value, 4)

for the middle portion I am using IIF : 

IIF(Len(Fields!NAME.Value) < 10, 
Left(Fields!NAME.Value,. 
(Len(Fields!NAME.Value)-7)), 
 Mid(Fields!NAME.Value,Len(Fields!NAME.Value)-9,3))

But the above IIF condition gives me #Error whenever the length is less than 10. What could be the possible solution for it? Or its better if I could know the expression to generate the complete format in easiest way possible.

aduguid
  • 3,099
  • 6
  • 18
  • 37
Shailesh Prajapati
  • 458
  • 2
  • 7
  • 20
  • Can you post a example in a table with a before and a after result and pls tell us what you want to achive (like a globaly rule?). You want to display the string `Vancouer` as `(V) anc-our` and `Roberts Cut-Off Road` as `Roberts Cut(t-O) ff-Road`?? And pls the full code you use. – Strawberryshrub Oct 10 '18 at 09:31
  • Use a SWITCH expression instead of IIF and put the test for <10 characters as the first expression to test. – Alan Schofield Oct 10 '18 at 12:06

2 Answers2

0

Even if you have the condition to check for 10 characters SSRS still evaluates the false part which is why you are getting the error, this is normally encountered when attempting to avoid divide by zero errors. more info here

divide by zero/null workaround

another article

RegBes
  • 554
  • 3
  • 11
0

To compensate for the issue that RegBes mentioned, use an additional IIF to workaround the error for the false part of the initial IIF.

IIF(Len(Fields!NAME.Value) < 10, 
        Left(Fields!NAME.Value, Len(Fields!NAME.Value) - 7), 
        Mid(Fields!NAME.Value, Len(Fields!NAME.Value) - IIF(Len(Fields!NAME.Value) < 10, 0, 9), 3) 
    )

This will subtract 0 if the LEN is less than 10. The first IIF will ensure that this code doesn't display a value but worksaround the issue of the IIF evaluating the TRUE and FALSE parts of the IIF.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39