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

Moving the Report Server Databases to Another Computer

You can move the report server databases that are used in a current installation to a SQL Server Database Engine instance that is on a different computer. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving.

Moving a database does not effect scheduled operations that are currently defined for report server items.

Schedules will be recreated the first time that you restart the Report Server service.

SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.

Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.

Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

SQL Server provides several approaches for moving databases, including backup and restore, attach and detach, and copy. Not all approaches are appropriate for relocating an existing database to a new server instance. The approach that you should use to move the report server database will vary depending on your system availability requirements. The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

Important
The steps provided in this topic are recommended when relocating the report server database is the only change you are making to the existing installation. Migrating an entire Reporting Services installation (that is, moving the database and changing the identity of the Report Server Windows service that uses the database) requires connection reconfiguration and an encryption key reset. For more information about migrating a Reporting Services installation, see Migration (Reporting Services).


Detaching and Attaching the Report Server Databases
--------------------------------------------------------------------------------

If you can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use. This approach preserves permissions in the databases. If you are using a SQL Server 2008 database, you must move it to another SQL Server 2008 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.

Use the following steps to move the databases:

1.Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.

2.Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.

3.Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.

4.Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.

5.Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.

6.In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.

7.Right-click the Databases node, and then click Attach.

8.Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.

9.After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database. RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see How to: Create the RSExecRole.

10.Start the Reporting Services Configuration tool and open a connection to the report server.

11.On the Database page, select the new SQL Server instance, and then click Connect.

12.Select the report server database that you just moved, and then click Apply.

13.On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.

14.Restart the Report Server service.

Backing Up and Restoring the Report Server Databases
--------------------------------------------------------------------------------

If you cannot take the report server offline, you can use backup and restore to relocate the report server databases. You should use the ‘Copy_Only’ option. Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups. After you restore the databases, you must configure the report server to use the database on the new server instance. For more information, see the instructions at the end of this topic.

Using BACKUP and COPY_Only to Backup the Report Server Databases
When backing up the databases, set the COPY_OnlyL option and backup type to Full.

Note
Be sure to back up both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to backup a database, see How to: Back Up a Database (SQL Server Management Studio)

Using RESTORE and MOVE to Relocate the Report Server Databases
When restoring the databases, you can Use the RESTORE WITH NORECOVERY argument to perform the initial restore; this keeps the database in a restoring state, giving you time to review log backups to determine which one to restore. You would then repeat the RESTORE operation but use the RESTORE WITH RECOVERY argument.

Note
Be sure to restore both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to restore a database, see How to: Restore a Database Backup (SQL Server Management Studio)

How to Configure the Report Server Database Connection
1.Start the Reporting Services Configuration tool and open a connection to the report server.

2.On the Database page, click Change Database. Click Next.

3.Click Choose an existing report server database. Click Next.

4.Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.

5.In Database Name, select the report server database that you want to use. Click Next.

6.In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.

7.Click Next and then Finish.

Note
A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. If you use alternate approaches (specifically, if you use the rsconfig.exe command prompt utility) to configure the connection, the report server will not be in a working state. You might have to write WMI code to make the report server available. For more information, see Reporting Services WMI Provider.