Creating and Deploying Reporting Services using Custom Assembly
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.
Context: I use Custom Assembly to fetch colours from database. So I use custom assembly in Background Expression of Report Cell.
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()
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.
Context: I use Custom Assembly to fetch colours from database. So I use custom assembly in Background Expression of Report Cell.
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()
5. To use this code you will have to add following classes
Imports System.Data.SqlClient
Imports System.Security.Permissions
6. Now Add
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
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
Comments