BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
Posted by Andy Brown on 03 August 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Create and Deploy the Code DLL
When you've written one or more C# or VB functions, you still have a bit of work to do. At the moment the code can't be accessed by SSRS:
For now, the code hasn't been compiled into a DLL, or Dynamic Link Library.
The first thing to do is to build the project; then we can copy the DLL over to your development or live server. These steps are explained below.
Building a Project
When you build your class library, Visual Studio will check that your code makes sense, and create something called a DLL file (standing for Dynamic Link Library) for it. First right-click on your code project and choose to build it:
Right-click on the code project, and choose to build it as shown here.
Keep your eye on the Output window (usually at the bottom of your screen) and you'll see that Visual Studio:
- Saves any unsaved files.
- Checks the code to check that there aren't any syntax errors.
- Creates a machine-readable DLL file from the code.
If Visual Studio finds any errors in your code, it will abort the above process:
Here I've made one error (I missed out the semi-colon at the end of a line of C# code). I can double-click on the error message to take me to the offending line.
If all goes well, however, you'll see something like this:
A sign that all is well - Visual Studio has created the DLL file needed.
You can now copy this DLL file to your test or live reports server, as shown below.
Finding the DLL File to Copy
The easiest, and safest, way to find the DLL file to copy is as follows:
Right-click on the project you've just built in Solution Explorer, and choose the option shown.
You can now expand this folder to show its bin subfolder:
By default, this is where you'll find the DLL just created.
When you've found the DLL, you can copy it:
Right-click on the DLL file to copy it (ignore the pdb file - this is not important).
Pasting the DLL File to your Test or Live Site
You now have to copy the file that you've just copied to where your reports are. The location will depend on how many versions of SQL Server you have installed, and whether you have any named instances.
Each named instance of SQL Server is, in effect, a completely separate copy of the software, running independently of all other named instances.
On my laptop, at the time of writing, the folder location for my development work is:
The folder location for the "live" site for my laptop is:
These folder locations will probably be similar on your computer, but are unlikely to be the same:
Here I've copied the VB and C# versions of the functions, creating two DLLs. You would typically only create one!
When you've copied the DLL files to the relevant folders, you'll be able to reference and use them within your reports, as shown in the next part of this blog!