Query work items history

Currently, I believe you can only query on the current state of a work
item, not on its previous states.

But you can run a report on the historical state of a work item (if the
state information of interest is included in the RTC data warehouse.

Cheers,
Geoff

bportaluri wrote:

We have a process workflow that allows the reopen of defects.
I need to know if a defect that has been closed (fixed) was reopened
or not. In other words if it was reopened for a bad fix or not.

I was thinking to search in the history of the defect if it was ever
in the 'reopened' status.
I cannot find this option in the query editor.
Is it possible to do this query ?

Thanks

But you can run a report on the historical state of a work item (if the
state information of interest is included in the RTC data warehouse.

Can you also please specify how we can search on the historical state of the work items? The additional information we need is also the date it was moved to that state.

But you can run a report on the historical state of a work item (if the
state information of interest is included in the RTC data warehouse.

Can you also please specify how we can search on the historical state of the work items? The additional information we need is also the date it was moved to that state.

While we do not have any out-of-the-box reports that will suit your needs, it should be possible to author such a report yourself.

We have a number of resources on our wiki page that should help with authoring:

https://jazz.net/wiki/bin/view/Main/ReportsMain

Specifically, if you've never written a BIRT report in RTC before, here's a primer:

https://jazz.net/wiki/bin/view/Main/ReportsCreatingANewReport20

The information you're looking for is indeed stored in the data mart. The table you're interested in is WORKITEM_CHNGS, stored in the WORKITEMS_SNAPSHOT. The changes table stores one row for each work item change, has columns for the pre-change and post-change values. So in your case, it sounds like you'll want to query on this table for work items where pre-change state (Prev_Wi_State_Id) is one of your closed states, and post-change state (Wi_State_Id) is the re-opened state. This will also include the date of the change, that is, the timestamp when the work item was reopened. Of course, you'll want to filter further, probably based on project area (Project_Area_Id), possibly work item type (Wi_Type_Id) so you only get defects, and maybe further based on team area or other criteria that the workitem_chngs table allows you access to.

A full annotated description of the workitem_chngs table (in fact, all the tables in the data mart) can be found here:

https://jazz.net/wiki/bin/view/Main/DataWarehouseSnapshotSchemas20

You may also find our out-of-the-box reports helpful to understanding the birt editor. You can find the .rptdesign files (birt artifacts) in your rich client installation in the plug-in directory for com.ibm.team.examples.reports.common. You can import these into your workspace and open them in the BIRT editor.

Let me know if you need any further information.

james
RTC Reports Team Lead