Custom Assemblies in SQL SERVER Reporting Services(SSRS)

Custom Assemblies in SQL SERVER Reporting Services(SSRS)

Create custom assembly


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

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

In the Name field enter "rsCustomAssembly"

In the Location field enter "C:\Visual Studio 2008\Projects"

In the Solution field enter "rsCustomAssembly"

Click OK when you are done

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

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

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

{

///

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

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

///

///

///

///

public static string Concat(string str1, string str2)

{

return str str1+ " " + str2;

}



public static string HelloReportingServices()

{

return "Reporting Services";

}

}

}

Your screen should look similar



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

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

The Add New Item window will open



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.

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.

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

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.

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

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:

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



SQL SERVER Reporting Services: Create and Call a Custom Assembly



namespace MyAssembly

{

public class Colors

{

public string PercentageToColor(double percentage)

{

string returnValue = string.Empty;



if (percentage < 20)

{

returnValue = "red";

}

else if (percentage < 80)

{

returnValue = "blue";

}

else

{

returnValue = "green";

}



return returnValue;

}

}

}

Once you’ve compiled your class you need to copy the resulting assembly to the directory in which it is accessible from within your report:

· To use it in the report designer, you need to copy it to: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

· To make it available for reports that have been deployed to the report server, you need to copy it to: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin



Before you can access your custom assembly you have to reference the assembly: Open the report properties (Menu: Report – Report Properties) and select the References tab.

Browse to your assembly and define a Class Name and an Instance Name. (The Class Name and the Instance Name or only for non-static methods). Make sure to prefix your class name with the assembly name.

Now you can call the methods in your assembly from your report, using an expression:

· To call a static method: =..

· To call an instance method: =Code..

So in our example this would be: =Code.TestColor.PercentageToColor(Fields!Percentage.Value)

That's it! You've just created and called a custom assembly from your report...

In one of my future articles I will explain how to pass parameters to the class constructor and I will also tell something about SSRS and .Net Permissions.

Source: ssrstips