How to get STATE_NAME in STATE ?

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.

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.

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,

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.

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,

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.

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!

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.

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:

Where is the STATE_GROUP?
I didn't find a table "state" in 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.

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

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
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,

Look at the example report design named Work Items by Owner. You may find your answer there.

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:

- "join" WI with Iteration to know endDate

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:

- "join" WI with States to know Open States (state_group) and filter Open Work Items only

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!