Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

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 article)
  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 | 1 comment

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

CONSTRAINT [PK_tblFilm] PRIMARY KEY CLUSTERED

(

[FilmID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

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!

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 article)
  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

Comments on this blog

This blog has one comment:

Comment added by petepete on 13 June 2014 at 17:46 GMT
Any articles on executing DML in the SSRS custom code section??. Specifically something like : INSERT INTO dbo.ATBLNAME VALUES (User!UserID,Fields!THEURL.Value,date) Ultimately I want to track a url that was selected in a SSRS report. The url column in the report uses the Go to URL Action method.
Reply from Andy Brown (blog author)
In a word, I'm afraid ... no, not as yet!

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.