Using Custom Reports in TFS: Part 3 – How to set up my reporting environment and structure

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.

clip_image002

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.

clip_image004

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.
clip_image005
clip_image006

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.

clip_image007

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.

clip_image008

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.

clip_image009

clip_image011

  • 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.

clip_image012

  • Remove everything from the design paneclip_image013

     

  • 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.

Advertisements

16 Responses to “Using Custom Reports in TFS: Part 3 – How to set up my reporting environment and structure”

  1. Sonia Bansal May 9, 2013 at 5:49 am

    This is really nice posts

  2. 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

  3. 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!

  4. 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.

  5. 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.

Trackbacks/Pingbacks

  1. TFS Reports–Unleash them with PowerPivot | The Road to ALM - July 17, 2014

    […] How to set up my reporting environment […]

  2. Create a Trend chart in Visual Studio Online | The Road to ALM - September 1, 2014

    […] posts on this blog, I really like reports. I wrote about how to customize them (here, here and here), how you should use them in your ALM implementation and how to use PowerPivot to your […]

%d bloggers like this: