SSRS Importing an Assembly

In previous posts I provided VB.NET code to convert names to proper case and provide a “NOT LIKE” expression in reporting services. I recently had a request on how this code could be made re-usable as an assembly in reporting services.

The steps for creating an assembly are pretty straight forward. From Visual Studio create a Visual Basic Class library:

I have renamed the class to ProperCaseClass and included the two functions:


Public Class ProperCaseClass
    Public Shared Function propercase(ByVal str As String) As String
        Return StrConv(str, vbProperCase)
    End Function
    Public Shared Function NotLike(ByVal val As String, ByVal filter As String) As Boolean
        If val.Contains(filter) Then
            Return False
        Else
            Return True
        End If
    End Function
End Class

Once complete then build the project:

After building the project copy the ProperCaseClass.dll to the C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies folder, this enables you to use the assemblies within BIDS.

To utilize the assembly open up BIDS and create a new Report Server Project:

After creating the project right click the Reports folder in the solution explorer and select Add>New Item

Select Report and accept the default name, Report1.rdl:

In order to utilize the assembly you must add a reference to it from the Report>Report Properties menu:

From the Report Properties choose References and click the Add button and click the ellipse button to the right of the first row, the button with the “…”:

From the Add Reference window select the Browse tab and navigate to the C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies folder and select the ProperCaseClass.dll and click Ok:

Back in the Report Properties window click the Add button to Add or remove classes and in the Class Name column type ProperCaseCass.ProperCaseClass and in the Instance name type propercase:

Since all reports must have a data source and data set create a data source to an instance of SQL, in this case I am using the localhost default instance and AdventureWorks2008:

In the data set I am using the query SELECT GETDATE() since I will be using string literals in my expressions to display using the reference the data set is only necessary to preview the report:

Drag and drop a table on the design surface and in the second data column right click and choose Expression:

In the expression type =ProperCaseClass.ProperCaseClass.propercase("UPPER") which will call the propercase function:

In the details row in the third column right click and again choose Expression. In the Expression builder type =ProperCaseClass.ProperCaseClass.NotLike("UPPER", "UPPR") which will call the NotLike function:

Click OK and preview the report:

The second column in the details has now put “UPPER” into proper case and the third column shows “True” indicating that the values "UPPER" and "UPPR" are not like.

The above process has made the assembly available for BIDS, but not to the report server. In order to assure that the report will render without error the ProperCaseClass.dll must be copied to the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin, or the appropriate folder based on the version of SSRS.

Once the file has been copied then you need to modify the rssrvpolicy.config file to apply the appropriate permissions to the assembly. The rssrvpolicy.config file is located in the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer folder. MSDN goes more in depth into code access security in reporting services here. In this case I added the following code group in the rssrvpolicy.config file:


<CodeGroup
   class="FirstMatchCodeGroup"
   version="1"
   PermissionSetName="FullTrust"
   Name="ProperCaseClass"
   Description="A special code group for my custom assembly.">
   <IMembershipCondition
   class="UrlMembershipCondition"
   version="1"
   Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL08R2\Reporting Services\ReportServer\bin\ProperCaseClass.dll"/>
</CodeGroup>

Once the code group is added you can deploy the report to the report server.