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)

If I can add my 2 cents, actually your expression looks fine. Can you somehow check when you have null or empty value for Fields!aric_getpartylist.Value field does it go to you last statement. I mean rather than your last IIF statement try showing some value. If it shows value i.e your first two IIF does not work and then may be we can move forward. Also expression like this can be helpful =IIF(Len(Fields!aric_getpartylist.Value) <= 0, true, false) — AnkUser, Aug 07 '19 at 13:09
I just test it and it doesn't work too. I think this language precalculate the result for any path of the IF so it give an error... Is there a statement as `return` to escape from the IIF just after the `nothing` ? — bosskay972, Aug 07 '19 at 13:42
@bosskay972 As far as I know, there's no `return` and that likely wouldn't fix it anyways. What is the datatype of `aric_getpartylist`? — Steve-o169, Aug 07 '19 at 13:47
I put more precision in my post and the problem could be insoluble... — bosskay972, Aug 08 '19 at 08:02

bosskay972 · Accepted Answer · 2019-08-08T15:08:53.230

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.

click on Conception(if you already did it go to the next step)
Go to the toolbar on the TOP
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 !

score 0 · Answer 2 · answered Aug 07 '19 at 12:35

0

I see the problem, though the solution is going to be somewhat of an odd workaround... The issue you're having is that in SSRS, the entire IIF expression is evaluated at the time it is executed. This means that regardless of the result of the expression, it is still trying to at least evaluate the Join(Split(Split(... parts of the equation but then it would only display the part that matches the conditional.

What I'd recommend -- and there may be a better way -- is to use an ISNULL() in the query on this field. Something like ISNULL(aric_getpartylist, 'Nothing') will take any NULL values and simply replace them with the string Nothing. Then, you can simply check the field in SSRS for the string "Nothing"(assuming the field will NOT normally contain that string).

IIf(
Fields!aric_getpartylist.Value = "Nothing",
Nothing,
IIf(
    Fields!aric_getpartylist.Value = "Nothing",
    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 ")
        )
    )
)

answered Aug 07 '19 at 12:35

Steve-o169

2,066
1
12
21

I only work with SSRS RDL Language so I can't use ISNULL, because I can't handle the table behind the CRM's record... Maybe there is something in the SSRS which permit to do it but for now I don' tknow how to put SQL command in this interface – bosskay972 Aug 07 '19 at 12:49
@bosskay972 Perhaps a calculated field with a value of `=IIF(IsNothing(Fields!aric_getpartylist.Value), "Nothing", Fields!aric_getpartylist.Value)`? – Steve-o169 Aug 07 '19 at 12:51
It give me the same result :/ – bosskay972 Aug 07 '19 at 12:58
Maybe I need to calculate this line too: `Split(Fields!recubrutnotnull.Value,"@").GetValue(1) = ""` – bosskay972 Aug 07 '19 at 13:00
@bosskay972 That line wasn't included in the expression in your question, but yes, that could help. – Steve-o169 Aug 07 '19 at 13:23
I doesn't work too, I ask a interesting question in the comment of my question please check it – bosskay972 Aug 07 '19 at 13:43

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

2 Answers2

FIRST SOLUTION(custom code)

SECOND SOLUTION(pure ssrs code)