How to write custom code in SQL Reporting Services 2005 in 3 easy steps

Have you ever written a report in Reporting Services 2005? Most people have, but to get anything worth it's salt you need to have some pretty advanced t-sql experience or have a good background in databases to be productive in SQL Reporting Services. For those of you that are experienced in procedural language like C#.NET or VB.NET but are not in t-sql this article is for you. In this post I am going to show you a quick and dirty way to get some advanced custom code working in your reports.

First off, you need to know what your options are before you get started:

1. You can build custom assemblies in any .NET compliant language of your choice
   
2. You can embed Visual Basic code into your report directly
   

This article is going to discuss the 2nd option because it easier and quicker to get your custom code working than the 1st option. Don't get me wrong there are good reasons for both and it is better to know when to use one over the other in certain situations. 

Now that we know what we are trying to achieve, lets dig into to some code!

Step 1: Build a report 

 -- Build the dataset

-- Build the report layout

Step 2 : Add custom code to your report to do something useful

-Click on Report > Report Properties

-Click the Code tab

This is where you will put your custom code and reference in your report. Some things to take note of:

• All code that is in this block must be Visual Basic .NET code
• All code that gets executed here runs with full-trust permissions when using 'Preview'
• In order to get custom authenication hops , i.e. like database calls you must add the required assemblies and required permissions
• When performing database calls, web services calls, you need to set the trust level on the web.config file on the Report Server
• Also any additional libraries that you use, i.e. System.Data.SqlClient must be referenced explictly in the references tab. 
• Any code that you use here can be directly used anywhere in your reports via the '=Code.Method()' syntax. If you are just building functions this will work, however if you put the class in the code you will need to reference the class like Code.ClassName.MethodName() in order to access it. 

Step 3 : Write some Visual Basic.NET code

 - I prefer to write the code in Visual Studio in a class library project so I can get the benefit of intellisense and complie-time checking. Once I have what I need, I copy it into the code tab of the Report>Report Properties window like so.

--Visual Studio Code Window

-- Copy the code into the code tab

-- Place code into your report wherever you like

-- Run your report to see what your logic returns

Conclusion

To recap what we did was pretty basic in terms of complexity, but offers us the power of the .NET Framework in our reports. Because reporting services utilizes an expression-based processing engine you can make many things dynamic throughout your report. I think that personally this is the best way to add custom logic that you could not do otherwise. However, we could have hard-coded this logic in an iif() statement in the report and that would have worked as well but the point I am trying to make is that we can utilize the .NET Framework class libraries to help us write better reports faster and more efficiently.  

Josh Clark

If you have any comments or questions, please comment on this blog or send me an email at 

jclark434175@gmail.com

Speaking at Cleveland C#/VB.NET User Group

I am just recovering from giving a talk at the Cleveland C#/VB.NET User Group in Cleveland, OH yesterday. My talk was about LINQ and it's various flavors including LINQ to Objects, LINQ to SQL, LINQ to XML, and LINQ to Entities. However, my talk was specifically about the first two. To be honest, each of the different subsets could take a whole presentation on own because there just so much to cover in a relatively short amount of time. Overall, I would say that the majority of the people that came out really enjoyed the presentation and could take something away from in the end.

For all of you who subscribe to my blog, the links to the sample code and slide decks are listed below.

Click http://cid-e53966b765831664.skydrive.live.com/browse.aspx/Powerpoint%20Slides%200%20Code?uc=2 to get access to my sample code and slide decks.

Lansing Day of .NET

After attending the first annual Lansing Day of .NET in Lansing, Michigan, I was impressed with the sessions and the content. There were several sessions consisting of the ASP.NET MVC Framework, LINQ to SQL, Test-driven development, Agile Project Management, Structuring your solutions in Visual Studio, Introduction to WPF, Getting Started with WCF and many more. I think the conference was a big success in terms of turn out and by the content that was presented.

How to Disable the Back button...

In my queries against the Internet in hopes of finding a clean way of keeping users from using the back button in my application I have found a simple way to go about this. Let me first start off with the problem at hand. Many of us writing code for the web experience this all the time with users using the backward and forward buttons of their browsers when testing and working with our applications. So, say for example we have some pages that we don't want the user to be able to hit the back button and go to because if they do our code will possibly break, blow up, or insert another record. All of these and many others are not good.

So lets look at some of the obvious examples to fix this problem. One possibility is to close the current window and open another window that does not have the toolbar enabled. That may work for some, but is not the best approach. Another approach out there is to clear the browser's cache and force the client to request the web page again. This works just fine but you will also need to make use of session variables to keep track of whether or not the user has already visited this page in the current session. Another method that I find pretty easy and straight-forward to use is with some simple javascript. Here is an example: (Blogger doesn't like html or body tags in their posts, so be creative.)

"html"
"body onload="history.go(1)
"body
"html

From this example any web page that you include this javascript in will not allow the browser to ever return to the previous page. Basically, what is happening is that the javascript is telling the browser when the page loads to go forward 1 page in the history collection. Pretty neat, huh? This works well for scenarios where you want the user to navigate around your site using links and navigation elements. This method is actually better in some cases in that it will use the cached page instead of going back to the server. One thing to note is that there is no real way of disabling the browser's buttons. If anyone has any other cool ways of doing this please respond to this post.

How to perform deletes with LINQ to SQL

When I first started working with LINQ to SQL I was very impressed by how easy it was to delete records from the db with the new API's. The cool thing is that when you need delete records that have child records the way you go about it is straight-forward and object-oriented. For example, say you have a Customers with Orders which would constitute a foreign-key relationship, if you needed to delete that Customer you would have to delete their records first or you would violate referential integrity rules. With this example being pretty straight-forward let's look at some code examples of this.

C# Example:
DataContext = new DataContext("server=localhost;database=MyDb");

Customer c = ctx.Customers.Single(o => o.CustomerId == "12345");

//Customers has a entity set on it called Orders which has a collection of Orders for that
//Customer
foreach(Order o in c.Orders)

{

//Do something with order object o...
}


ctx.Customers.DeleteOnSubmit(c);
ctx.SubmitChanges();
^^^^^^
//this would cause a run time error because there are child //records present

So, what we need to do is delete the customers orders first and then delete the customer record.Now, as one might expect you can do this a number of ways. Also, one might say that if you were doing this with stored procedures or in-line sql that you would have to delete the child records first and then delete the parent record. Well, this is not neccessarily true with LINQ to SQL. With our datacontext remembering all of objects and their relationships, we can delete the records in any order we want because the datacontext will know what needs to be deleted first because of the FK relationship. Pretty cool, huh? I will show how we can accomplish this below.

DataContext = new DataContext("server=localhost;database=MyDb");

Customer c = ctx.Customers.Single(o => o.CustomerId == "12345");

//You can do it this way...
c.Orders.Clear(); <---secretly sets values in the FK field to NULL for this customer...
ctx.Customers.DeleteOnSubmit(c);

ctx.SubmitChanges();

//Or you can do it this way...
ctx.Customers.DeleteOnSubmit(c);
c.Orders.Clear(); <---secretly sets values in the FK field to NULL for this customer...

ctx.SubmitChanges();

It does not matter which order we the code is processed because when the SubmitChanges() method executes the data context will take care of the FK relationship for us. I was a little curious about what LINQ to SQL what doing under the covers to achieve this. So what I did was to open a trace and monitor the generated SQL from the .NET Framework. By examing the trace generated from SQL Profiler you can see that what is really happening behind the scenes is that LINQ to SQL is going the Customer's orders and setting the value of the foreign-key field to a NULL value. Then, the datacontext issues the delete statement for that customer.

By allowing us to work with objects and collections instead of datasets and datatables performing deletes on parent and child data is relativily simple. If you have any questions about the code, you can comment on this post or send me an email at jclark434175@gmail.com

Sharepoint Webcasts for ASP.NET Developers

Anyone that's interested in learning some useful information about SharePoint you should tune into these MSDN web casts. I know from watching Andrew Connell's session's at a past devconference that he is an awesome presenter and a SharePoint genius. Check it out!

They are every Tuesday & Wednesday form 12p-1pm(GMT -0500) starting next week for the next five weeks. Here's a breakdown of the schedule:

Date (all times EDT)	Topic & Registration URL	Presenter
Tues, May 20 : 12-1p	Web Parts	Rob Bogue
Wed, May 21 : 12-1p	Data Lists	Rob Bogue
Tues, May 27 : 12-1p	Silverlight	Andrew Connell
Wed, May 28 : 12-1p	Event Handlers	Andrew Connell
Tues, June 3 : 12-1p	Site Branding	Andrew Connell
Wed, June 4 : 12-1p	Workflow	Rob Bogue
Tues, June 10 : 12-1p	Web Services	Andrew Connell
Wed, June 11 : 12-1p	Page Navigation	Andrew Connell
Tues, June 17 : 12-1p	User Management	Rob Bogue
Wed, June 18 : 12-1p	Content Types	Rob Bogue

Speaking at the Cleveland Day of .NET

Being that it was my first presentation ever, I was a little nervous about getting up in front of 50 or so other developers and talking about LINQ to SQL. LINQ itself is not that hard to talk about from a 10,000 ft. view but where things start to get blurry is when you try to show people how things work with demos. I had spent most of my time working on getting all the powerpoint material (slides, notes, speaking material) perfect without using notecards or anything like that but what I thought would be a breeze was the code.



Obviously not, I had developed the demos about a week prior and they were all working fine then. However, when I was actually giving the demo I ran into a few problems. My Query Analyzer threw an exeception, my debugger decided to stop working, and I forgot where to go to show everyone how to implment Stored Procedures over manual LINQ code. Anyways, aside from those issues everything else I think went well. I will take what I have learned from this exeperience and make myself better for the next time. I really do enjoy speaking about technology and how it can make our lives more efficient and productive.

You can download the sample code and slides at the link below.
http://cid-e53966b765831664.skydrive.live.com/browse.aspx/Powerpoint%20Slides%20%7C0%20Code



-Josh

The first post...

This is my first post on this blog. I will be posting to this blog rather frequently, about once every week, so subscribe to my blog if you want up-to-date info on what's hot in information technology...