locked
SSRS - Vb code String Length for Header RRS feed

  • Question

  • I need to get single column multiple departments in header and if anything excess my header string length I need to display them as Plural

    .

    I’m using following code with string length 125 characters

    ** If I use 125 its repeating some fields name in header and diplaying 1,2,3, repeat1, repeat2, 4 and more 1000 Industries.

    =Code.outTruncate(Join(MultiLookup(source_expression, destination_expression, result_expression, “ dataset "), "|"), "Industry", "Industries", 125)

    If I use string length less than 100 It’s showing me exact data but in singular like

    1,2,3, and more 1000 Industry

    ------------------------------------

    code as Follows 

    --------------------------------------------------------------------------------------------------------

    ' Cut Off Long Titles, version 3.0

    Public Function outTruncate

    (

    ByVal sPipeDelimitedText As String,

    ByVal sSingularCategory As String,

    ByVal sPluralCategory As String, ByVal iMaxWidthInCharacters As Integer

    ) As String

                ' Loop until the string is the right length or there are no more pipes

                Dim iElementsRemoved As Integer = 0

                Dim sDummyText = sPipeDelimitedText

                Do Until (outWidthInAverageCharacters(sDummyText) < iMaxWidthInCharacters) Or (InStr(sDummyText, "|") < 1)

                            ' Remove everything after the last pipe

                            sPipeDelimitedText = Left(sPipeDelimitedText, InStrRev(sPipeDelimitedText, "|") - 1)

                            ' Create the "dummy text" to find out how long the final string will be

                            iElementsRemoved += 1

                            sDummyText = sPipeDelimitedText + "; and " + CStr(iElementsRemoved) + " More"

                Loop

                ' Find the last pipe-delimiter in the pipe-delimited string

                Dim iLastPipe As Integer = InStrRev(sPipeDelimitedText, "|")

                ' Add commas or semicolons, based on whether there's already a comma in the string

                If InStr(sPipeDelimitedText, ",") > 0 Then

                            ' Add on text for any elements that were removed, or add "; and" before the last element

                            If iElementsRemoved > 0 Then

                                        sPipeDelimitedText = sPipeDelimitedText & "; and " & CStr(iElementsRemoved) & " More"

                            Else

                                        ' If there's at least one pipe...

                                        If iLastPipe > 0 Then

                                                    sPipeDelimitedText = Left(sPipeDelimitedText, iLastPipe) _

                                                      & "and " & Mid(sPipeDelimitedText, iLastPipe + 1)

                                        End If

                            End If

                            ' Replace the pipes with semicolon-space

                            sPipeDelimitedText = Replace(sPipeDelimitedText, "|", "; ")

                ' There are no commas in the string already

                Else

                            ' Add on text for any elements that were removed, or add "and" before the last element

                            If iElementsRemoved > 0 Then

                                        sPipeDelimitedText = sPipeDelimitedText & " and " & CStr(iElementsRemoved) & " More"

                            Else

                                        ' If there's at least one pipe...

                                        If iLastPipe > 0 Then

                                                    sPipeDelimitedText = Left(sPipeDelimitedText, iLastPipe -1) _

                                                      & " and " & Mid(sPipeDelimitedText, iLastPipe + 1)

                                        End If

                            End If

                            ' Replace the pipes with comma-space

                            sPipeDelimitedText = Replace(sPipeDelimitedText, "|", ", ")

                End If

                ' Add the singular/plural category description ("Industry"/"Industries") at the end

                If iElementsRemoved = 1 Or iLastPipe = 0 Then

                            sPipeDelimitedText = sPipeDelimitedText & " " & sSingularCategory

                Else

                            sPipeDelimitedText = sPipeDelimitedText & " " & sPluralCategory

                End If

                ' Return the string

                Return sPipeDelimitedText

    End Function

    '_______________________________________________________

    Public Function outWidthInAverageCharacters (ByVal sText As String) As Integer

                ' Base the width on an average character width, like a "v" or a "d".

                ' So how many of those "average characters".

                ' Set up the strings of character widths

                Dim sNarrow As String = "ijlIJ,.'()" & """"

                Dim sAverage As String = "abcdefghknopqrstuvxyzABCDEFGHKLNOPRST" _

                    & "UVXYZ1234567890&*/$" & Space(1)

                Dim sWide As String = "mwMQW"

                ' Determine how wide sText is, in terms of "average" character widths

                Dim sngLen As Single = 0

                For iLoop As Integer = 1 to Len(sText)

                            If InStr(sNarrow,Mid(sText, iLoop,1)) > 0 Then

                                        sngLen += 0.5

                            ElseIf InStr(sWide,Mid(sText, iLoop,1)) > 0 Then

                                        sngLen += 1.5

                            Else     ' Assume this is "average" width

                                        sngLen = sngLen + 1

                            End If

                Next

               

                ' Assign the string's "width" to the function

                Return snglen

    End Function

    Thanks in Advance.

    Thursday, July 5, 2018 2:58 PM

All replies

  • Hi Surendr.a10,

    I am not professional in VB,  and I can't make the VB work in my environment. If you want to replace character which exceed string length, I think you could use expression like below to modify it in Tablix

    =iif(len(Fields!textn.Value)>7, left(Fields!textn.Value,7) & "aaa",Fields!textn.Value)

    In addition, about VB code issue, you could post this issue on VB forum for more details.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 6, 2018 2:12 AM
  • Hello Zoe Zhi,

    Thank you for response and I like the Idea about VB Forum.

    Thank You

    Monday, July 9, 2018 5:30 PM
  • Hi Surendr.a10,

    You are welcome, I hope this will help you. And if you solve this issue later with other forum's help and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.


    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 1:34 AM