- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
SSRS CUSTOM ASSEMBLIES USING C# OR VB
Part one of a six-part series of blogs
Reporting Services allows you to write functions in C# or Visual Basic, then reference these as custom assemblies. This blog shows how - and why - you might do this.
- Creating Custom Assemblies in Reporting Services (this article)
- Creating a Project, Class and Function in VB or C#
- Create and Deploy the Code DLL
- Referencing and Using Custom Assemblies in Reports
- Post-Build Events to Help Deployment
- Debugging Custom Assemblies
Creating Custom Assemblies in Reporting Services
In SQL Server Reporting Services (SSRS) you have access to a whole range of built-in functions. However, to release the full power of SSRS you'll need to create custom assemblies. This rather long blog will explain how - and why - you might do this!
Our Example - a Proper Case Text Function
Suppose that you frequently want to capitalise a word. SSRS includes functions to convert text to upper and lower case, but nothing to capitalise a word:
The LCase and UCase functions convert text to lower and upper case, but neither capitalises a word.
You could get round this by embedding a VB function in a report, looking something like this:
Embedded code is hard to read, limited to a single report and VB-specific.
However, the best solution is to create a function in VB (or C#), compile this into something called a DLL file and then reference this within your report. This is called a custom assembly.
Advantages of Custom Assemblies
There are a fair few hoops to jump through to create a custom assembly; why would you need one? Here are some reasons:
| Advantages | Notes |
|---|---|
| Portability | Code that you embed within one report is obviously not available for other reports to use, whereas you can reference a custom assembly from any report. |
| Error-checking | When you type code into the Report Properties dialog box there's no way of knowing whether you've made any syntactical mistakes until you try to run it, whereas custom assemblies provide full Intellisense and code formatting. |
| Languages | You can write custom assemblies in either VB or C#. |
| Speed | It's not usually a big issue, but code in a custom assembly can be pre-compiled, and so will run more quickly. |
| Debugging | You can debug code using a custom assembly. |
Following the Rest of this Blog
To create a custom assembly you will need to do the following:
- Create a project, class and function.
- Build, deploy and reference your DLL.
- Use the custom assembly within a report.
- Deploy your project (perhaps automatically, using post-build events).
- Learn how to debug a custom assembly.
Let's begin by creating a project, class and function in Visual Studio.
This blog assumes that you already know basic programming in either VB or C# (examples are given for both languages).
SSRS CUSTOM ASSEMBLIES USING C# OR VB
Part one of a six-part series of blogs
Reporting Services allows you to write functions in C# or Visual Basic, then reference these as custom assemblies. This blog shows how - and why - you might do this.
- Creating Custom Assemblies in Reporting Services (this article)
- Creating a Project, Class and Function in VB or C#
- Create and Deploy the Code DLL
- Referencing and Using Custom Assemblies in Reports
- Post-Build Events to Help Deployment
- Debugging Custom Assemblies
Comments on this blog
This blog has 4 comments:
CAN you handle events like printing? If you add a comment giving a summary of what you mean, or a link to a relevant website, I'll certainly consider this. There's always more to learn about SSRS!
Off the top of my head, I'd say it wasn't possible without adding some JavaScript to your report somehow. However, what you could do is create an action for each area, such that when you clicked on the area it transferred to another report, passing in the area id as a parameter. Your client could then print out this second report. Admittedly that's two mouse clicks, not one.
If anyone can improve on this, please add your penn'orth!
You could create a true/false (Boolean) report variable whose expression would be set to the value of a custom assembly function (which could look at the Oracle database). The value of this variable would be calculated once only, when the report first loads. You could then set the visibility of all relevant parts of the report to be the value of this variable.
Not for the faint-hearted - you'll need to learn about custom assemblies and report variables - but it should work OK!
Hi Martin
Great question - rubbish answer coming up! When I'm training on SQL or SSRS, I always emphasise that there is one area I can't help with: security. I too tear my hair out with problems like this. The only thing I can suggest is copying your question to StackExchange or a similar site. Unless any other readers can help?