March 13, 2012 
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
AS
SELECT
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
--etc
FROM Tfs_Warehouse.dbo.CurrentWorkItemView
After that I create a view that lists all my Bugs
CREATE VIEW vw_AllBugs
SELECT bf.*
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.
The Road to ALM 
This is really nice posts
Hi, very nice post about the custom reports. You mentioned ” In my next post I will build a report based on our custom views and talk a little about the Tfs_Warehouse content..”
Is this post online? I cannot find it.
Not online (yet)…
How do you get bug description (not title) with sql, please
Hi,
Description is not a field that you can extract from the data warehouse. Long Text fields (Text and HTML) are not published. If you need to get this text you need to query the collection database. This is not recommended or supported.
However, If you decide to do it. Create a Reporting Data source to the Tfs_Collection Database. You can create a query that gets the text from the WorkItemLongTexts table. This table contains all revisions of the long text of each work item. The fieldID is an id for a specific field. This varies per installation. If you find a work item by id just look at the field ID.
The query below gets a text of the LAST revision of a work item..
Good luck and be careful!
SELECT ID, Words
FROM (
SELECT FldID, ID, Words,rev, ROW_NUMBER() OVER (Partition by ID ORDER BY REV DESC ) as rownum
FROM Tfs_DefaultCollection.dbo.WorkItemLongTexts wl
WHERE fldid = @fieldid
) WorkItemLongTexts
where rownum = 1
AND ID = @WorkItemID
Hi
In TFS you can setup teams and assign team members to them.
Where would you find the team information from in the Tfs_Warehouse?
You cannot find that in the warehouse 😦 What I usually do is create a view in my extensions database that parses the area path to extract all the team names. DimPerson in the warehouse contains all the people. Then you should move to the collection database to figure out teams and people from the security groups. I know it’s not ideal but this is what it is.
Hope this helps!
Hi,
I would like to know is it possible to create a hierarchical custom report. for eg: Requirement-Test case- Bug. Only Title or the Id of the work items are enough but the hierarchical structure is needed. Could you please tell me is that possible.
It is possible but quite hard in ssrs. You need To Create a query that joins itself. Maybe it is easier to just use the hierarchical query tool in vs or vs web.
Hi,
I would like to know if it is possible to create a report using the Current ItemView of TFS_warehouse. I need the report with the fields Product Backlog Item-Test case-Bug. Also, could you please let me know on where can i find good resources to gain understanding of the TFS_Warehouse views and the tables.
Hi,
Could you please let me know on how to display the test-cases and the bugs associated with them using CurrentWorkItemView in TFS_Warehouse. Also, please let me know about any good resource to get a deeper understanding about the tables and the views of TFS_Warehouse.
Just add the table twice and join them based on the correct link type. The warehouse model is rather old and did not change for a long time. I do not have a lot of links I can share.
Ok. Thanks for the information.
The CurrentWorkItemView in my model doesnot have link type attribute to make a join. So should i be using the dimension and fact tables to eastablish this? If so, could you please elaborate on the solution.