SSRS custom assemblies using C# or VB
Part two of a six-part series of blogs

Reporting Services allows you to write functions in C# or Visual Basic, then reference these as custom assemblies. This blog shows how - and why - you might do this.

  1. Creating Custom Assemblies in Reporting Services
  2. Creating a Project, Class and Function in VB or C# (this blog)
  3. Create and Deploy the Code DLL
  4. Referencing and Using Custom Assemblies in Reports
  5. Post-Build Events to Help Deployment
  6. Debugging Custom Assemblies

Posted by Andy Brown on 03 August 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.

Creating a Project, Class and Function in VB or C#

Suppose that we want to create a function to capitalise words, and make it accessible to all our SSRS reports.  To start this off, we'll need a project in Visual Studio.

If you're using BIDS in SSRS 2008 R2, you're already using Visual Studio 2008; if you're using SQL Server 2012 Data Tools, you're already using Visual Studio 2012.

Creating a New Project

Typically you will put all of your custom assemblies in a single class in a new project.  To create this, first add a new project to your solution:

Creating a new project

Right-click on your report project's solution in Solution Explorer, and choose to add a new project.

Choose to create a class library in either VB or C#:

Creating a class library

Follow the numbered steps below.

The numbered steps shown above are:

  1. Choose to create either a C# or a VB project, depending on your preference (and don't listen to people who tell you that C# is better).
  2. Choose to create a class library (for the purposes of creating a custom assembly, think of this as just a place in which to write code).
  3. Give your class library a name, and choose where to store it (we'll call ours FunctionsC, although the default name is ClassLibrary1).

If you can't see the installed templates shown above, it's probably because you've only installed Reporting Services, and not the full-blown Visual Studio.  Time to download Visual Studio Express from the Microsoft website (which is free)?

You'll now see another project appearing in Solution Explorer:

sad

Here we've chosen C# as a programming language. Visual Studio always creates a class (Class1.cs or Class1.vb) for you, which I always delete!

 

Creating a Class

Now that you have a class library, you can create a class within it (again, think of this as just a place in which to write code):

Inserting a class

Right-click on your new project, and choose to add a class to it (the options for a VB project are the same).

 

You can now give your new class a name:

Renaming a new class

Again, the only difference for Visual Basic would be that the file extension would be vb and not cs.

 

You can now double-click on your new class at any time to edit it:

Double-click to edit a class

Double-click on the name of your class in Solution Explorer to edit its contents.

 

Creating Functions within a Class Library

If you've followed the steps above, you'll now be editing your VB or C# class.  Here's what our ProperText function might look like (make sure you take note of the hints shown below!).  First in C#:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace FunctionC

{

public class SampleClass

{

// other functions could go here

public static string ProperCase(string What)

{

// capitalises a name correctly (eg fReD becomes Fred)

string FirstLetter = What.Substring(0, 1);

string Remainder = What.Substring(1, What.Length - 1);

return FirstLetter.ToUpper() + Remainder.ToLower();

}

// other functions could go here

}

}

Note that the class and each method within it must be declared as public (so everything is visible from outside the class).  Methods must also be made static (so that we don't have to create an instance of the class to access its methods).  If the last sentence didn't make sense, don't worry - just remember to use static!

The VB equivalent might look like this:

Public Class SampleClass

 

'other functions could go here

 

Shared Function ProperCase(What As String) As String

 

'capitalises a name correctly (eg fReD becomes Fred)

Dim FirstLetter As String = What.Substring(0, 1)

Dim Remainder As String = What.Substring(1, What.Length - 1)

 

ProperCase = FirstLetter.ToUpper & Remainder.ToLower

 

End Function

 

'other functions could go here

 

End Class

Again, note that each VB function must be declared as Shared.  As for C#, this is so that you don't have to create a new instance of the class before accessing its functions; and as for C#, if you don't understand that sentence, don't worry - just remember the keyword Shared!

Now that you've created a project, class and one or more functions, it's time to make them accessible within SSRS.  To do this you have to create a DLL.

This blog has 0 threads Add post