WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
How to embed and call VB functions within a Reporting Services report
Part two 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 (this blog)
  3. Truncating a Film's Description using a Function
  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.

Embedding Code in a Report to Show a Film's Status

As promised, we'll create a function which will give us the Oscar status of a film:

Film Oscar status report

Each film can have one of three status descriptions, depending on whether it won or was nominated for Oscars, or neither.

Writing the Function

To embed the function to show a film's Oscar status within a report, you first need to write it!  Here's a suggestion:

Function OscarStatus(Wins As Integer, Nominations As Integer) As String

'determine whether an Oscar winner, nominee or neither

If Wins > 0 Then

Return "Winner"

ElseIf Nominations > 0 Then

Return "Nominee"


Return "N/A"

End If

End Function

This function will take two arguments as follows:

Argument Type What it means
Wins Integer How many Oscars the film won.
Nominations Integer How many Oscars the film was nominated for.

Given these two bits of information, the code uses an IF statement to return the required status (which is of data type String).

All functions that you create must return one - and only one - value, which they should do using a Return statement.

Types of Argument Allowed in VB

Here's a list of the main types of data you can use in a VB function:

What to use What it denotes Example
Boolean Anything either True or False Whether an actor is still alive
Integer Any whole number The length of a film in minutes
Double Any other number (eg 3.14) The budget for a film in dollars
Date Any date or time The date a film was released
String Any string of text characters The name of a film

There are a lot more types, but if you're just starting out with VB these will do for now.

Embedding the Code in your Report

Now that you know what you want to say, how do you say it?  First show your report's properties in Design view:

Right-click menu report properties Top menu report properties
Right-click on the report's edge ... ... or use the Report menu.

You can now type in your code:

Typing in Code

Type your code into the Code tab, as shown here.

If you've written your code in another software application, any formatting will be lost when you save your code in this dialog box.

Finally, select OK to confirm the new code.

Using Embedded Code

Once you've embedded code in a report, you can call it within any expression by using the prefix Code.  This is usually best done in SSRS by adding a calculated field to a dataset:

Add calculated field

Right-click on any dataset and choose to add a calculated field.

You can now give your new field a name and an expression:

Entering name for calculated field

Here we've called the new field OscarStatus, and are about to give it an expression.


Type in the function you want to use, prefixed by code.:

Expression using code

SSRS won't give you any help, and will leave your function name underlined.

Was it worth it? 

The above example allows us to create a function which spits out the status of a film given two parameters, but you can do this in at least 3 other ways, all of which are probably better!

Firstly, you could include the expression in the underlying SQL view or stored procedure.  This would have the advantage that you could then use the result of the expression in any report based on this view/procedure:

-- return status given Oscar performance


WHEN FilmOscarWins > 0 THEN 'Winner'

WHEN FilmOscarNominations > 0 THEN 'Nominee'


END AS OscarStatus

Alternatively, you could have used a SWITCH expression in a calculated field in SSRS:

SWITCH expression

This expression would also return the Oscar status.

This calculated field could be used in any table, matrix, list or chart based on this dataset. 

Finally, you could create the function in a separate class using either VB or C# and reference it as a custom assembly (something I'll blog about soon):

public static string OscarStatus(

int Wins,

int Nominations

) {

if (Wins > 0)


return "Winner";




if (Nominations > 0)


return "Nominee";




return "N/A";




This would allow you to use indentation, comments, Intellisense and - if you were that way inclined - C# as above, and would be appropriate for more complicated functions.

For simple functions like the one on this page, it's hard to see how embedded code is worth it (an SSRS or SQL expression would be a better idea). 

Given the hint above, let's look at a more complicated example! 

This blog has 0 threads Add post