This post is part of the series Using Custom Reports in TFS. The posts are more or less built on top of each other. Please read the earlier posts for a better understanding.
Earlier in this series:
How to set up my reporting environment and structure
Now that we know a little bit about how the Tfs_Warehouse works and which tools we can use to present some data from the warehouse, it is time to set up your environment. The “mistake” I made in the beginning is to start building reports directly. This is of course tempting and the normal behavior of a developer J, but it is better to take some time and set things up correctly from the start.
The things I will talk about in this post:
- Setting up a Custom Reports database
- Creating a Custom Reports Data source
- Creating some reusable views
- Creating a reusable report template
I will not cover the basics of building reports in Report Builder. Please refer to MSDN for some good tutorials and walkthroughs for this. You can find those here. http://msdn.microsoft.com/en-us/library/ms159253.aspx
Setting up a Custom Reports database
As I described in my previous post, the database that you have to use for your custom reports is the Tfs_Warehouse database for all relational queries. The most obvious thing to do is to create queries in your report that use the default data sources.
This works great, but you will see that if you create a few reports queries will be copied and pasted and maintainability drops. Every report needs that extra field? All queries have to be rewritten. In case of a simple “SELECT * “ this is not hard, but you can imagine the effort if you have a lot of UNIONS, INNER JOINS etc..
A second challenge of inline queries is even worse. Imagine that TFS gets a new version and the Tfs_Warehouse database gets a bit restructured. We have seen the changes from TFS 2008 to TFS 2010 so this is not unthinkable.
A good way to avoid this problem is to create views and/or stored procedures that contain all your SELECT statements. This way you have all your queries centralized and you only have to update them once. But what is a good place to store your views/stored procedures?
This is definitely NOT the Tfs_Warehouse database. This database can be thrown away and be rebuilt by the TFS admin console, not to mention an upgrade.
So, we create a new database that will service our query needs. Go to your SQL Manager and create a database called Tfs_Warehouse_Extensions. Also create a security role TfsWarehouseDataReader. This is the same role name as in the Tfs_Warehouse database. Add your TFS Reports user to this group.
This database will from now on serve as your Custom Reports API. Add all users that want to build reports to the TfsWarehouseDataReader group. By doing this you prevent users to write complex queries against the Tfs_Warehouse. You only grant them read rights on the queries you write. The database serves as an API. If you document your queries, even “end-users” can build reports.
Creating a Custom Reports Data source
The database that you created must now be made available for use inside your reports. To do this we create a data source that point to this database.
Go to your report server (normally running on http://<tfsserver>/reports) and create a new data source on the highest level. The highest level because Tfs_Warehouse contains information of all your collections so your Custom database will as well.
See http://msdn.microsoft.com/en-us/library/ms159165.aspx for a more detailed explanation.
Creating some reusable views
Now it is time to write some queries. Create some basic views that provide you some handy functionality. For example:
- Select all Bug Fields (example)
- Select all Bugs (example)
- Select all Product Backlog Item Fields
- Select a list of all Active sprints
- Select a list of all Test Plans
In my next posts I will explain more about the views in the Tfs_Warehouse. For now it is sufficient to know that Tfs_Warehouse contains a view CurrentWorkItemView. This contains all information about the latest state of a work item.
If I will have a lot of reports that show bug information, it is useful to let all the “bug related” views show the same columns. So for example.
I first create a view that select all “relevant” Bug Fields
CREATE VIEW vw_BugFields
System_Id as BugId
, System_Title as BugTitle
, System_AssignedTo as BugAssignedTo
, Microsoft_VSTS_Common_Priority as BugPriority
, System_State as BugState
, IterationGuid as BugIterationGuid
, IterationPath as BugIterationPath
After that I create a view that lists all my Bugs
CREATE VIEW vw_AllBugs
FROM Tfs_Warehouse.dbo.CurrentWorkItemView cv
INNER JOIN vw_BugFields bf ON cv.System_Id = bf.BugId
WHERE cv.System_WorkItemType = 'Bug'
This way I am starting to build up my API, by wrapping the Tfs_Warehouse database views in my own.
Creating a reusable report template
Besides setting up reusable queries, it is also recommended to set up a template report. This will save you loads of time in the future. TFS Reports have some properties that are not in a “Blank” report that you get when you start Report Builder. Therefore it is very useful to create a template report that contains all the necessary information.
For storing your Work In Progress and templates it is a good plan to create a folder on the report server.
Open a report that comes with a TFS project (Agile or Scrum) inside Report Builder. A good one is the Sprint Burndown of the MS Scrum Template, but it can be any other TFS Report.
- Open up the parameters section and delete all parameters except the ones below.
These parameters are used by the SharePoint portals and in your TFS Team Projects. For example ExplicitProject makes sure the context is set to the project you have active in Team Explorer.
- Open up the Data Sources.
Rename the data sources to resemble the name on your report server, and add your custom report data source.
- Open up the datasets. Remove all datasets except the ones below. These datasets you can use to display some useful information about processing times etc.
- Remove everything from the design pane
- Save your report as a template and use this for all your future reports.
Now you are all set to get started. In my next post I will build a report based on our custom views and talk a little about the Tfs_Warehouse content.