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

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.

CodePudding user response：

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 )