Report Question - Howto get work item state at given date
I am working on a BIRT report to display the work items states for a date range. Using the WORKITEMS_SNAPSHOT Jazz data source, I have found state information in a few tables. Here is what I have found:
LIVE_WORKITEM_CNT - state is always the most current state
WORKITEM_STATES - state is always the most current state for all occurrences of a work item id
WORKITEM_CHNGS - state and date changed are present, but no easy way to get state as at a specified date
What I need is to be able to get the state of each work item as it was on a date specified. For example, if my state history for a work item is:
Jan 1 - New
Jan 2 - In progress
Jan 30 - Resolved
Feb 2 - Closed
and I want to know the state of the work item on Feb 1 (Resolved) I can only get that information from the WORKITEM_CHNGS table and if I have a parameter with the appropriate date, I will get three rows. Using SQL, I would use a nested query to get the most recent row for each work item id but I don't think that is possible with the Jazz data source.
Is there any way to do what I am trying to accomplish?
LIVE_WORKITEM_CNT - state is always the most current state
WORKITEM_STATES - state is always the most current state for all occurrences of a work item id
WORKITEM_CHNGS - state and date changed are present, but no easy way to get state as at a specified date
What I need is to be able to get the state of each work item as it was on a date specified. For example, if my state history for a work item is:
Jan 1 - New
Jan 2 - In progress
Jan 30 - Resolved
Feb 2 - Closed
and I want to know the state of the work item on Feb 1 (Resolved) I can only get that information from the WORKITEM_CHNGS table and if I have a parameter with the appropriate date, I will get three rows. Using SQL, I would use a nested query to get the most recent row for each work item id but I don't think that is possible with the Jazz data source.
Is there any way to do what I am trying to accomplish?
3 answers
I am working on a BIRT report to display the work items states for a date range.
Hi, Rob.
I was able to do this with RTC 2.0.0.2 with the WORKITEM_CHNGS table. I specified the work item ID parameter and selected the TIME_ID.TIMESTAMP, WI_ID, and WI_STATE_ID.STATE_NAME fields for the output. I get the following results:
TIME_ID.TIMESTAMP WI_ID WI_STATE_ID.STATE_NAME
Oct 7, 2008 2:03 PM 2869 New
Oct 14, 2008 4:03 PM 2869 New
Jan 27, 2009 3:25 PM 2869 New
Jan 28, 2009 9:06 AM 2869 In Progress
Mar 27, 2009 1:48 PM 2869 In Progress
Mar 27, 2009 2:26 PM 2869 In Progress
Apr 2, 2009 3:21 PM 2869 Resolved
Jan 18, 2011 3:08 PM 2869 Closed
If you want to find the state of a work item on a certain date, specify a timestamp in the FROM_TIME parameter. (That parameter is not technically part of the table; it shows up automatically on the Parameters tab of the Edit Data Set window.) In this case, I specified Feb 1, 2009 with this expression:
date = new Date('2009','1','1');
(Note that the months parameter starts with zero, so January = 0, February = 1, etc.) I got the following table:
TIME_ID.TIMESTAMP WI_ID WI_STATE_ID.STATE_NAME
Mar 27, 2009 1:48 PM 2869 In Progress
Mar 27, 2009 2:26 PM 2869 In Progress
Apr 2, 2009 3:21 PM 2869 Resolved
Jan 18, 2011 3:08 PM 2869 Closed
Then just use the value from the first row. Does that get you the info that you need?
-Tim
I was able to do this with RTC 2.0.0.2 with the WORKITEM_CHNGS table.
Whoops -- realized right after I posted that this isn't quite right. If I put April 1 in, it gives me Resolved for the state. I think it will be right if you use the TO_TIME parameter (rather than FROM_TIME) and use the last row of the results by filtering to the row with the largest date.
I am working on a BIRT report to display the work items states for a date range.
Hi, Rob.
I was able to do this with RTC 2.0.0.2 with the WORKITEM_CHNGS table. I specified the work item ID parameter and selected the TIME_ID.TIMESTAMP, WI_ID, and WI_STATE_ID.STATE_NAME fields for the output. I get the following results:
TIME_ID.TIMESTAMP WI_ID WI_STATE_ID.STATE_NAME
Oct 7, 2008 2:03 PM 2869 New
Oct 14, 2008 4:03 PM 2869 New
Jan 27, 2009 3:25 PM 2869 New
Jan 28, 2009 9:06 AM 2869 In Progress
Mar 27, 2009 1:48 PM 2869 In Progress
Mar 27, 2009 2:26 PM 2869 In Progress
Apr 2, 2009 3:21 PM 2869 Resolved
Jan 18, 2011 3:08 PM 2869 Closed
If you want to find the state of a work item on a certain date, specify a timestamp in the FROM_TIME parameter. (That parameter is not technically part of the table; it shows up automatically on the Parameters tab of the Edit Data Set window.) In this case, I specified Feb 1, 2009 with this expression:
date = new Date('2009','1','1');
(Note that the months parameter starts with zero, so January = 0, February = 1, etc.) I got the following table:
TIME_ID.TIMESTAMP WI_ID WI_STATE_ID.STATE_NAME
Mar 27, 2009 1:48 PM 2869 In Progress
Mar 27, 2009 2:26 PM 2869 In Progress
Apr 2, 2009 3:21 PM 2869 Resolved
Jan 18, 2011 3:08 PM 2869 Closed
Then just use the value from the first row. Does that get you the info that you need?
-Tim
Thanks, Tim. It's not quite what I need but it is a good start. I may need to do some intermediate Javascript to get exactly what I need.