[VSTS] Excel integration and order of items
One of the annoying characteristics of the VSTS integration with MS Excel is inability to manually change the order of work items. It is not possible to drag and drop or to copy one work item in front of the other. This is especially annoying if we have a scenario and tasks that implement that scenario, and would like to put the tasks below the scenario and possibly additionally put the tasks in some order. Well, in Excel it is not possible.
Good news is that MS Project supports such actions: you can put the tasks below the scenario and even indent them and the order is saved in the Project file.
Since all data comes from the common Team Foundation Server database, it should be possible to use the Project’s services to achieve the needed effect in Excel. The order, set and saved by the MS Project, is by virtue of the Refresh action transferred in the Excel.
To achieve it, I have extended all work item types with additional field for the MS Project’s item ID (use witexport.exe and witimport.exe):
<FIELD name="MSProject Task ID" refname="Ekobit.VSTS.MSProjectTaskID" type="Integer" reportable="dimension"/>
No other changes were necessary since we will need the field only in the work item queries.
The field should be bound to the MS Project’s Task ID field (use TFSFieldMapping.exe):
<Mapping WorkItemTrackingFieldReferenceName="Ekobit.VSTS.MSProjectTaskID" ProjectField="pjTaskID" PublishOnly="true"/>
The next step was to include the new field in a work item query and to sort the query by that field. The query will be used to load the data in Excel.
So far everything has worked as planned. What I wanted to achieve was to be able to change the order of some work items in MS Project, publish and after refresh to get the same order of work items in Excel.
Alas, if the only thing that you change in MS Project is the order of tasks, although this changes the Task ID (notice, this is not the TFS’s work item ID), TFS Project Addin doesn’t see any changes and when publishing does nothing. The dirty trick I had to use was to touch the rows in the Project file somehow not changing the data in the process but triggering the publishing logic of the Project Add in. Afterwards, Project Add in works as expected and data including the changed Task IDs are published. After a refresh, the tasks in Excel are displayed in the new order.
The goal is achieved – MS Project makes it possible to set the order of items in Excel.
Good news is that MS Project supports such actions: you can put the tasks below the scenario and even indent them and the order is saved in the Project file.
Since all data comes from the common Team Foundation Server database, it should be possible to use the Project’s services to achieve the needed effect in Excel. The order, set and saved by the MS Project, is by virtue of the Refresh action transferred in the Excel.
To achieve it, I have extended all work item types with additional field for the MS Project’s item ID (use witexport.exe and witimport.exe):
<FIELD name="MSProject Task ID" refname="Ekobit.VSTS.MSProjectTaskID" type="Integer" reportable="dimension"/>
No other changes were necessary since we will need the field only in the work item queries.
The field should be bound to the MS Project’s Task ID field (use TFSFieldMapping.exe):
<Mapping WorkItemTrackingFieldReferenceName="Ekobit.VSTS.MSProjectTaskID" ProjectField="pjTaskID" PublishOnly="true"/>
The next step was to include the new field in a work item query and to sort the query by that field. The query will be used to load the data in Excel.
So far everything has worked as planned. What I wanted to achieve was to be able to change the order of some work items in MS Project, publish and after refresh to get the same order of work items in Excel.
Alas, if the only thing that you change in MS Project is the order of tasks, although this changes the Task ID (notice, this is not the TFS’s work item ID), TFS Project Addin doesn’t see any changes and when publishing does nothing. The dirty trick I had to use was to touch the rows in the Project file somehow not changing the data in the process but triggering the publishing logic of the Project Add in. Afterwards, Project Add in works as expected and data including the changed Task IDs are published. After a refresh, the tasks in Excel are displayed in the new order.
The goal is achieved – MS Project makes it possible to set the order of items in Excel.
posted by Ognjen Bajic at Thursday, May 04, 2006
0 Comments:
Post a Comment
<< Home