How to embed and call VB functions within a Reporting Services report
Part three of a four-part series of blogs

SQL Server Reporting Services allows you to write (and use) custom functions written in Visual Basic within a report. This blog considers the pros and cons, and shows how to do this.

  1. Embedding Code in Reporting Services
  2. Embedding Code in a Report to Show a Film's Status
  3. Truncating a Film's Description using a Function (this blog)
  4. Writing VB Code in a Class Library

Posted by Andy Brown on 13 July 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Truncating a Film's Description using a Function

Again as promised, we'll create a function which neatly truncates a film's description to a maximum (say) of 40 characters:

Truncated film descriptions

The clever thing is that we avoid breaking any description in the middle of a word.

You can follow the steps in the previous part of this blog to embed report code - the rest of this page shows what the function could look like.

Our Specimen VB Function

Our VB function relies on splitting a long string of text into an array of words, using the spaces between words as a separator:

Function Prefix(What As String, MaxLength As Integer) As String

'arguments:

'WHAT the film synopsis or other string of text passed in

'PREFIXCOUNT the maximum number of letters to return

Const Ellipsis As String = "..."

'split the text passed into an array of single words

Dim Words() As String = What.Split(" ")

'create a variable to hold the final prefix

Dim StartString As String = ""

'loop over all of these words, adding each to the prefix ...

Dim Word As String

For Each Word In Words

'if we've reached the maximum number of letters requested, don't add word

If Word.Length + StartString.Length + 1 > (MaxLength - Ellipsis.Length) Then

Exit For

Else

'add this word onto list accumulating

StartString &= Word & " "

End If

Next

Return StartString & Ellipsis

End Function

Here's an expression which would give us the correctly truncated synopsis for each film:

Expression using function in VB

The expression calls the Prefix function in the report's embedded code.

The Same Question: Was it Worth It?

On this occasion it's easier to see the justification for using embedded code.  This function would have been hard to write in SQL (it can be done, but it's tricky), and I can't at this moment see how it could have been done in an SSRS expression.

However, the problem now is where to write the code.  The Report Properties dialog box doesn't give any help at all:

Report Properties dialog box - code tab

You can type your function directly into this dialog box, but you'll need to be a phenomenally good programmer!

The best answer is to write your code into a .NET class (as covered in the last part of this blog) then copy the code into the dialog box shown above.

  1. Embedding Code in Reporting Services
  2. Embedding Code in a Report to Show a Film's Status
  3. Truncating a Film's Description using a Function (this blog)
  4. Writing VB Code in a Class Library
This blog has 0 threads Add post