Using Excel pivot charts, can I filter on creation dates within the Work item history cube.
Hello,
I am using the cumulative count in Work item history cube using the Pivotchart within Excel (a great way to get quick graphs for management).
The problem I am having is that I want to see the progression of states between specific dates.
As it stands now I can choose specific weeks to see but the counter in the graph does not start at zero. If I could put the creation date as an additional filter the graph would start at zero.
In the Work Item History cube there are no dates contained in the workitem that can be selected
In beta3 this was possible but then again the structure was different (there was only a workitem cube).
I was wondering if there is a way I can accomplish this?
Thanks
B. Huard - Incycle software
[795 byte] By [
B.Huard] at [2008-3-5]
Hello, I'm not sure if I understand exactly the report you are trying to create so let me try to give you and idea and see if that helps.
If you are trying to build a report where you can see for each day how many bugs were created, activated, closed, resolved in that particular day, you can keep using the Work Item History perspective and you can drag to your pivot table columns, "Work Item.Previous State" and "Work Item.State", then drag to your rows any of the date dimensions and finally drag to the data section the "State Change Count" measure.
Federico.
Thanks for the idea
But this is what I am looking to do; display the progression of defect states each week as of date X.
By using the Area Chart type the manager sees the state as there where at the end of each week (I use the date - week as my row and WorkItem.State as my column).
I could use the StateChange count measure but the Graph has peaks between each week. It is not as visual as the progressive charts.
The advantage of the progressive is that you also get the total number of defect as they keeps on going up. You see the stalibilization curve of the project (ex: mid project 50 defects opened in a week and toward the end of project you have 5 defects). The VP quicly understands if the week was good and the situation of the states.
To do this I can only use the Cumulitive count measure. The problem I have is that if wish used the Submitted By date to filter all defect submitted previous before date X. But doing this it has no impact on cumulitive count measure.
I can always take the raw data, substract previous counts and do a chart based on that but it more work and management charts is something you would usually generate each week (for steering commitee meetings I am doing this workaround)
In beta3 refresh I was able to accomplish this.
Hi,
The reason that there are no dates except for "Date" dimension in the work item history perspective is that it becomes very confusing to use the "As-of" measures like "Cumulative Count" in conjunction with other dates.
The "Cumulative Count" measure will show the total number of work items that meet the selection criteria as it appeared at the end of the day, month, week, or year that you use as a slicer. If you want to look at the progression of states, you should use "State Change Count", which will be 1 for each revision of a work item in which the state changed from the previous revision.
So, for example, if you want to look at the number of activations, you can use "State=Active" in your filter, Scope the date range down in the filter to the time you want to analyze, for example, "Month=January", place Date.Date or Date.Week on the row axis in the pivot table, and then put "State Change Count" in the data section of the pivot.
You can use this in conjunction with "Previous State" to do all sorts of analysis on how states are changing over time (for example, Previous state=Resolved or Closed and State=Active will show "Reactivations", etc)
It seems that what you are after is a cumulative-style report based on state transitions. This requires a calculated member, or some other calculation to aggregate the state transitions for the selected time range.
You have two options for getting the base data:
1. Use "Current Work Item Count" in conjunction with the date you are analyzing. For instance, if you slice "Current Work ITem Count" by "Resolved Date" it will show the count of all work items that were resolved during that slice of time.
2. Use "State Change Count" in conjunction the state you are analyzing and Date.Date (which is effectively the "as-of" date).
The first technique deals with the latest revision of each item, so if the item was resolved 5 times, you will see it only counted once (for the last resolution). The second deals with events, so you would see all five events.
Once you have chosen the technique (events vs. latest revision), you need to aggregate the results using a RunningSum-like calculation, which will add up things cumulatively over the time for your analysis yielding results like:
day number cumulative number
1 2 2
2 3 5
etc
Again, you have a couple of options for doing this:
1. Add a calculation to the cube. Unfortunately, this is quite difficult on v1.
1a: Copy the warehouse schema file from C:\Program Files\Microsoft Visual Studio 2005 Team Foundation Server\Tools to a local directory
1b: Add a calculated measure to it using the same pattern as is used for cumulative count. The formula for (1) above (counts multiple resolutions of the same item) would be:
Sum([Date].[Date].[Date].Members.Item(0):[date].[date].currentmember, measures.[state change count])
1c: Rebuild the warehouse using the "-o" option as described here http://msdn2.microsoft.com/en-us/ms400783.aspx
2. You can use the "RunningValue" function in a Reporting Services report to do the calculation. For example, "RunningValue(Fields!State_Change_Count.Value). This would not be available from excel, but would enable creation of a nice rendered report that you can share on the web.
Hope that helps you,
tom
