Writing Custom Code in SQL Server Reporting Services
Someone asked me today how to use a Custom Assembly in Reporting Services.
Since I'd never used one before I tried to point them to some useful articles
that I found here on MSDN.
However, the article didn't seem to help much so I decided to look into it
and figure out how to do this since it is an interesting topic.
Let me start by saying that I found the documentation on this to be pretty poor.
The docs (which can be found here) do you give enough information to figure it out, but they leave out the information that
would make the process as simple as it should be. So that is the goal of this article, to show you
how easy it really is to write custom code for SQL Server Reporting Services.
Embedded Code
For our first trick we will write some embedded code. To get started open your browser to this page. On that page
under embedded code you will see the following statement.
To use code within a report, you add a code block to the report. This code block can contain multiple methods. Methods in embedded code must be written in Visual Basic .NET and must be instance based.
I'm assuming that "instance based" means the methods don't have to be shared (that's static for you C# coders). However, in my tests I found you can use either instance based or shared methods.
So let's get started and add some embedded code to a report. Fire up Visual Studio and create a new report project and a new report (Right click the project -> Add New Item -> Report). Next click this link for instuctions to add the embedded code to the report.
To add code to a report
- On the Report menu, click Report Properties.
Note If the Report menu is not available, click within the report design area.
- On the Code tab, in Custom Code, type the code.
So type the code :)
It actually is that easy. So for our example type the following:
Public Function SayHello() as String
Return "Hello from Embedded Code"
End Function
Next add a textbox to the new report put "=Code.SayHello()" without the quotes in the textbox. You should now be able to preview your
report and see "Hello from Embedded Code" on the report. That is as far as we are going to
take embedded code (I also found this article which talks a little
more about embedded code). At this point you should be able to deploy your report to the server and run it on the server.
Custom Assemblies
The next topic is custom assemblies. This is a much more useful feature since you aren't constrained to writing code in VB.Net on a small dialog in the report designer. You can use Visual Studio and your language of choice. So for our example create a new Visual Studio Class Library project in either C# or VB.Net. Call it MyCustomAssembly. Next rename Class1 to SayHello. To the SayHello class we will add one method which is shown below.
[VB]
Public Shared Function Hello() as String
Return "Hello from My Custom Assembly!"
End Function
[C#]
public static string Hello()
{
return "Hello from My Custom Assembly!";
}
Once you're done with the code go ahead and compile the project. Once you've gotten your assembly compiled follow this link for instructions on how to add a reference to your code. Once you've added the reference to your code you will also need to copy the dll file to the C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer folder on your computer. The dll file can be found in your project folder under the bin\debug folder (or bin\release if you compiled a release build).
Note: You only need to add a reference. The Class section will only be used if you're class has instance methods (versus Shared or static methods).
Add another textbox to your report and this time fill it with "=MyCustomAssembly.SayHello.Hello()". You should be able to preview the report and see "Hello from My Custom Assembly" on the report. So you have now used both embedded code and custom assemblies. Before you can deploy the report you need to copy the MyCustomAssembly.dll to the server and put it in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin folder on the server. Once you have copied the dll file you should be able to deploy your report to the server.
But what if you want to actually do something interesting like grab values from a database or use a web service? To do that you will need to modify the Code Access Security (CAS) settings for reporting services.
Code Access Security
There is actually a comprehensive article on this topic here. I would recommend you read that article. There is also a good introduction to CAS in general here and here. The goal of this article (the one you're reading) is to give you a quick-and-easy example that can get you started. So we will attempt to read a value from a file and return it.
First create a text file called hello.txt and put it in your c:\temp folder. In the file add the text "Hello from a text file!" and save and close the file. Next add the following method to your SayHello class:
[VB]
Public Shared Function HelloFromFile() as String
Dim reader as StreamReader = New StreamReader("c:\temp\hello.txt")
Dim hello as String = reader.ReadToEnd()
reader.Close()
Return hello
End Function
[C#]
public static string HelloFromFile()
{
using (StreamReader reader = new StreamReader("c:\\temp\\hello.txt"))
{
return reader.ReadToEnd();
}
}
Note: You will also need to add Imports System.IO in VB and using System.IO in C#.
Next compile you the project. Once the project is compiled you will need to remove the old reference in your report and add the new reference and copy pver the new dll (you will have to close the report project to do this). Add a new textbox to the report and fill it with "=MyCustomAssembly.SayHello.HelloFromFile()". You should be able to preview the report and see the text that you added to the text file.
Next copy the dll and the text file (and put the text file in the c:\temp folder) to the server and deploy the report to the server. When you try to view the report on the server you won't see your message. Instead you should see "#Error".
In order to make this work on the server we need to follow the steps outlined in the CAS article. The first step according to the article is to:
1. Identify the exact permissions that your code needs in order to make the secured call. If this is a method that is part of a .NET Framework library, this information should be included in the method documentation.
Since we are using the StreamReader object we should be able to find this information in the StreamReader documentation but unfortunately I wasn't able to find it (at least I didn't see it). However, I'm going to guess (and I guessed right) it requires the FileIOPermission from the System.Security.Permissions namespace.
Next we need to:
2. Modify the report server policy configuration files in order to grant the custom assembly the required permissions.
So following the example given in the article on CAS we can add the following Xml under the <NamedPermissionSets> node in the rssrvpolicy.config file in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer folder.
Note: Be careful when you edit this file. I would highly recommend making a backup copy of the original file before making any changes. This file is an Xml file and you must be sure that what you add is valid Xml.
<PermissionSet
class="NamedPermissionSet"
version="1"
Name="HelloFilePermissionSet"
Description="A special permission set that grants read access to my hello file.">
<IPermission
class="FileIOPermission"
version="1"
Read="C:\temp\hello.txt"
/>
<IPermission
class="SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
</PermissionSet>
Next we need to add the code group. Again, based on the example in the CAS article we can add the following Xml. We have to put this under the correct CodeGroup node in order for it to work correctly. So you will need to put it under the last code group, but make sure it has the same parent node as the last code group. To do this just insert it before the second to last ending CodeGroup as shown below:
...
/>
</CodeGroup>
[Insert Here]
</CodeGroup>
</CodeGroup>
</PolicyLevel>
...
Here is the code group to add.
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="HelloFilePermissionSet"
Name="MyCustomAssemblyCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\MyCustomAssembly.dll"
/>
</CodeGroup>
Once you've added all this you will still need to complete steps 3 & 4 before the custom assembly will work.
3. Assert the required permissions as part of the method in which the secure call is made. This is required because the custom assembly code that is called by the report server is part of the report expression host assembly which runs with Execution by default. The Execution permission set enables code to run (execute), but not to use protected resources.
4. Mark the custom assembly with the AllowPartiallyTrustedCallersAttribute. This is required because custom assemblies are called from a report expression that is a part of the report expression host assembly, which by default is not granted FullTrust, thus is a 'partially trusted' caller. For more information, see "Using Strong Named Custom Assemblies," earlier in this document.
To complete step 3 you will need to first add using System.Security.Permissions or Imports System.Security.Permissions to your SayHello class. Next you will need to assert the FileIOPermission on the HelloFromFile method as shown below:
[VB]
<FileIOPermissionAttribute(SecurityAction.Assert, Read="c:\temp\hello.txt")> _
Public Shared Function HelloFromFile() as String
...
[C#]
[FileIOPermissionAttribute(SecurityAction.Assert, Read="c:\\temp\\hello.txt")]
public static string HelloFromFile()
...
Next in your assembly attribute file (AssemblyInfo.cs in C# or AssemblyInfo.vb in VB), add the following assembly-level attribute (and add Imports System.Security or using System.Security):
[VB]
<assembly:AllowPartiallyTrustedCallers>
[C#]
[assembly:AllowPartiallyTrustedCallers]
Once you've done this, recompile the assembly and copy it to the server. You should now be able to execute the report and see "Hello from a Text File!" instead of "#Error". If you still see the error need to make sure that you click the refesh button on the report toolbar and you may have to restart the reporting service.
That's it! Hopefully you were able to get everything to work. Now you should have a basic understanding of how to use custom code with reporting services. If you have drop leave a comment and I will try to address it.