How to write custom code in SQL Reporting Services 2005 in 3 easy steps

Have you ever written a report in Reporting Services 2005? Most people have, but to get anything worth it's salt you need to have some pretty advanced t-sql experience or have a good background in databases to be productive in SQL Reporting Services. For those of you that are experienced in procedural language like C#.NET or VB.NET but are not in t-sql this article is for you. In this post I am going to show you a quick and dirty way to get some advanced custom code working in your reports.

First off, you need to know what your options are before you get started:

1. You can build custom assemblies in any .NET compliant language of your choice
   
2. You can embed Visual Basic code into your report directly
   

This article is going to discuss the 2nd option because it easier and quicker to get your custom code working than the 1st option. Don't get me wrong there are good reasons for both and it is better to know when to use one over the other in certain situations. 

Now that we know what we are trying to achieve, lets dig into to some code!

Step 1: Build a report 

 -- Build the dataset

-- Build the report layout

Step 2 : Add custom code to your report to do something useful

-Click on Report > Report Properties

-Click the Code tab

This is where you will put your custom code and reference in your report. Some things to take note of:

• All code that is in this block must be Visual Basic .NET code
• All code that gets executed here runs with full-trust permissions when using 'Preview'
• In order to get custom authenication hops , i.e. like database calls you must add the required assemblies and required permissions
• When performing database calls, web services calls, you need to set the trust level on the web.config file on the Report Server
• Also any additional libraries that you use, i.e. System.Data.SqlClient must be referenced explictly in the references tab. 
• Any code that you use here can be directly used anywhere in your reports via the '=Code.Method()' syntax. If you are just building functions this will work, however if you put the class in the code you will need to reference the class like Code.ClassName.MethodName() in order to access it. 

Step 3 : Write some Visual Basic.NET code

 - I prefer to write the code in Visual Studio in a class library project so I can get the benefit of intellisense and complie-time checking. Once I have what I need, I copy it into the code tab of the Report>Report Properties window like so.

--Visual Studio Code Window

-- Copy the code into the code tab

-- Place code into your report wherever you like

-- Run your report to see what your logic returns

Conclusion

To recap what we did was pretty basic in terms of complexity, but offers us the power of the .NET Framework in our reports. Because reporting services utilizes an expression-based processing engine you can make many things dynamic throughout your report. I think that personally this is the best way to add custom logic that you could not do otherwise. However, we could have hard-coded this logic in an iif() statement in the report and that would have worked as well but the point I am trying to make is that we can utilize the .NET Framework class libraries to help us write better reports faster and more efficiently.  

Josh Clark

If you have any comments or questions, please comment on this blog or send me an email at 

jclark434175@gmail.com