posted 12/20/2010 by JeffRush - Views: [3422]
In this blog post I am going to attempt to show how to create a quick and dirty C# class library as a simple way to provide end-user customization across reports.
The sample I will use will be to facilitate Name formatting but this can extended to suit whatever needs you have. For example, we also use it for address formatting, custom encryption/decryption algorithms, .ToUpper/.ToLower functionality and HTML character stripping, to list a few.
The first step will be to launch BIDS and create a Visual C# Class Library Project:
Once that is created, you may modify the information in Assembly.cs if you so wish to set Owner/Version information:
Now that your project is setup it is time to open up your main .cs file and build out the functionality.
I would start by setting up the Region and Class:
Declare your Function:
As you can see in the example, it expects to be passed 9 parameters. The first 8 of these are strings that map to 8 pieces of data we store about an individuals name in the database. The 9th is an integer which will be used to determine the results.
The meat of this function is a simple switch statement that we use to determine how to build out a stringbuilder object.
With these in place, you can see how it constructs the individual's name in the format requested. This becomes particularly useful because it leaves control over the formatting in the end-users hands.
Now that the C# is in place, go ahead and build the .dll:
I would recommend placing the .dll in your ...\Microsoft Visual Studio 9.0\Common7\IDE directory. (location will vary from install to install and based on version)
On the report server itself it should be placed in your ...\Reporting Services\ReportServer\bin directory
Install Note:
When you install this locally, you will have to restart BIDS.When you install this on the server, you will have to restart the Reporting service
Now that the .dll is in place, let's hook it into a report.
Go ahead and create a new .rdl in your reporting project. Once it is created, right click outside of the body => Report Properties => References => Add Assemblies and browse to your .dll:
For the datasets and parameter that power this process I have the following:
@dd_IndividualFullNameFormat_add - this parameter maps uses dsFullName for Value/Label and is passed through to the C# class library.dsMain - The main dataset. This returns all of the components we will need at any given time for any name format and correspond to the 8 string parameters referenced above.dsFullName - This calls a stored procedure which returns all possible name formats and integer values.
The results of dsFullName are:
Back to our .rdl I set a simple table with the following expression:
Please note, the name you call will correspond to your NameSpace.Class.Function
With all of that in place, below are some samples on the selections and output.
With this in place, all we have to do is return those 8 fields in our queries, use the standard expression and stored proc to provide the name format values and we can let anyone running any of our reports determine how they would like names to be formatted at run time.
Needless to say, with > 60k report runs per week across > 40k users adding this layer of run-time customization has been rather useful.
I hope someone finds this useful.
If you have any questions or comments, you know where to find me :)
Wow! Awesome post!
Very helpful post, Jeff. I look forward to reading more from you!
I agree. This is a very informative post. I thought that you would have to add the dll to the GAC though (at least I did when using SSRS 2005). Can't remember and I am on some meds right now from the surgery. :)
Excellent info - thanks for sharing!
Hello JeffRush, It's nice blog ,but while i am trying similar as describe in your blog .I got a error as "The name 'GoesByOrFirst' does not exist in the current context" while debugging.It would be better if you can provide the .dll and database used and as well .rdl of it.So, i can figure out it.
Thanks, Anil Maharjan
Anil: I am afraid I cannot provide the items requested, however you are absolutely right about the error. I did not notice that when I was going through the first time and neglected to include it.
You will need to add one additional function:
private static string GoesByOrFirst(string firstName, string goesByName, bool returnBoth) { if (!returnBoth) { if (goesByName != null && goesByName.Length > 0) return goesByName; else if (firstName != null && firstName.Length > 0) return firstName; } string name = ""; if (firstName != null && firstName != null) { name = firstName; } if (goesByName != null && goesByName.Length > 0) { if (name.Length > 0) { name += " \"" + goesByName + "\""; } else { name = goesByName; } } return name; }
Please add this function and let me know your results.
My apologies for the confusion.
Hello jeff,
All works fine now but after creating some test DB when i create .rdl and put the exp code as you given it shows some error and i could not figure out what's the problem exact so could tell me whats the main things the output trying to say and whats the benefit of working with C# library in SSRS?.
Thanks
Anil
Anil: The benefit that I tried to highlight in the blog post was allowing the end user to determine name formatting at run time so that a single report can satisfy any of the desired name formats.
Can you email me the errors you are experiencing? Jeff.Rush@TheDataYouNeed.com I will try to debug if I can.