Calling a .Net Assembly from a SQL Server 2005 Reporting Services report?

Question

I've currently got a set of reports with a number of common functions sitting in code blocks within the .rdl files. This obviously presents a maintainability issue and I as wondering if anyone knew a way for these different reports to share a library of common code?

Ideally I'd like to have a .Net Assembly attached to my Reporting Services project, which all of my reports can access and call functions from. This would save the headache of trying to update and redeploy about 100 reports every time a change needs to be made to a common function.

Any suggestions?

Answer 1

You must deploy to the GAC. http://www.developerdotstar.com/community/node/333

Answer 2

The following article lists just about all the different ways of calling .Net code from an SSRS report. Extending Microsoft SQL Server 2000 Reporting Services with Custom Code

If all these reports run against the same server, another option to consider would be to use .Net stored procedures in the database to hold your code.

Answer 3

From within Visual Studio in the properties of the report, on the 'References' tab add the details for the assembly that contains the managed code. This code can be called from expressions within reports using the instance name that is specified.

This assembly can either be stored in the GAC or the PrivateAssemblies directory of Visual Studio, and be deployed to the Report Service 'bin' directory on the Reporting Services server. For more information refer to How to use custom assemblies or embedded code in Reporting Services

Answer 4

Many thanks guys, I can now call my assembly from my reports.

Supplementary question: Is there a namespace I can include when I'm creating my assembly that makes it aware of objects in the report designer such as fields and parameters? It'd be really great if I could pass, say, a collection of fields in a strongly-typed way to my assembly.

And the answer: A couple of hours of searching reveals that adding \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Microsoft.ReportingServices.ProcessingObjectModel.dll as a reference in my assembly allows me to access the various Reporting Services types, such as Fields and Parameters. Note that in Reporting Services 2008, the namespace changes.

Answer 5

I had a lot of pain with this so I hope this helps someone. You can get it from the MSDN article but there are a few points below that I think can help speed someone through this a little faster.

Don't forget to add this to your rssrvpolicy.config file:

<CodeGroup class="UnionCodeGroup"
   version="1"
   PermissionSetName="FullTrust"
   Name="MyCodeGroup"
   Description="Code group for my data processing extension">
      <IMembershipCondition class="UrlMembershipCondition"
         version="1"
         Url="C:\pathtocustomassembly\customassembly.dll"
       />
</CodeGroup>

I forgot to do this and I was hating it for awhile.

Plus don't forget to hit both of the following folders for 2005 with your new dll:

Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies 
Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

Plus don't use log4net with your assembly. I couldn't make it work. Maybe someone can but not me.

Plus if you mess up like I did you won't be able to delete the files until you close Visual Studio.

Plus make your methods shared or static. It's easier.

Create a deployment batch file. Something like:

@ECHO OFF
REM   Name: SRSDeploy_Local.bat
REM
REM   This batch files copies my custom assembly to my Reporting Services folders.
REM
REM   This is the SQL Server 2005 version:
copy "C:\Projects\Common\lib\SCI.Common.SSRSUtils.dll" "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies"
copy "C:\Projects\Common\lib\SCI.Common.SSRSUtils.dll" "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin"

Finally, build your report before previewing.

If it builds you're likely on your way.

Except...

You can't deploy it to your production report server because you'll always get the following error:

Error while loading code module

Which is what I'm working on right now.