Introduction

Creating and Deploying Reporting Services using Custom Assembly ( step by step )

Well, it was really a night mare to get it done. I tried too many ways but could get it done. While browsing internet I read an article (here) written by Rod Paddock and though to do exactly the same as he did and it work. Here is the step by step detail.

Background

Context: In reporting services using Visual Studio 2003 there was a problem that i had to fetch colours from database based upon some parameter values and i had to do this in the expression of the background property of a report cell. So I use Custom Assembly to fetch colour from database.

Using the code

Steps to create Custom Assembly:

1. Create a "Class Library" Project. (It must be a class library otherwise you will face problem.)
2. Create Shared public function. (This is a function which we will use to fetch colour from report.)
3. Create another method which will connect to the database and will fetch colour from the table.
4. At the beginning of this method ( which will connect to the database) write following code.
Dim permission As SqlClientPermission = New SqlClientPermission(PermissionState.Unrestricted)
permission.Assert()

Screenshot - Untitled.jpg

5. To use this code you will have to add following classes
Imports System.Data.SqlClient
Imports System.Security.Permissions
6. Now Add in your AssemplyInfo.vb class.
7. To use this you will have to import "Imports System.Security"
8. Compile it and your custom assembly is ready to use in Reporting Services

Steps to Add Custom Assembly Into Report (Reporting Services) in Visual Studio 2003:

1. Add reference of the custom library DLL into your report. You will have to add reference in each report one by one in which you are going to use custom assembly DLL (custom assembly = DLL).
2. To add reference go to Report->Report Properties ->References->(browse the button and select the DLL)->OK->OK
3. No need to add Instance name because you are going to use Shared function of your custom assembly.
4. In Expression add: =reportHelperLib.rptHelpers.FilterColours(Fields!DkHighStageOrderNo.Value)
5. reportHelperLib is the Project Name of custom library
6. rptHelpers is the Class name of custom library
7. FilterColours is the Shared Function name of custom library.
8. Fields!DkHighStageOrderNo.Value is the parameter value, using which you want to fetch colour from database.

Steps to make custom assembly run under your Reporting Services in Visual Studio

1. Close Visual Studio 2003.
2. Copy and paste custom assembly DLL into the following path
3. C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE\PrivateAssemblies ( It can be change according to your visual studio installation)
4. Open Visual Studio 2003 again with your project
5. Build it. It should build correctly without any error.
6. Once it done you can see your report displaying different colours which are being fetched by custom assembly

Steps to Deploy Reports and Custom Assembly in Reporting Server

1. Copy custom assembly DLL and paste it in the following folder
2. C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin ( can be change according to the installation)
3. In your visual studio 2003 goto Project[Project name] Properties
4. Under the deployment do the following
5. Overwrite Datasource = True
6. TargetFolder = (give the folder name under which you want to deploy reports)
7. TargetServerURL = (your report server url)
8. After complete press OK
9. Now goto Build Deploy Solution
10. It will deploy reports in reporting server.
11. Go to the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer 12. First backup the file "rssrvpolicy.config"
13. Now open rssrvpolicy.config and under CodeGroup find the last group
14. After that last group add following code

Screenshot - CodeGroup.jpg
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="reportHelperLibSample"
Description="reportHelperLib. ">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\reportHelperLib.dll"
/>
</CodeGroup>

15. No need to change anything but just three places Name, Description and Url
16. Change the Name according to your requirement
17. Change the description whatever suit you
18. Give your Custom Assembly path + name here. Give the path same as above step 2 where you copy the custom assembly DLL (C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin)
19. RESTART computer
20. Run Report Server and check the report it should run perfect.
21. If you do not have access to deploy reports on reporting server or if you have to deploy these reports on client machine then in this scenario there will be only one change that rather deploying your reports from visual studio 2003 you will manually go to the report server and will upload the reports into the specific folder. Rest everything will be the same.1

Ali Faraz
Software Developer
Festival Business Solutions
United Kingdom

Points of Interest

Pleas do not forget to write comments that how can I imporve this article. There may have few things which i have overlook and if you will write comment, i will update my article accordingly and it will be helpful for other readers. Thanks.

History

Ali Faraz is a software developer in Festival Business Solutions and having 8 years of experience using .net, sql server etc.