How to form a range on a concatenated string array in SSRS report

Question

The data is in separate rows in SQL DB. It has been grouped and then concatenated to get the table in this format. However, I need to form a range for the Maturity Value.

I have tried few ideas like: =IIF(Fields!Pivot_2.Value.Contains(" Years"), "30 Years", Nothing)

and using Calculated Fields in Dataset Properties like: =Split(Fields!Pivot_2.Value,",")(0)

However not sure how to expand on it to get a range for this cell.

Desired output: Grab the biggest in years and smallest in days. If days are not there then smallest in months. Something like 30 Years - 1 Day.

Answer 1

You can use the following custom code

Public Function ParsePeriods(ByVal str As String) As String

Dim iMin As Integer = 1000000
Dim iMax As Integer = -1

Dim sMin As String = ""
Dim sMax As String = ""

' Replace text with numbers to make it comparable
str = Replace(str, " years", "0000")
str = Replace(str, " year", "0000")
str = Replace(str, " months", "00")
str = Replace(str, " month", "00")
str = Replace(str, " days", "")
str = Replace(str, " day", "")

' Split string elements and loop to get the min and max values
Dim strArr() As String = str.Split(",")

For Each element As String In strArr

If Cint(element) < iMin Then
iMin = Cint(element)
End If

If Cint(element) > iMax Then
iMax = Cint(element)
End If

Next


' Use the integer min and max values to create min and max strings
If iMin < 100 Then
sMin = Cstr(iMin) & " day(s)"
ElseIf iMin < 10000
sMin = Cstr(iMin/100) & " month(s)"
Else 
sMin = Cstr(iMin/10000) & " year(s)"
End If

If iMax < 100 Then
sMax = Cstr(iMax) & " day(s)"
ElseIf iMax < 10000
sMax = Cstr(iMax/100) & " month(s)"
Else 
sMax = Cstr(iMax/10000) & " year(s)"
End If

Return Cstr(sMin) & " - " & Cstr(sMax)

End Function

Now you can use an expression like the one below to call the custom code

= Code.ParsePeriods( ReportItems!MaturityValue.Value )

UPDATE

You can apply a similar logic for SP rating

Public Function ParseSPrating(ByVal str As String) As String

Dim sMin As String = "111111111"
Dim sMax As String = "-1"

str = Replace(str, " ", "")
str = Replace(str, "A", "9")
str = Replace(str, "B", "8")
str = Replace(str, "C", "7")
str = Replace(str, "D", "6")
str = Replace(str, "+", "2")
str = Replace(str, "-", "0")

Dim strArr() As String = str.Split(",")

For Each element As String In strArr

If Cint( Left(element & "1111",4) ) < CInt(sMin) Then
sMin = Left(element & "1111",4)
End If

If Cint( Left(element & "1111",4) ) > CInt(sMax) Then
sMax = Left(element & "1111",4)
End If

Next

sMin = Replace(sMin, "9", "A")
sMin = Replace(sMin, "8", "B")
sMin = Replace(sMin, "7", "C")
sMin = Replace(sMin, "6", "D")
sMin = Replace(sMin, "2", "+")
sMin = Replace(sMin, "0", "-")
sMin = Replace(sMin, "1", "")

sMax = Replace(sMax, "9", "A")
sMax = Replace(sMax, "8", "B")
sMax = Replace(sMax, "7", "C")
sMax = Replace(sMax, "6", "D")
sMax = Replace(sMax, "2", "+")
sMax = Replace(sMax, "0", "-")
sMax = Replace(sMax, "1", "")

Return sMax & " - " & sMin

End Function