Dashboard Creator – Part IV


In previous posts, I introduced on the Dashboard Creator tool that I made in Excel.


The most recent post presented an example of where you could pull data from external sources into your dashboard, and display alongside TFS stored data.


What I found when creating this dashboard creator was that while TFS would reference people by their friendly name, such as "Gregg Boer". Other systems would reference the person by their email alias, such as "greggboe".


Technorati Tags:

So to solve this problem, I programmed in the ability to define an alternate set of look up values into the dashboard.


In the Dashboard-TFS.xls file I attached to the first post, the alternate lookups are stored in Row 3 of the sheet. Row 3 is normally a hidden row, but when you un-hide it, you'll see this:

image

You'll note that Row 3 (highlighted with a red-box) shows alternate names for each of the team members.


For example, for me, Row 2 would have "Gregg Boer", and Row 3 would have "greggboe".


This way, if any of the data being read from the various sheets has its "Assigned To" field matching either "Gregg Boer" or "greggboe", it will be compiled into my data column.


However, "Assigned To" is very likely not the field name used by the external system. For example, with these test results, the field name we are after is "owner":


image


The dashboard creator allows you to specify, for a given dashboard row, if another field should be used. Note the "Headerfield=owner" clause in the example below.


image


With the functionality of specifying alternate names and altername field names, I was able to create a dashboard that successfully mixed data from multiple sources.


I'm very curious if this series of posts is interesting to you, the reader, or not. I'm looking to provide tools that are helpful in managing the project, not just coding related tasks.


Let me know. Thanks


--Gregg Boer

Comments (6)
  1. Very interested in what you are doing.  I have spent some time looking at reporting for projects and it is not easy! Having a bit of a problem with your spreadsheet however. I tried to replace your TFS bound list with mine and it cannot find the server – I am guessing that excel stores the name of the TFS server, which is currently yours.  How do I reset that?

  2. Chris Rathjen on TFS Orcas Setup. Trent Nix on Work Item Customization: Set a Field Value That Cannot…

  3. I’m actually having the same issue as James.  I cannot replace the list with a TFS-bound list from our TFS server.

    It "can’t find the server" so I’m also thinking that Excel stores that information with the file somehow.

    Ed B.

  4. In these four posts ( I , II , III , IV ), I introduced a Dashboard creator that I wrote using Excel+Macros.

  5. In these four posts ( I , II , III , IV ), I introduced a Dashboard creator that I wrote using Excel

Comments are closed.

Skip to main content