Customized VSTS/TFS Reporting Part IIa - Using the Report Wizard

In my previous post on this topic, I discussed everything that you need on the client in order to develop customized reports for TFS. Now we'll walk through creating a very simple report that lists each Team Project that hasn't been deleted, and summarizes the WorkItems, state changes and revisons. This report can be used to get a quick snapshot of how active any given project is on your server. This report will use the relational database named TfsWarehouse on your TFS database server.

To begin the process of creating this simple report, start Visual Studio and create a new "Business Intelligence" project using the "Report Server Project Wizard" template as shown below:

Name the project and click OK. This will start the Report Design Wizard as shown below:

 

Click Next, and create a new Shared Data Source named "TFSWareHouseRelational" as shown below:

Click Edit to fill in the name of the SQL Server that hosts your TfsWarehouse database and other required connection information as shown below:

Make sure you test the connection and ensure that the server is available and operational. Click OK to close this screen and then click next to get to the query screen. For this screen, we're simply going to paste in the query shown below. (The query will make more sense in a later article where we did into the data warehouse schema for TFS)

SELECT

tp.[Team Project]

,SUM([Record Count]) AS [Total Work Items]

,SUM([Revision Count]) AS [Revisions]

,SUM([State Change Count]) AS [State Changes]

FROM [Work Item History] wih

JOIN [Team Project] tp

ON wih.[Team Project] = tp.__ID

WHERE tp.[Is Deleted] = 0

GROUP BY tp.[Team Project]

ORDER BY 1

 

Once you have the query in place, choose next and then choose a tabular report type as shown below:

Click next, and add Team_Project to the grouping, and the remaining fields to the detail as shown below:

 

Click Next and then choose the specific formatting you want. I personally prefer the Block layout with the Corporate style, but of course this is just a personal preference. These screens are shown below:

Once you have the style and layout selected, click next to take you to the deployment screen. Here you'll put the name of your SSRS Server and the folder that you want to deploy the reports to. In my case, I'm using my development instance of SSRS to deploy and test the reports so I just use Localhost as shown below:

Once this is specified, click Next to name the report as shown below:

Once you have named the report, click Finish to return to the Visual Studio development environment. This will open to the report designer with the design pane open as shown below:

If you want to see what your report will look like, click the Preview tab to see a report that looks something like this:

Obviously you'll want to play around with the layout of the columns and the colors to meet your requirements, but once you have the report looking the way you want it, you can choose DEPLOY from the BUILD menu to publish the report to SSRS. The report will then be available for use by users of TFS. (We'll discuss the deploying of reports to end-users in a future article)

This rather lengthy article demonstrated a very simplistic approach to creating a report to detail statistics for all Team Projects that are deployed to your TFS instance. In the next article I'll discuss using the wizard with the OLAP cube to detail more information about your TFS projects.