SSRS CUSTOM ASSEMBLIES USING C# OR VB

Part five 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
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 (this article)
Debugging Custom Assemblies

Posted by Andy Brown on 03 August 2012 | 4 comments

Post-Build Events to Help Deployment

Sooner or later (probably sooner) you'll get sick of copying DLL files from your class library to your report server projects, and you'll start wondering why Visual Studio can't copy the files for you automatically. The answer is that it can, if you set up post-build events.

A post-build event is one which is triggered automatically whenever you build a project.

Creating Post-Build Events for C# Class Libraries

If you're coding custom assemblies in C#, first display the properties of your project:

Right-click on the Properties category of the project in Solution Explorer, and choose to open it.

Now choose to create a post-build event:

Choose Build Events.
Click in the post-build event command line.
Click on the Edit Post-build... button.

These steps are shown below:

The numbered steps to follow to start creating post-build events.

Now build up the commands that you want to run:

Start typing a valid command.
Click on the < < Macros button to insert preset folder paths.
Click on the folder path you want to use.

Here are the numbered steps:

The steps to follow to create post-build events.

When you've finished, select OK to review your post-build event command line:

These commands are given in full below.

The commands above are:

Command	What it will do
copy /y "$(TargetPath)" "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies"	Copy the DLL built for the project to the Visual Studio report preview folder on the author's computer.
copy /y "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer\bin"	Copy the DLL built for the project to the deployment folder on the author's computer.

The /y switch means that SSRS will overwrite any files on the target folder.

Creating Post-Build Events for VB Class Libraries

The procedure for Visual Basic is nearly the same. First display the project's properties:

First right click on the VB class library project, and choose to display its properties.

Choose the Compile tab:

Click on this tab to go to post-build events.

You can now go to the project's post-build events:

Click on the button shown at the bottom right of the dialog box.

From this point, you can continue as for C# post-build events.

Things to Watch Out For

There is one thing which catches me out every time!

If you're previewing a report using a custom assembly, you won't be able to build the project (if it's using post-build events to copy the DLL over, that is) until you return to design view for the report.

SSRS CUSTOM ASSEMBLIES USING C# OR VB

Part five 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
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 (this article)
Debugging Custom Assemblies

Comments on this blog

This blog has 4 comments:

Comment added by MikeWood on 23 October 2012 at 10:49 GMT

This is a really useful tutorial. Thanks for explaining it all so carefully. Is is possible to extend this sort of thing to handle events like printing?

Reply from Andy Brown (blog author)

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!

Comment added by MikeWood on 29 October 2012 at 11:31 GMT

I have a report where summaries are shown for different levels of the company in a descending hierarchy: Division, Region, Area, Section. I use the standard + to reveal the lower levels of the hierarchy. What my client requires is the ability to click on a level (say Area) and print the summaries for the Sections within that Area. Is there a way of embedding some code within an expression to pass the correct parameters to a print routine and have the print flow through without further input from the user? Your assistance is greatly appreciated.

Reply from Andy Brown (blog author)

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!

Comment added by krootz on 19 November 2012 at 14:00 GMT

Great tutorial!

is there a way to auto run a code to do something before the report is rendered? Is there an on load event, or in VBA if you name your function autoexec it would run first? We have an app with an Oracle table containing report security levels defined for each user. I would like to use this instead to check if the user can run/view a specific SSRS report or not -- the report can still be rendered I guess but with the visibility property of the information set to false, or possibly redirect to a new report with a static message "not authorized"? Managing the security via the SSRS security would be duplicated effort.

Any ideas? Thanks!

Reply from Andy Brown (blog author)

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!

Comment added by ChromaticRanger on 28 January 2013 at 16:37 GMT

Hi Andy

Many thanks for taking the time to put up this series of posts. Probably the best set of posts on the subject are found so far taking things nice and steady and doing an end to end real world example.

I do still have a problem however, and it might be an area that will make you cry, "oh no, someone has asked THAT question"! ;)

I have successfully created a custom dll along the same complexity as the ones you have shown, however, as soon as I have raised the complexity I've hit a problem that I just cannot seem to get solved.

My custom dll calls into other dll's (namely System.Data.SqlClient classes to get values from a database) and from what I have read so far in other places, this means I need to play around with Code Access Security files because previewing runs your code under full trust, but deployed code runs without full trust and denies the calling of your code.

My custom assembly method (happens to be C#) :

1. Takes an encrypted string.

2. Calls a stored procedure in SQL Server with that string as a parameter.

3. Get result from Stored Procedure which happens to be an encrypted connection string to use in my report.

4. I decrypt this result and return the connection string as the final result of my custom assembly call.

This is all done so I can dynamically switch which database I report against depending on the original value passed to the report.

This all works perfectly in the preview tab. Debugging works fine as well. However, as soon as I deploy to the Report Server and run in Report Manager the report fails with, "Error during processing of the ConnectString expression of the data source" and I think this is because of the full trust issue.

Have you had any success developing a report and getting the full trust configuration files correct so that your solution works on a Report Server?

Any help would be greatly appreciated as I've been pulling my hair out with this.

Kind Regards,

Martin

Reply from Andy Brown (blog author)

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?

SSRS CUSTOM ASSEMBLIES USING C# OR VB

Part five of a six-part series of blogs

Post-Build Events to Help Deployment

Creating Post-Build Events for C# Class Libraries

Creating Post-Build Events for VB Class Libraries

Things to Watch Out For

SSRS CUSTOM ASSEMBLIES USING C# OR VB

Part five of a six-part series of blogs

BLOGS BY TOPIC

BY AUTHOR

BY YEAR