How to get STATE_NAME in STATE ?
Creating my Own REPORT
I can get STATE_NAME from the table LIVE_WORKITEM_CNT But I ultimately want to get the STATE_NAME from the STATE database neither STATE not STATE_GROUP in the STATE table match the STATE_NAME What path should I take ? It seems I need to get WORK_ITEM:STATE_NAME -> <map> -> STATE:STATE -- Christophe Elek Jazz L3 IBM Software Group - Rational |
11 answers
STATE_NAME in the LIVE_WORKITEM_CNT table is the id of the state which is only unique within a project area.
In the table STATE, the column named STATE is the concatenation of the item id of the project area + the letter 's' + the id of the state. So the get the human readable STATE name given the id of the state, do the following: concatenate the project area item id + "s" + the id of the state which you get from LIVE_WORKITEM_CNT. And use that as a key in the STATE table to get the STATE_NAME column. |
STATE_NAME in the LIVE_WORKITEM_CNT table is the id of the state which is only unique within a project area. this note help me a lot! please, add this information in wiki (snapshot schema) |
|
rjaouani,
Are there any tip to join LIVE_WORKITEM_CNT.ITERATION_ID with ITERATION.ITERATION_ID? ITERATION_ID is not a unique field, so: if my project template creates the Iteration called "1.0 - M1, all my projects have an Iteration with the same ITERATION_ID :/ I would like to join a workitem with his Iteration. |
rjaouani, The LIVE_WORKITEM_CNT table should have exposed the ITERATION_ITEMID column. But it did not. I guess we never needed to know about an iteration more than its name. In 2.0, we exposed a new snapshot named LIVE_SNAPSHOT which exposes the raw data in the repository. Look for the following tables which may give you the information you need: com.ibm.team.process.Iteration com.ibm.team.workitem.WorkItem I also recommend avoiding using BIRT's joins. Those are not real database joins. BIRT will select all the data from both tables will join the data in memory. I would recommend selecting the data from the smaller tables, then use that data the restrict the number rows you will be selecting from the big table. Goof luck Rafik Jaouani RTC Developer (Reports component) |
|
Hi rjaouani,
I have the ITERATION_ID and it's a not unique ID, not a name. But I need to know the endDate of iteration to know the endDate of a workitem when dueDate is null. This endDate is important in my report, so I asked about the join. I can't do that using LIVE_WORKITEM_CNT, so I will try the LIVE_SNAPSHOT. Thanks!
Good Information! But in my case, using LIVE_WORKITEM_CNT I need to use joins to complete information to each workitem :/ Apparently in LIVE_SNAPSHOT the workitem has more data then I will not need the joins. Thanks for the tips!! |
|
First question about LIVE_SNAPSHOT:
Where is the STATE_GROUP? I didn't find a table "state" in LIVE_SNAPSHOT. |
First question about LIVE_SNAPSHOT: The Live Snapshot only exposes a subset of the repository data. We call that data: queryable item data. Data in jazz can also be stored as XML. That is how the states are stored. May I ask what kind of report are you trying to develop? I could suggest what tables to use. |
I would like to create a "simple" Open Work Items by Owner report. For each workitem I need to show: - WI ID - WI Type - WI Summary - WI Project - WI number of day open (date now - create date) - WI estimate time (duration) - WI dueDate or Iteration endDate if dueDate is null So think I need to: - "join" WI with States to know Open States (state_group) and filter Open Work Items only - "join" WI with Iteration to know endDate LIVE_SNAPSHOT have Iteration endDate in "com.ibm.team.workitem.WorkItem", so to finish report I need to know the Open States only. thanks |
I would like to create a "simple" Open Work Items by Owner report. For each workitem I need to show: - WI ID - WI Type - WI Summary - WI Project - WI number of day open (date now - create date) - WI estimate time (duration) - WI dueDate or Iteration endDate if dueDate is null So think I need to: - "join" WI with States to know Open States (state_group) and filter Open Work Items only - "join" WI with Iteration to know endDate LIVE_SNAPSHOT have Iteration endDate in "com.ibm.team.workitem.WorkItem", so to finish report I need to know the Open States only. thanks Look at the example report design named Work Items by Owner. You may find your answer there. It is a live report that uses both the LIVE_WORKITEM_CNT and the STATE tables; though, we did that report two years ago, our BIRT skills are much better now :) You may also look at the following two videos which show a better way to do joins than BIRT's inefficient way. http://jazz.net/library/video/288 http://jazz.net/library/video/289 I will be on vacation all of this week. If you really get stuck, raise a work item against Reports and me or James Moody could do the report for you and attach it to the work item. Rafik Jaouani RTC Developer (Reports component) |
|
Hi Rafik,
Where can I find it? I get "Open Work Items" from Report Central: https://jazz.net/wiki/bin/view/Main/ReportsCentral MY STATUS: --------------------- I'm trying to use LIVE_WORKITEM_CNT, but I have problem with this:
Remember what you said: "The LIVE_WORKITEM_CNT table should have exposed the ITERATION_ITEMID column. But it did not.". So I can't get endDate of Iteration. And using LIVE_SNAPSHOT I have problem with this:
Remember: LIVE_SNAPSHOT don't have the table "state" or the attribute "state_group". --------------------- I'm going to watch the videos, thanks for the tips! Have a good vacation! |
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.