SQL Server Reporting Services (SSRS)– Call a DLL assembly

1) Create an external Assembly DLL (Class Library). You can use Framework 3.5.

a) Name it ‘DataForSSRSReport’.
b) Rename the default class to ‘clsDataForSSRS’. Make sure the class is Public.
c) Use ‘Private Shared’, ‘Public Shared Property’, and ‘Public Shared Function’

Public Class clsDataForSSRS
    Private Shared mGettysburgAddress As String = String.Empty

    Public Shared Property GettysburgAddress()
        Get
            GettysburgAddress = mGettysburgAddress
        End Get
        Set(ByVal value)
            value = mGettysburgAddress
        End Set
    End Property

    Public Shared Function GetGettysburgAddress() As String
        GetGettysburgAddress = String.Empty
        Try
           mGettysburgAddress = "Four score and seven years ago our fathers brought forth on this continent, a new nation, conceived in Liberty, and dedicated to the proposition that all men are created equal." & vbCrLf _
                     & vbCrLf & "Now we are engaged in a great civil war, testing whether that nation, or any nation so conceived and so dedicated, can long endure. We are met on a great battle-field of that war. We have come to dedicate a portion of that field, as a final resting place for those who here gave their lives that that nation might live. It is altogether fitting and proper that we should do this." & vbCrLf _
                     & vbCrLf & "But, in a larger sense, we can not dedicate -- we can not consecrate -- we can not hallow -- this ground. The brave men, living and dead, who struggled here, have consecrated it, far above our poor power to add or detract." & vbCrLf _
                     & vbCrLf & "The world will little note, nor long remember what we say here, but it can never forget what they did here. It is for us the living, rather, to be dedicated here to the unfinished work which they who fought here have thus far so nobly advanced. It is rather for us to be here dedicated to the great task remaining before us -- that from these honored dead we take increased devotion to that cause for which they gave the last full measure of devotion -- that we here highly resolve that these dead shall not have died in vain -- that this nation, under God, shall have a new birth of freedom -- and that government of the people, by the people, for the people, shall not perish from the earth." & vbCrLf _
                     & vbCrLf & "-- Abraham Lincoln" & vbCrLf _
                     & "Nov. 19, 1863"

           GetGettysburgAddress = GettysburgAddress
        Catch ex As Exception
         'Error has occurred
        End Try

        Return GetGettysburgAddress
    End Function
End Class

2) Setup a strong name to sign or version the assembly. Do not password protect.

B1 B2

3) Add an import to System.Security and provide the proper permissions in the
AssemblyInfo.vb file.

B3

4) Build the assembly.

5) Copy the DLL you created, ‘DataForSSRSReport.dll’, to the following folder:

C:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServerbin

6) Copy the DLL you created, ‘DataForSSRSReport.dll’,  into the GAC by creating and executing a bat file with the following code:

CD 
CD "C:Program FilesMicrosoft SDKsWindowsv6.0ABin"gacutil.exe -i "C:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServerbinDataForSSRSReport.dll"
PAUSE 'Any key to finish'

B4

The DLL is now in the GAC and can be viewed 2 ways:

a)  GAC Browser
http://sourceforge.net/projects/gacbrowser/

B5

b)  c:Windowsassembly

B6

7) Using the Business Intelligence Development Studio add-on, create an SSRS report (RDL). Place a textbox on the report that will be populated with data from the DLL you created, ‘DataForSSRSReport.dll’.

B7

8) Right click outside the body of the report, and then click on ‘Report Properties’. In the ‘Report Properties’ dialog box, click ‘References’. Click the ‘Add’ button. In the ‘Add Reference’ dialog box, click the ‘Browse’ tab. Navigate to and select the DLL you created, ‘DataForSSRSReport.dll’, then click the ‘OK’ button. Finally, click the ‘OK’ button on the ‘Report Properties’ dialog box.

Note:

SSRS will actually use the GAC to get the DLL.

B8 B9

9) Add the following expression to the textbox of the report to get the data
from the DLL.

=DataForSSRSReport.clsDataForSSRS.GetGettysburgAddress

Note:

DataForSSRSReport.dll is the DLL (namespace)
clsDataForSSRS is the name of the static (shared) class
GetGettysburgAddress is the function that returns the data

10) Run the report.

B10

Note:

Any time a change is made to the DLL, repeat steps 4 through 6, and then you must close and reopen the Business Intelligence Development Studio to test it again locally.

Please follow and like us: