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)
There is 2 solutions to do it, we can do it with custom code or in PURE SSRS Language.
Inspired by this question here I find a way to do it. We will use Visual Basic code to do it.
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)
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 !