SSRS custom assemblies using C# or VB
Part one 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 (this blog)
  2. Creating a Project, Class and Function in VB or C#
  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 | 6 comments

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 Custom Assemblies in Reporting Services

In SQL Server Reporting Services (SSRS) you have access to a whole range of built-in functions.  However, to release the full power of SSRS you'll need to create custom assemblies.  This rather long blog will explain how - and why - you might do this!

Our Example - a Proper Case Text Function

Suppose that you frequently want to capitalise a word.  SSRS includes functions to convert text to upper and lower case, but nothing to capitalise a word:

The text functions

The LCase and UCase functions convert text to lower and upper case, but neither capitalises a word.

 

You could get round this by embedding a VB function in a report, looking something like this:

Embedded code function

Embedded code is hard to read, limited to a single report and VB-specific.

However, the best solution is to create a function in VB (or C#), compile this into something called a DLL file and then reference this within your report.  This is called a custom assembly.

Advantages of Custom Assemblies

There are a fair few hoops to jump through to create a custom assembly; why would you need one?  Here are some reasons:

Advantages Notes
Portability Code that you embed within one report is obviously not available for other reports to use, whereas you can reference a custom assembly from any report.
Error-checking When you type code into the Report Properties dialog box there's no way of knowing whether you've made any syntactical mistakes until you try to run it, whereas custom assemblies provide full Intellisense and code formatting.
Languages You can write custom assemblies in either VB or C#.
Speed It's not usually a big issue, but code in a custom assembly can be pre-compiled, and so will run more quickly.
Debugging You can debug code using a custom assembly.

Following the Rest of this Blog

To create a custom assembly you will need to do the following:

  1. Create a project, class and function.
  2. Build, deploy and reference your DLL.
  3. Use the custom assembly within a report.
  4. Deploy your project (perhaps automatically, using post-build events).
  5. Learn how to debug a custom assembly.

Let's begin by creating a project, class and function in Visual Studio.

This blog assumes that you already know basic programming in either VB or C# (examples are given for both languages).

This blog has 6 comments

Comment added on 23 October 2012 at 10:49 GMT
This is a really useful tutorial. Thanks for explaining it all so carefully. Is is possible to extend this sort of thing to handle events like printing?
Reply from Andy Brown

CAN you handle events like printing?  If you add a comment giving a summary of what you mean, or a link to a relevant website, I'll certainly consider this.  There's always more to learn about SSRS!

Comment added on 29 October 2012 at 11:31 GMT
I have a report where summaries are shown for different levels of the company in a descending hierarchy: Division, Region, Area, Section. I use the standard + to reveal the lower levels of the hierarchy. What my client requires is the ability to click on a level (say Area) and print the summaries for the Sections within that Area. Is there a way of embedding some code within an expression to pass the correct parameters to a print routine and have the print flow through without further input from the user? Your assistance is greatly appreciated.
Reply from Andy Brown

Off the top of my head, I'd say it wasn't possible without adding some JavaScript to your report somehow.  However, what you could do is create an action for each area, such that when you clicked on the area it transferred to another report, passing in the area id as a parameter.  Your client could then print out this second report.  Admittedly that's two mouse clicks, not one.

If anyone can improve on this, please add your penn'orth!

Comment added on 19 November 2012 at 14:00 GMT
Great tutorial! 

is there a way to auto run a code to do something before the report is rendered? Is there an on load event, or in VBA if you name your function autoexec it would run first?  We have an app with an Oracle table containing report security levels defined for each user.  I would like to use this instead to check if the user can run/view a specific SSRS report or not -- the report can still be rendered I guess but with the visibility property of the information set to false, or possibly redirect to a new report with a static message "not authorized"?  Managing the security via the SSRS security would be duplicated effort.

Any ideas? Thanks!
Reply from Andy Brown

You could create a true/false (Boolean) report variable whose expression would be set to the value of a custom assembly function (which could look at the Oracle database).  The value of this variable would be calculated once only, when the report first loads.  You could then set the visibility of all relevant parts of the report to be the value of this variable. 

Not for the faint-hearted - you'll need to learn about custom assemblies and report variables - but it should work OK!

Comment added on 28 January 2013 at 16:37 GMT
Hi Andy

Many thanks for taking the time to put up this series of posts. Probably the best set of posts on the subject are found so far taking things nice and steady and doing an end to end real world example.

I do still have a problem however, and it might be an area that will make you cry, "oh no, someone has asked THAT question"! ;)

I have successfully created a custom dll along the same complexity as the ones you have shown, however, as soon as I have raised the complexity I've hit a problem that I just cannot seem to get solved.

My custom dll calls into other dll's (namely System.Data.SqlClient classes to get values from a database) and from what I have read so far in other places, this means I need to play around with Code Access Security files because previewing runs your code under full trust, but deployed code runs without full trust and denies the calling of your code.

My custom assembly method (happens to be C#) :
1. Takes an encrypted string.
2. Calls a stored procedure in  SQL Server with that string as a parameter.
3. Get result from Stored Procedure which happens to be an encrypted connection string to use in my report.
4. I decrypt this result and return the connection string as the final result of my custom assembly call. 

This is all done so I can dynamically switch which database I report against depending on the original value passed to the report.

This all works perfectly in the preview tab. Debugging works fine as well. However, as soon as I deploy to the Report Server and run in Report Manager the report fails with, "Error during processing of the ConnectString expression of the data source" and I think this is because of the full trust issue.

Have you had any success developing a report and getting the full trust configuration files correct so that your solution works on a Report Server?

Any help would be greatly appreciated as I've been pulling my hair out with this.

Kind Regards,
Martin
Reply from Andy Brown

Hi Martin

Great question - rubbish answer coming up!  When I'm training on SQL or SSRS, I always emphasise that there is one area I can't help with: security.  I too tear my hair out with problems like this.  The only thing I can suggest is copying your question to StackExchange or a similar site. Unless any other readers can help?

Comment added on 12 June 2013 at 18:27 GMT

Hello Andy,

First and foremost, a great big THANK YOU to you and your team for your blogs and youtube videos...they are truly appreciated!!  i've learned a great deal from them and your teaching methods are straightforward and thorough and easy to understand.  i am actually considering coming to UK to take one of your courses in person!

I have a report uses a de-normalized table for the data.  i created the table using data from 6 different business sections within our company, and because of this not all sections have data in all fields.  for example, fields 'category' and 'type' have data for Membership, but for Certification there is data in 'category' only.  my objective is to write custom code that allows for 'optional' columns on the report if data exists in the 'category' and 'type' fields.  is this possible?  i know i'll need custom code to accomplish this, but first i need to know if it's even possible.

By the way, i'm only 9 months into my SQL/SSRS career, and i am primarily self-taught (with your help, of course!!) - the fact that i'm even thinking of tackling something complex like this is a tribute to your teaching...the possibilities are endless!!  :)

Reply from Andy Brown
Firstly, thank you for the kind comments!  At some stage we'll launch a one-to-one online training facility to help you, but that's probably a year or two off ...

I'll be honest and admit I don't fully understand what you're trying to achieve, but I would have thought you could do it without any custom code.  Couldn't you right-click on a column of your report and set the column visibility to be an expression depending on the underlying data fields?  If you do have to use custom code, I am convinced that ANYTHING is possible!
Comment added on 17 September 2013 at 08:24 GMT
Great tutorial - I created a project called SSRSFunctions with a class called Methods using C#. However I'm just having one problem, I'm getting the following error after attempting to run my report:

"Error while loading code module: 'SSRSFunctions, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'. Details: could not load file or assembly 'SSRSFunctions, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of it's dependencies.  The system could not find the file specified"

Obviously the above error indicates that SSRS cannot find my assembly, however I followed your directions to the letter.  I have my SSRSFunction.dll in the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin  folder and am referencing this file in my .rdl file, so I'm at a loss here. 

Any ideas what else might be causing this?


Reply from Andy Brown
Try this link on StackOverflow in the first instance, I'd suggest.

Andy

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.