business intelligence consulting
 

Reference custom assemblies in SQL Server Reporting Services

by Hans Esquivel 1. October 2012 12:34

For this post we will walk through the steps in creating a custom assembly and referencing it from Reporting Services 2008. After we reference the assembly we will can the call the static (shared) method from a texbox field, using a custom expression.

This article assumes you have the following:

  • SQL Server 2008 and Reporting Services installed
  • Business Intelligence Development Studio (BIDS) for report development
  • Experience with C# and SQL Server Reporting Services

Create custom assembly

1) Open Visual Studio 2008 and create a new Project.

reporting builder 2.0

2) The New Project window will open; Select the Visual C# node; from the right pane select Class Library. Complete the following:

  1. In the Name field enter "rsCustomAssembly"
  2. In the Location field enter "C:\Visual Studio 2008\Projects"
  3. In the Solution field enter "rsCustomAssembly"
  4. Click OK when you are done

reporting builder 2.0

3) Delete the default class.cs file created. When prompted click OK.

reporting builder 2.0

4) Add a new class by right clicking on the solution name and selecting Add >> Class...

reporting builder 2.0

5) Name the new class Utilities.

6) Enter the code below into the class file and save it.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace rsCustomAssembly

{

    public class Utilities

    {

        ///<summary>

        ///  Sample concatenate string method; not intended for production use.

        ///  Note: the string object already provides a concatenate method - string.concat(object o)

        ///</summary>

        ///<param name="string1"></param>

        ///<param name="string2"></param>

        ///<returns></returns>

        public static string Concat(string string1, string string2)

        {

            return string1 + " " + string2;

        }

 

        public static string HelloWorld()

        {

            return "HelloWorld";

        }

    }

}

Your screen should look similar

reporting builder 2.0

7) From the top menubar you will find Build; Click this and select rsCustomAssembly from the list. The bottom status bar should presenet Build succeeded. If not, check your code again to make sure it follows the description above.

Copy the assembly to the Visual Studio Private assemblies and Report Server bin

You must copy you assembly (.dll) to the report designer and report server folders before you can use the assembly in Reporting Services. You can find your assembly by locating the bin folder in your project. For example: C:\Visual Studio 2008\Projects\rsCustomAssembly\rsCustomAssembly\bin\Debug

Note: the location is dependent on your install

For Report Designer: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

For Report Server: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin 

You have completed with this step.

Create a new Report

 This step we will perform the following:

  • Create a new report
  • Reference the custom assembly
  • Add a new textbox to the report designer with a custom expression

1) Open BIDS and create a new project, like you did in 1) under "Create custom assembly".

2) Under the Project Type pane, select Business Intelligence Projects; from within the Templates pane, select Report Server Project and complete the following:

  • For the Name field enter SampleReport
  • For the Location field enter C:\Visual Studio 2008\Projects
  • For the Solution field enter SampleReport
  • Click OK

reporting builder 2.0

3) From the Solution Explorer, right click on the Reports folder and select Add >> New Item

reporting builder 2.0

The Add New Item window will open

reporting builder 2.0

4) From within the Templates pane, select Report and leave the default name of Report1.rdl

5) Click Add

Reference the Assembly

1) From the top menu bar select Report >> Report Properties

Note: if you don't see the Report menu item, try placing your cursor anywhere in the design view; you should then see the menu item.

reporting builder 2.0

2) The Report Properties window opens. From within the left pane, select References.

3) Click Add under "Add or remove assemblies".

4) Click the ellipsis right of the text box. The Add Reference window will open.

reporting builder 2.0

5) Select the Browse tab and navigate to the Report Server bin location, where you copied your .dll, as described earlier in this article under Copy the assembly to the Visual Studio Private assemblies and Report Server bin

you should see a similar value in the reference text box

reporting builder 2.0

6) Click OK to save and close the Report Properties window.

Add Expression and run report

1) Drag and drop a Text Box from the Toolbox pane on the left of your design pane. Strech the text box so you have some room for text.

reporting builder 2.0

2) Right click the text box you just added and select Expression

reporting builder 2.0

3) For the expression value enter :

=rsCustomAssembly.Utilities.Concat("Reporting", "Services")

4) Click OK to save and close the Expression window.

5) To the right of the Design tab, click Preview. You should see the following:

reporting builder 2.0

Save your work. You have completed referencing a custom assembly in SQL Server Reporting Services.

Cheers!

For this post we will walk through the steps in creating a custom assembly and referencing it from Reporting Services 2008. After we reference the assembly we will can the call the static (shared) method from a texbox field, using a custom expression.

Reporting Services

Pingbacks and trackbacks (1)+

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud