BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Embedding Code in Reporting Services
- Embedding Code in a Report to Show a Film's Status
- Truncating a Film's Description using a Function (this blog)
- 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:
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:
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:
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.
- Embedding Code in Reporting Services
- Embedding Code in a Report to Show a Film's Status
- Truncating a Film's Description using a Function (this blog)
- Writing VB Code in a Class Library