Customized VSTS/TFS Reporting Part I - Configuring the Development Environment

As I mentioned in my previous post, I've decided to spend a bit of time here discussing custom reporting for VSTS and TFS. (Please feel free to leave me a comment here if there's anything in particular that you'd like to see discussed)

This post will primarily cover the basics of what you need and how to set it all up. Keep in mind that there are many different paths to get to where you need to be to develop reports, but I am going to detail what I would call the "Happy Path" (in my opinion, the easiest way to get to where you need to be). There's a ton of information available out there on MSDN and other places that discuss this, but I'm not sure there's any one concise listing of what you really need.

So, let's get started. The first thing we need to do is discuss the components that we'll need. Since we will be working with both relational (SQL Engine) and OLAP (Analysis Services) databases, we'll need to ensure that we have client tools for both. Of course the easiest way to get these is to install a local copy of SQL Server, but we'll stick with the more common client side components. I am also going to assume that you have Visual Studio Team System available to you. In my case, I'll use Visual Studio 2005, since we'll be talking to an instance of SQL Server 2005 for the purposes of this discussion. So, with those disclaimers out of the way, here's a list of the components you need:

• SQL Server 2005 Client Tools - When you don't have a local copy of SQL Server installed, the easiest way to get these is through the SQL Server 2005 feature Pack, which can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en (You'll need a few components from this list)
• SQL Server Native Client - Allows you to communicate with SQL Server Relational Databases
• SQL Server 2005 Analysis Services 9.0 OLEDB Provider - Allows you to communicate with SQL Server OLAP cubes
• Core XML Services (MSXML) 6.0 - Adds interfaces for various XML activities (Behind the scenes everything you do in SSRS will be XML)
• Business Intelligence Development Studio - The easiest way to get this is through the SQLExpress Toolkit, which can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=e8ad606a-0960-4efd-8bd7-b21370c7be2b&DisplayLang=en Even though BIDS is just a slimmed down version of Visual Studio (It's actual SKU is "Visual Studio Premium Partner Edition") you still need to install this, because you need the designers (Project templates) that are included.

One thing to note here, if you do not have a local instance of SQL Server, you may want to go ahead and download/install the Management Studio Express Edition that is packaged with the SQLExpress Toolkit.

• SQL Server Books Online - You don't *really* need this, but I think you'll be happier if you have the documentation handy while  you're working on this. BOL can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en 

Another disclaimer here. The links I'm posting are always subject to change without notice, and more importantly, the components themselves tend to go through revisions on a regular basis. It might be worth your while to search download.microsoft.com for the items in question before simply relying on my links.

Ok, so once we have the components downloaded and available, you'll want to install things in the following order (I'm assuming that you already have Visual Studio loaded on your system.. By the way, you can accomplish everything I am doing in this series of posts without the full blown Visual Studio, but I imagine that doesn't really apply here since we are talking about Visual Studio Team System customized reporting):

1. Core XML Services 6.0
2. SQL Native Client (Install the appropriate version for your platform, x86 or x64)
3. SQL 2005 Analysis Services 9.0 OLEDB Provider
4. Business Intelligence Development Studio (BIDS)
5. SQL Server Management Studio (SSMS)

Once you get everything installed, you'll know that you have success when you start Visual Studio and see the additional "Business Intelligence" project types as shown here:

Another way you can make sure you have the correct connectivity is to start SQL Server Management Studio and connect both the database engine and Analysis Services to the machine containing your TFS Database as shown below:

Once we have connectivity verified, we can get down to the business of creating a simple report.

I'll detail that in the next article in this series. Again, please feel free to post a comment here if there's anything in particular that you'd like to see discussed.