WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 525 reviews for our classroom and online training
How to embed and call VB functions within a Reporting Services report
Part one 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 (this blog)
  2. Embedding Code in a Report to Show a Film's Status
  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 Reporting Services

This blog is for anyone who's ever wondered what the Code tab of a report's properties is for:

Code tab of report properties

The Code tab allows you to embed functions within reports. This blog explains how and why to do this!


Our Examples

This blog will use two examples.  The first is a simple one to show the status for each film (sorry, movie), given how many Oscars it was nominated for and won:

Oscar status for films

A function will return either Winner, Nominee or N/A, depending on how well the film did in the Academy Awards.


Our second example will truncate a long description for each film at a sensible point:

Truncating description sensibly

This is a harder function: it accumulates words up to a maximum string length.


For both examples, we'll consider whether embedded code is actually a sensible way to go!

All of the code in this blog uses Visual Basic, not C#.  This isn't prejudice (although I'm not free of that); just that you can't embed code in SSRS using any language other than VB (you can, however, write custom assemblies in C#).

Downloading the SQL for this Example

If you want to try any of the ideas in this blog yourself, run the following script in a database to create the table called tblFilm that you will need:

CREATE TABLE [dbo].[tblFilm](

[FilmID] [int] NOT NULL,

[FilmName] [nvarchar](255) NULL,

[FilmSynopsis] [nvarchar](max) NULL,

[FilmOscarNominations] [int] NULL,

[FilmOscarWins] [int] NULL



[FilmID] ASC




INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (1, N'Jurassic Park', N'Scientists clone dinosaurs to populate a theme park which suffers a major security breakdown and releases the dinosaurs.', 3, 3)

INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (2, N'Spider-Man', N'When bitten by a genetically modified spider, a nerdy, shy, and awkward high school student gains spider-like abilities that he eventually must use to fight evil as a superhero after tragedy befalls his family.', 2, 0)

INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (3, N'King Kong', N'In 1933 New York, an overly ambitious movie producer coerces his cast and hired ship crew to travel to mysterious Skull Island, where they encounter Kong, a giant ape who is immediately smitten with leading lady Ann Darrow.', 4, 3)

INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (5, N'Superman Returns', N'After a long visit to the lost remains of the planet Krypton, the Man of Steel returns to earth to become the peoples savior once again and reclaim the love of Lois Lane.', 1, 0)

INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (6, N'Titanic', N'Fictional romantic tale of a rich girl and poor boy who meet on the ill-fated voyage of the ''unsinkable'' ship.', 14, 11)

INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (7, N'Evan Almighty', N'God (Freeman) contacts Congressman Evan Baxter (Carell) and tells him to build an ark in preparation for a great flood.', 0, 0)

INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (8, N'Waterworld', N'In a future where the polar ice caps have melted and most of Earth is underwater, a mutated mariner fights starvation and outlaw "smokers," and reluctantly helps a woman and a young girl find dry land.', 1, 0)

INSERT [dbo].[tblFilm] ([FilmID], [FilmName], [FilmSynopsis], [FilmOscarNominations], [FilmOscarWins]) VALUES (9, N'Pearl Harbor', N'Pearl Harbor follows the story of two best friends, Rafe and Danny, and their love lives as they go off to join the war.', 4, 1)

So now we've set some ground rules, let's have a look at how to embed code in a report!

This blog has 0 threads Add post