Report: joint data set to select by resolution state
Hello,
I have tried to create a report that should show attributes (summary, ...) of fixed workitems only (to exclude duplicate, invalid, etc.) The idea is to create joint data set + set up a filter.
But I have faild with creating joined data set.
Problem #1:
The table of WI states has "extened" id instead of WI table, see bellow:
Status:
STATE | STATE_GROUP | STATE_NAME
----------------------------------------------------------------------------
_mirLkB7qEd6HwOhml8ZEZAs3 | CLOSED_STATES | Resolved
_M1ZDIEX8Ed6oBPSG_x_KcgsConsultancy.state.s3 | CLOSED_STATES | Resolved
I have only idea: to "shorten" the States.STATE but I don't know if id "extenssion" is the same width all the time.
Work Items:
SUMMARY | CATEGORY_NAME | WI_TYPE | STATE_NAME | RESOLUTION | ...
-----------------------------------------------------------------------------------------------------------------------
Process, ... | Internal | task | 3 | 1 | ...
ASM centre ... | Support | consultancy | Consultancy.state.s3 | Consultancy.resolution.r2 | ...
Problem #2:
Where (in which table) can I find the resolution name?
Thanks you for any ideas :-)
I have tried to create a report that should show attributes (summary, ...) of fixed workitems only (to exclude duplicate, invalid, etc.) The idea is to create joint data set + set up a filter.
But I have faild with creating joined data set.
Problem #1:
The table of WI states has "extened" id instead of WI table, see bellow:
Status:
STATE | STATE_GROUP | STATE_NAME
----------------------------------------------------------------------------
_mirLkB7qEd6HwOhml8ZEZAs3 | CLOSED_STATES | Resolved
_M1ZDIEX8Ed6oBPSG_x_KcgsConsultancy.state.s3 | CLOSED_STATES | Resolved
I have only idea: to "shorten" the States.STATE but I don't know if id "extenssion" is the same width all the time.
Work Items:
SUMMARY | CATEGORY_NAME | WI_TYPE | STATE_NAME | RESOLUTION | ...
-----------------------------------------------------------------------------------------------------------------------
Process, ... | Internal | task | 3 | 1 | ...
ASM centre ... | Support | consultancy | Consultancy.state.s3 | Consultancy.resolution.r2 | ...
Problem #2:
Where (in which table) can I find the resolution name?
Thanks you for any ideas :-)
13 answers
Use the LIVE_WORKITEM_CNT table.
WORKITEM_STATES is used for trend reports like open vs Closed and usually has a lot of duplicated rows, is slow and does not store all the work item fields.
LIVE_WORKITEM_CNT is a live table meaning it gets its data from the repository as opposed to the data warehouse. So it shoudl give you more up to date results.
WORKITEM_STATES is used for trend reports like open vs Closed and usually has a lot of duplicated rows, is slow and does not store all the work item fields.
LIVE_WORKITEM_CNT is a live table meaning it gets its data from the repository as opposed to the data warehouse. So it shoudl give you more up to date results.
Yes, we are using LIVE_WORKITEM_CNT
It contains Resolution column that seems to be an id (1, consultancy.resolution.r2)
Should we filter out the resolved as invalid by the id or?
Roman
It contains Resolution column that seems to be an id (1, consultancy.resolution.r2)
Should we filter out the resolved as invalid by the id or?
Roman
Use the LIVE_WORKITEM_CNT table.
WORKITEM_STATES is used for trend reports like open vs Closed and usually has a lot of duplicated rows, is slow and does not store all the work item fields.
LIVE_WORKITEM_CNT is a live table meaning it gets its data from the repository as opposed to the data warehouse. So it shoudl give you more up to date results.
Problem 1: Yes the size of the id extension is fixed. BTW, the id extension is the project area's item id.
Problem 2: Yes you could use the the resolution ids. Unless you intend to present those to the user. In that case you could use the new Live table named ENUMERATIONS to map the ids to the names. The ENUMERATIONS table is only available in 2.0.
Problem 2: Yes you could use the the resolution ids. Unless you intend to present those to the user. In that case you could use the new Live table named ENUMERATIONS to map the ids to the names. The ENUMERATIONS table is only available in 2.0.
On 8/6/2009 11:38 AM, roman.smirak wrote:
Hi Roman,
I see the ENUMERATIONS table in the WORKITEMS_SNAPSHOT in 2.0. (Note
it's a live view on the database, so it doesn't actually exist as a
table in the data warehouse, although the BIRT data sets treat it as
one). Attached is a screenshot from my data warehouse admin web ui. Let
me know if you can't find it.
james
RTC Reports Team Lead
Hi Rafik,
thanks for your answer but I cannot find any ENUMERATIONS table.
We use 2.0 release.
Or is there any other way to learn the id's?
Roman
Hi Roman,
I see the ENUMERATIONS table in the WORKITEMS_SNAPSHOT in 2.0. (Note
it's a live view on the database, so it doesn't actually exist as a
table in the data warehouse, although the BIRT data sets treat it as
one). Attached is a screenshot from my data warehouse admin web ui. Let
me know if you can't find it.
james
RTC Reports Team Lead
page 1of 1 pagesof 2 pages