Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Server Reporting Services Custom Code Assemblies


By:   |   Last Updated: 2014-05-08   |   Comments (5)   |   Related Tips: > Reporting Services Functions

Problem

What are SQL Server Reporting Services Custom Code Assemblies? Why would I want to use them?  Check out this tip to learn more.

Solution 

In a previous tip, I described the process of adding code directly to an individual SSRS report. As noted in the tip, using embedded code provides for some code reuse while at the same time giving report developers, local report level customized coding. Similarly, we can actually take the coding to a second level by creating custom code assemblies that are referenced by a SSRS report via a class\ function embedded in a dll. Of course, using this method extends our code reuse from beyond a single report to across a group of reports. We can then make changes in one location which will then be applied to all reports which reference the assembly code. Furthermore, we can add multiple functions within a single class file, and of course, the coding can take place in Visual Studio and allow for easier use of version control applications. Finally, the coding can be completed in any DotNet language; for this tip, though, we will use Visual Basic. As with any process, there are some disadvantages which include a rather complicated process of creating, deploying, and referencing the code assembly, and many find troubleshooting the assembly to be rather complicated. Finally, report data sets are not allowed to be passed to custom assemblies.

Develop Custom Assembly and Add to an SSRS Report

To display data for our reports, we will again use AdventureWorks 2012 SSAS database; the database is available on Codeplex. Once you download and install the SQL Server database, we will subsequently use the SSRS 2012 sample reports. Now we can create a simple function to evaluate whether a number is less than zero or not; if the value is less than zero then the function will return the string "Red". Otherwise it will return the string "Blue". Thus, we will first open up Visual Studio 2010, as shown below, and create a new solution and project for our function. 

New Project

As mentioned earlier, the coding for this tip is being completed using Visual Basic. Thus, as coded below, we create a class and then a very simple function. The function accepts one argument, an integer and then returns a string with the color red or blue. We use an If / Else statement to decide which color we want returned by the function.           

Imports Microsoft.VisualBasic

Public Class ColorClass
Public Shared Function COLORNUMBER(ByVal InputNumber As Integer) As String
Dim ReturnColor As String
If InputNumber < 0 Then
ReturnColor = "RED"
Else : ReturnColor = "BLUE"
End If
Return ReturnColor
End Function

End Class

Now that the function is built, we have a several step process to get the assembly deployed. First, we need to sign the assembly with a strong name. In order to sign the assembly, we first must right mouse click on the project and select properties as displayed subsequently. 

Project Properties

After that, we need to navigate to the Signing tab. First, as shown below, click on the Sign the assembly check box, and then click "New" in the Choose a strong name key file list box.

StrongName

Next, on the Create Strong Name Key window, illustrated below, add a key file name and a password. Secondly, you can click ok twice to finish the signing process.

Strong Name
password

As shown below as part of our security setup for the assembly, we need to adjust the assembly to allow only partially trusted assemblies. We complete this task by opening up the AssemblyInfo.vb file available within the project. 

Assembly Open
 

Once open, you need to add <Assembly: AllowPartiallyTrustedCallers()>  as shown in the subsequent screen shot. This addition may also require a reference to the System.Security object.

Assembly Additions

Now, we are ready to build the project as noted next.

deploy build

At this point, the assembly is ready to be copied to our report server directories (see below) and to the c:\windows\assemblies directory (aka GAC or Global Assembly Cache). You may have to install the file as described in this link. The dll file will reside in the bin\debug directory within our project folder. Again, the dll is copied to the noted directories on the report server and not the local machine.

sql server directory

window

Once successful, we are at last ready to finally use the custom assembly in a report. Thus, you can open SQL Server Data Tools, SSDT, and create a new SSRS project and report. Add a data source and data set.  If you are not familiar with creating a new report, please see the following tips:

In the below example, we created a dataset based on the SalesOrderDetail table in the Adventure Works database; after that add a tablix to report and add several fields to the tablix including the UnitPrice field. Now we want to use the function in the custom code assembly, but in order to do so we must add a reference to the dll in the report properties. From the menu bar, Select Report, then Properties as shown next.

Reprot Properties

As illustrated below, select the Reference Window, and click the Add button. Next click on the ellipse button.

Ref Add

Add Elipse

Now, click on the Browse tab and then navigate to the <program files>....Reporting Services > Report Server > bin directory and find the newly created custom assembly, as noted below. Then click OK and OK again.

SSRS Custom

Finally we are ready to implement the function in an expression. In order to reference a function in the assembly, we must use the following syntax:  =AssemblyName.ClassName.FunctionName(arguments). For our example, the syntax is:  =SSRSCustomAssembly.ColorClass.COLORNUMBER(Fields!ModifiedUnitPrice.Value). Thus for the Modified Unit Price field, we are adding the noted expression to the Font Color property as shown below. Thus, if the Modified Unit Price is less than zero, the font color will be red; otherwise the font color will be blue.

Font Color

This expression results in the following report, which is partially shown below.  Notice that the positive numbers are blue and the negative numbers are red.

Report Results

We are now free to use this function within this report or other reports as long as we add the appropriate reference to the assembly. We could certainly add additional functions to this same dll which would be handy for complicated calculations or formatting across a range of reports.

Conclusion

Custom assemblies in SSRS allow for report developers to program code using a DotNet language within a separate object from the SSRS report itself. The coding can be completed in Visual Basic or C and allows for consistent code reuse and simplified maintenance of standard code across multiple reports and projects. However, the process of implementing and deploying the code is rather complicated with required changes to the AssemblyInfo file along with required signing of the project. Once these steps are completed, the dll file must be deployed to the report server bin directory along with the windows\assembly directory on the reports. Finally, in the report itself, a reference must be added for the assembly, and then at last the assembly functions can be used and referenced within the report.

Next Steps


Last Updated: 2014-05-08


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 12, 2017 - 6:29:38 PM - Nkabirwa Back To Top

 

 Im un LUCKY ive tried to follow it up on the dot, but i could not run the report it flags off errors

the definition of the report alevelarts.rdlc is not valid  .. details: could not load the file or assembly SSRCustomAssembly .. the system can not find the file specified!!

 

Ive been trying for the last one week, and getting the same message, where im i going wrong?


Wednesday, April 06, 2016 - 3:25:52 AM - Gourav Verma Back To Top

 

very simple , easy to understand and useful 


Wednesday, May 21, 2014 - 7:26:08 AM - Scott Back To Top

Roham Hossein: Not sure exactly what you are asking.  The process of registering the assembly for prior versions of SSRS is different but easily found on the web.


Tuesday, May 20, 2014 - 5:46:59 PM - Roham Hossein Back To Top

I am planning to use a custom assembley in my ssrs, but for SSRS reports we are using vs 2008 and the other .net assemblies using vs 2010. I am not sure how to register the assmbley with newer version (using framework 4.0) and refence in 3.5 framework reports. Thank you in advance


Thursday, May 08, 2014 - 6:15:38 AM - Girijesh Pandey Back To Top

Nice article, can be used extensively in SSRS.

 

same i did in my previous project in SSRS 2008 R2.


Learn more about SQL Server tools