I developed the Work Item Snapshot report in order to provide clear views to different layer of management for understanding their teams’ work load and how they are distributed among various technical areas.

The original idea was to group, filter, display, and drill-down them on pie charts based on areas in TFS. It evolved into the following feature list after getting feedback from managers:

  • Grouped based on areas and sub-areas
  • Also grouped by various other custom fields, i.e. Planning Types, Work Type, Activity Types, etc.
  • Users can specify date range
  • Provide a list of work items (instead of numbered pie chart) when reaching the end nodes of areas
  • Filtered by board membership
  • One copy of the report for all boards to minimize future maintenance

The initial challenges I ran into were mostly related to TFS 2015 database schema changes. Had to spend some time to find out how TFS manages areas, board membership, board’s areas, iterations, and column mapping, rules, and etc.

Another dilemma was the data sources. Since TFS did not populate the permission setting into its cube and data warehouse, to manage the access and data scope within SSRS (which we decided to use at this moment), we will have to get it either from transaction database or through APIs.

I decided to get measures from cube but generate a data set from database to lookup as the mechanism for managing the visibility scope. The impact for directly access database should be minimum as for the report itself, data referencing will not be per user, per report. Plus this is a management report and should only be run once every few days.