This code give an error for record which doesn't have value in the field but give a good result for those which have value in the field

Question

I want to retrieve data from Microsoft Dynamics CRM 365 for SSRS. So I made a link between visual studio and the CRM to prepare a RDL file to import it in the CRM. The RDL file will permit to generate the report directly on the CRM.

So, I work on the rdl file and I retrieve the value from a certain field and then I passed the value in many function and it give me an error when the field is empty I don't know why. Here is my code:

= IIf(
    IsNothing(Fields!aric_getpartylist.Value) or Fields!aric_getpartylist.Value = "" or Fields!aric_getpartylist.Value = "@" or Fields!aric_getpartylist.Value = " " or Fields!aric_getpartylist.Value = "@@",
    nothing,
    IIf(
        Split(Fields!aric_getpartylist.Value,"@").GetValue(1) = "",
        "Reçu par : " & Join(Split(Split(Fields!aric_getpartylist.Value,"@").GetValue(0),"|")," et "),
        "Reçu par : " & Join(Split(Split(Fields!aric_getpartylist.Value,"@").GetValue(0),"|")," et ") & " accompagné de " & Join(Split(Split(Fields!aric_getpartylist.Value,"@").GetValue(1),"|")," et ")
        )
    )

On field which have an empty string like this "" it give #ERROR and the fiels with a non-empty string it give the good result !

EDIT: I try to cut the big function in multiple function in calculate field, so I succeed to isolate the problem: I thinkg this language precalculate all path of a condition IIF. Because if I do this:

=IIf(
    IsNothing(Fields!something.Value),
    nothing,
    Split(Fields!something.Value,"@").GetValue(1)
)

It will throw an error if Fields!something.Value is nothing and it will throw the correct value if it's not.

Because in fact in this language if I do this: Split(nothing,"@").GetValue(1) it throw an error, so with this test it probably proove that this language probably precalculate the path which permit this error and then throw the error anyway even if the IIF doesn't enter in the path with Split(nothing,"@").GetValue(1)

EDIT2: I forgot to say that Split(nothing,"@").GetValue(0) this code doesn't crash with nothing value. BUT this code do: Split(nothing,"@").GetValue(1)

Answer 1

There is 2 solutions to do it, we can do it with custom code or in PURE SSRS Language.

FIRST SOLUTION(custom code)

Inspired by this question here I find a way to do it. We will use Visual Basic code to do it.

1. click on Conception(if you already did it go to the next step)
2. Go to the toolbar on the TOP
3. Go to Report ► Report properties ► Code(on the left toolbar)

Then paste this code:

Public Function Valid(ByVal str As String) As String
    If (str = Nothing or str = "" or str = "@") Then
        Return Nothing
    Else
        If (str.Split("@").GetValue(1) = "" or str.Split("@").GetValue(1) = Nothing) Then
            Return "Reçu par : " + Join((str.Split("@").GetValue(0)).Split("|"), " et ")
        Else
            Return "Reçu par : " + Join((str.Split("@").GetValue(0)).Split("|"), " et ") + " accompagné de " + "Reçu par : " + Join((str.Split("@").GetValue(1)).Split("|"), " et ")
        End If
    End If
End Function

And then in the calculated field change the function and put this:

    = Code.Valid(Fields!aric_getpartylist.Value)

ADVISE: It's possible that the console of the calculated field give an error but don't worry just test it and it would be okay ! It possible that if you work with MS CRM DYNAMICS 365 it won't work because you need to disable rdl sandboxing from config file. (Check the doc to see how to do that)

SECOND SOLUTION(pure ssrs code)

We can isolate the problem, and it break at this line: Split(Fields!aric_getpartylist.Value,"@").GetValue(1) because the field is null so Split(nothing,"@") probably equal to an array with one element: null so there is no second element so this works: Split(Fields!aric_getpartylist.Value,"@").GetValue(0) but this doesn't work: Split(Fields!aric_getpartylist.Value,"@").GetValue(1) so you need make sure that this array generate by Split get at least 2 elements. So to solve this I just add a calculated field with this code:

= Fields!aric_getpartylist.Value & "@"

And then use the calculated field in the principal field with this code:

= IIf(
    IsNothing(Fields!prerecu.Value) or Fields!prerecu.Value = "" or Fields!prerecu.Value = "@" or Fields!prerecu.Value = " " or Fields!prerecu.Value = "@@",
    nothing,
    IIf(
        Split(Fields!prerecu.Value,"@").GetValue(1) = "",
        "Reçu par : " & Join(Split(Split(Fields!prerecu.Value,"@").GetValue(0),"|")," et "),
        "Reçu par : " & Join(Split(Split(Fields!prerecu.Value,"@").GetValue(0),"|")," et ") & " accompagné de " & Join(Split(Split(Fields!prerecu.Value,"@").GetValue(1),"|")," et ")
        )
    )

And it should work even on the Microsoft CRM Dynamics 365 !