As of Today TFS Cube Report in Excel

  • Article

This walk through shows you how to get started using Excel to build a TFS report displaying Cube data relative to today.

When you build a report using an Excel Pivot Table often you want to filter that data by a date relative to today. For example you would like to see the number of Active Work Items as of today or the number of Work Items resolved this week. However in an Excel Pivot Table you cannot filter on a dynamic value, so every time today changes (i.e. tomorrow) you need up manually update the filter.

One way to get around this is to using the Cube Functions in Excel 2007. A couple caveats are that this makes the report harder to author and the rows/columns displayed in the report is statically defined. Often this is fine.

Example

I would like a report showing the number of Active Work Items assigned to and the number resolved by some set of people this week.

clip_image002

There are really two different bits of information we want (the Active Work Item Count and the Resolved Count) for each person. I will go through getting each bit of information separately.

(1) Active Work Item Count

To get the Active Work Item count as of today for a person we can use the Current Work Item Count Measure and filter/slice by the Work Item State and Assigned To. Translating this slightly:

We want the Cube Value of the Current Work Item Measure sliced by the Work Item State = Active and Assigned To Alias* = <someone> .

Current Work Item Measure = [Measures].[Current Work Item Count]

Work Item State Active = [Work Item].[System_State].&[Active]

Assigned To Alias = [Assigned To].[Alias].&[<someone>]

So for Nick Ericson (alias = nericson) we want:

=CUBEVALUE(<Name of Cube Connection>,"[Measures].[Current Work Item Count]","[Assigned To].[Alias].&[nericson]","[Work Item].[System_State].&[Active]")

Of course the member expressions (i.e. "[Assigned To].[Alias].&[nericson]") can be replaced with cell references making the report easier to manage. For example to replace "[Assigned To].[Alias].&[nericson]" with a cell reference:

1) In cell B15 (or wherever) put:

=CUBEMEMBER(<Name of Cube Connection>,"[Assigned To].[Alias].&[nericson]")

2) Then replace "[Assigned To].[Alias].&[nericson]" in the CUBEVALUE function with B15 (or reference it another way, such as $B$15, depending on your preference).

* Why did I use the Assigned To Alias?

We can specify an Assigned To person using either their identifier (i.e. [Assigned To].[Person].&[25]) or their Alias (i.e. [Assigned To].[Alias].&[nericson]). A person’s identifier in the cube is not necessarily static - it can change if the Reporting Relational Warehouse is rebuilt. We can work around this by either querying the Relational Warehouse to get the ID or updating the report if the Warehouse is rebuild, but it is easier here to use their alias. Hopefully that doesn’t change very often.

(2) Resolved Count this week

First off we need to understand what it means for a work item to be Resolved this week. I only want to count work items that are Resolved and stay resolved. So I will not count those that become reactivated. Those that are reactivated and assigned back to me will reappear in the Active Work Item Count above.

To find the Resolved Count this week for a person we can use the Current Work Item Count measure again and filter/slice by Work Item Resolved By and the Resolved Date. Translating slightly:

We want the Cube Value of the Current Work Item Measure sliced by the Work Item Resolved By = <someone> and Resolved Date (week) = <this week> .

Current Work Item Measure = [Measures].[Current Work Item Count]

Work Item Resolve By = [Work Item].[Microsoft_VSTS_Common_ResolvedBy].&[<someone>]

Resolved Date (week) = [Microsoft_VSTS_Common_ResolvedDate].[Year Week Date].[Week].&[<this week>]

<this week> can be found in Excel using:

=TODAY()-WEEKDAY(TODAY(), 2).

We need to convert it into the correct format which can be done using (where B9 = cell with formula above):

=IF(LEN(YEAR(B9))=1,"0"&YEAR(B9),YEAR(B9))&"-"&IF(LEN(MONTH(B9))=1,"0"&MONTH(B9),MONTH(B9))&"-"&DAY(B9)&"T00:00:00"

So for Nick Ericson we want (where B10 = cell above):

=CUBEVALUE(<Name of Cube Connection>,"[Measures].[Current Work Item Count]","[Work Item].[Microsoft_VSTS_Common_ResolvedBy].&[Nick Ericson]", "[Microsoft_VSTS_Common_ResolvedDate].[Year Week Date].[Week].&["&B10&"]")

Repeat this for the other people and you have the data needed to produce the report above.