<

Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SSRS Report - Control Your Data Length in a table column per column line

Published on
13,140 Points
7,040 Views
1 Endorsement
Last Modified:
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column.

Ok the fun part was "how am I going to control the output to the column from the table? ". So the first thing I started working on was seeing how many characters would fit on a single line of the column that I need to use; and still look nice in the report.

Since the report had alot of data per detail row in the table I ended up with a column width length of 29 characters for this report. What was nice was that on this report the data was being split up by using a comma as a delimiter. Once the width was decided, the next step would be to wrap the incoming data so that each part of the data would wrap by design rather than arbitrary.

My favorite namespace in coding this type of data is using RegularExpressions; the use of RegularExpressions is only limited to your imagination. The code could be used with other applications. You would just have to experiment with it in the project that you are working on.

The code is pretty straight forward when you look at; just place the code in your report code tab properties. The delimiter can be just about any character you want. The sample uses the "comma" character as a delimiter. The InputString is the data that is coming in from you dataset; an example would be Trim(Fields!WordValues.Value) and the last field would be the width of your column. The sample below is using 29, meaning that the maxium number of characters on a single column row.

This is a sample of what the code would look like in the textbox or column cell of the table.

=Code.FineDelimiter(",",Trim(Fields!WordValues.Value),29)

Open in new window


Sample out put would look something like this.....

Column A
-----------------------------
1234,6078,words,or,the,end,
 chars, long, the, length, of 
,970444, 77707,the, key, to, 
how, this, works

Open in new window


You will notice that the contents of "Column A" now wraps cleanly at a word or comma.

So, the only thing remaining is to show you the code for the FineDelimiter() function :

 
Public Function FineDelimiter(ByVal Delimiter As String, ByVal InputString As String, ByVal MaxLen As Integer) As String
        Dim FirstRow As Boolean = True
        Dim Counter As Integer = 0
        Dim MaxCnt As Integer = 0
        Dim strHolder As String = Nothing

        If InputString = Nothing Then Return Nothing

        For Each matchCurrent As String In  System.Text.RegularExpressions.Regex.Split(InputString, Delimiter)
            Counter += matchCurrent.Length + 1
            MaxCnt += matchCurrent.Length + 1
            If Counter > MaxLen Then
                FirstRow = False
                Counter = 0
            End If
            If FirstRow = True Then
                strHolder += matchCurrent + Delimiter
            ElseIf Counter = 0 Then
                If InputString.Length + 1 = MaxCnt Then
                    strHolder += vbNewline & matchCurrent
                Else
                    strHolder += vbNewline & matchCurrent + Delimiter
                End If
                Counter = matchCurrent.Length + 1
            Else
                If InputString.Length + 1 = MaxCnt Then
                    strHolder += matchCurrent
                Else
                    strHolder += matchCurrent + Delimiter
                End If
            End If
        Next matchCurrent

       If MaxCnt >= 2 And MaxCnt <= MaxLen Then
            Return strHolder.Substring(0, strHolder.Length - 1)
        Else
            Return strHolder
        End If
        Return Nothing
    End Function

Open in new window


The only other consideration is taking care when deciding your character count (width) depending on the type of font you are using, and once determined, dont change the font :)

1
Comment
Author:planocz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Join & Write a Comment

Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month