Every two weeks we have a knowledge session at Class-A about one or more technical subjects. Last Monday I had to give a presentation about Reporting Services and talked about calculations, parameters, embedded code and custom assemblies in Reports. We discussed about the pros and cons of using code in your report. And when I mentioned the code is a VB-like language a loud discussion started. VB-like languages does exist, but SSRS and SSIS use full blown VB.NET. So why doesn’t everything work full blown. In this blog I try to explain why.
During this session my Virtual PC 2007 gave some strange errors. It looks like I had sticky fingers, because it repeated a character while typing which is very annoying. By the way, I now know this is a known issue at Microsoft. Besides the sticky fingers, my Caps Lock was flipped as well. Maybe this is good moment to introduce my new colleague Pascal Naber who will start in January 2007 and what I think is a complete opposite of my type in IDM (Influencing Descision Makers). I'm an organizer and he's more of controller. He's wants to be in control with all his stuff and he's actually using "Getting things done", to stay one step ahead. Furthermore Pascal has its own casing and thanks to my Caps Lock problem I have my own too. So for this example I'm using mIKEcASING instead of PascalCasing.
Custom .NET code
You may want to add custom code to the report to do more actions than what's provided with the report functions. So it gives you a base for extending your report. You may want to add custom code to implement simple string manipulation task or sophisticated data access manipulation. Code added to Local Report can be one of two types:
- Embedded Code: Added directly inside the report. If you open the RDL file in notepad, you can see the code inside the <Code> tag.
- Custom Assemblies: You can add a reference in external assembly and use functions inside it.
Embedded code is by far the easiest method to implement for two main reasons. First, you simply add the code directly into the report using the report designers UI. Second, this code becomes a segment within the reports RDL file making its deployment simple because it is a part of your report and will be deployed with it. Reporting Services gives you a number of functions to use in a report. For instance there are functions to sum and average numbers, and functions to count values. Visual Basic functions such as InStr and DateAdd are present, as are Iif and Switch. However, the product designers could not predict your every need, and embedded code allows you to implement functions with custom code.
VB-like or VB.NET
You might notice this code is written in VB.NET. All .NET namespaces can be used inside your custom code but be aware that by default only Microsoft.VisualBasic, System, System.Convert, and System.Math namespaces are referenced inside your reporting custom code. Methods in other namespaces, as shown in the example needs to use their fully qualified name.
So why did I think it was a VB-like language?
Code Access Security is in effect in Reporting Services. Since end-users can upload reports with embedded code, it makes perfect sense to protect the server and network from malicious report code. Embedded code executes with the built-in Execute permission by default, which does not allow for much beyond string and math operations on the Reporting Services object model. You would not, for instance, be able to use file or network IO.
If you need additional permissions for your code, you can change the default permission for embedded code but it is not a step to take without very careful thought. Modifying the permissions for embedded code will allow the code in any report to execute with the same permissions. This is another scenario where I would strongly advise using a custom assembly. For more information on code access security in Reporting Services, take a look at Understanding Code Access Security In Reporting Services.
How to use it
The function can be accessed throughout the report as a member of the class called Code. The Code class is instantiated by the report once and its methods can be accessed throughout the report. In place of the expression I could use the following line of code to reference the FormatDateInterval method:
One of the biggest advantages to using embedded code is that you can encapsulate methods required by a report in a single place within the report. The embedded code in the Code window is put inside a special XML element in the report’s RDL file. This means that the code is deployed automatically with the report wherever it goes.
If you prefer C# or another .NET language, you’ll have to access a separate .NET assembly. And keep in mind when using embedded code that the methods can be accessed only by the report that contains them. The embedded code window is merely a large textbox and offers no IntelliSense or built-in debugging features. But for creating quick methods that are intended to be accessed by a single report, the code window is a very simple and effective tool.
If you want multiple reports to use the same code, you should consider accessing a .NET assembly from the embedded code, as I’ll demonstrate in the next part. Security policies prevent the report from performing anything beyond basic operations in embedded code, such as accessing external files, databases, or other resources. Your report can reference .NET assemblies from the References tab in the report properties dialog, but any referenced assembly will, by default, have only Execution permission.
bY tHE wAY i dO nOT pREFER mIKEcASING.