SSRS Code and Expressions

Epicor ERP 10
1

Dears,

I’m having a problem with an SSRS report of my creation, the report runs fine and all except for one expression I wrote whose behavior changes once the condition is not met.

The name of the column which has the expression is “Acceptance Criteria” and the idea behind it came from a couple of fields in specification attribute, the fields are [“ListValues”, “CmbPassValues”].

The expression does the following it checks whether the attribute in question is of type ComboBox, then if it is CB it matches pass values with the list values, extracts the description of the list values, concatenates all the descriptions with “,” and displays them in the report.

There is a trick as to how all this is done since Report Builder doesn’t have loops in it, this has forced me to use the Code Feature in the Report Properties as displayed below.

SSRSCodeFeature
SSRSCodeFeature648×503 10.2 KB

I wrote a VB function that takes both lists, breaks them down, and loops through them to extract the description and concatenates them, then to return them as one value, it has been tested in Visual Studio.

Public Function GetAcceptanceCriteria(ByVal PLV As String, ByVal LV As String) As String
    Dim AC As String = ""
    Dim PLVA = PLV.Split("~")
    Dim LVA = LV.Split("~")
    For i As Integer = 0 To (PLVA.Length - 1)
        For j As Integer = 0 To (LVA.Length - 1)
            If PLVA(i) = LVA(j).Split("`")(0) Then
                If Len(AC) = 0 Then
                    AC = AC + LVA(j).Split("`")(1)
                Else
                    AC += ", " + LVA(j).Split("`")(1)
                End If
            End If
        Next
    Next
    If Len(AC) = 0 Then
        AC = "Values were not matched!"
    End If
    Return AC
End Function

Now the problem is that the expression that I wrote works perfectly as long as the attribute type is ComboBox but when it comes across a record with a type of Numeric it returns “#Error”, I attached a copy of a printed report below.

Report-STBSingleTP (Single Test Point for Stabilit_97737.pdf (273.5 KB)

I don’t know what I’m missing here, it is just a single IIF statement that checks whether the type is CB or not, is there anything I need to know about code feature to solve this problem?

I appreciate any information you could give me.

2

The Expression is as follows:

IIf(Trim(Fields!InspAttr_AttrType.Value) = "ComboBox", CStr(Code.GetAcceptanceCriteria(Fields!SpecAttr_CmbPassValues.Value, Fields!SpecAttr_ListValues.Value)), "Not ComboBox")

It is surprising that it doesn’t work.

3

Seems this is because SSRS evaluates both true and false and encounters an exception.

You could try some workarounds:

  • Try using 2 textboxes on top of each other and hide them depending on the type numeric or combobox, although I’m not sure if expressions in hidden fields would also trigger even when they shouldn’t.
  • Try some of the workarounds on the link, such as using an IIF for each function parameter.