It's all about the answers!

Ask a question

Report: Show Current/Available Sprint Work


Glenn Poston (7194) | asked Aug 07 '09, 1:12 a.m.
I'm trying to create a report that will show the available tasks in the current iteration defined by the following rule:

A work item is available if it does not depend on another open work item or does not have a dependency.

I am also trying to link the tasks parent story in as well.

I thought I could use the links table (in COMMON_SNAPSHOT) to get this information, but it seems that the WI_ITEMID in the LINKS table does not match up with the WI_ITEMID for work-items of type "task."

I am able to join stories of the current sprint with the links table, but I am unable to isolate the "tasks" of the current sprint (unless I build the query in UI and use WI_QUERY_RESULTS view) correctly, nor am I able to join them with the links table on WI_ITEM_ID.

I would appreciate any help you could offer.

8 answers



permanent link
Rafik Jaouani (4.9k16) | answered Aug 07 '09, 7:23 a.m.
JAZZ DEVELOPER
Can you please check the following video on how to use the LINKS table:

http://jazz.net/library/video/285

When you say join with the links table do you fetch the whole LINK table, then join with which table?

That could be costly. BIRT joins happen after the fact and do not result in SQL joins.

permanent link
James Moody (3.3k24) | answered Aug 07 '09, 9:07 a.m.
JAZZ DEVELOPER
On 8/7/2009 7:38 AM, rjaouani wrote:
Can you please check the following video on how to use the LINKS
table:

http://jazz.net/library/video/285

When you say join with the links table do you fetch the whole LINK
table, then join with which table?

That could be costly. BIRT joins happen after the fact and do not
result in SQL joins.

Perhaps it would be more feasible in this case to use a work item query

in your report, if the conditions are such that you can express them in
a work item query.

james
RTC Reports Team Lead

permanent link
James Moody (3.3k24) | answered Aug 07 '09, 10:12 a.m.
JAZZ DEVELOPER
On 8/7/2009 9:07 AM, James Moody wrote:
On 8/7/2009 7:38 AM, rjaouani wrote:
Can you please check the following video on how to use the LINKS
table:

http://jazz.net/library/video/285

When you say join with the links table do you fetch the whole LINK
table, then join with which table?

That could be costly. BIRT joins happen after the fact and do not
result in SQL joins.

Perhaps it would be more feasible in this case to use a work item query
in your report, if the conditions are such that you can express them in
a work item query.

james
RTC Reports Team Lead


Following up again because when viewed in the forum, my previous post
appeared cut off. Here is what I was trying to say:


Perhaps it would be more feasible in this case to use a work item query
in your report, if the conditions are such that you can express them in
a work item query.

james
RTC Reports Team Lead

permanent link
Glenn Poston (7194) | answered Aug 10 '09, 9:45 p.m.
On 8/7/2009 9:07 AM, James Moody wrote:
On 8/7/2009 7:38 AM, rjaouani wrote:
Can you please check the following video on how to use the LINKS
table:

http://jazz.net/library/video/285

When you say join with the links table do you fetch the whole LINK
table, then join with which table?

That could be costly. BIRT joins happen after the fact and do not
result in SQL joins.

Perhaps it would be more feasible in this case to use a work item query
in your report, if the conditions are such that you can express them in
a work item query.

james
RTC Reports Team Lead


Following up again because when viewed in the forum, my previous post
appeared cut off. Here is what I was trying to say:


Perhaps it would be more feasible in this case to use a work item query
in your report, if the conditions are such that you can express them in
a work item query.

james

RTC Reports Team Lead

I'm sorry it took me so long to respond. I don't really check my email much on the weekends. I can get a list of the tasks fairly easily from a standard query. However, in the report I would like to link those tasks to their parent story. This is what I'm having problems with. The standard query does not have a means to reaturn/display the parent story's ID. I wa trying to do this using the results of the standard query and joining it with the links table to identify the parent. The thing is, the IDs returned by the standard query don't seem to match up with the IDs on the links table. Any ideas how to match these. I could export and send the projects if that would help.

permanent link
Rafik Jaouani (4.9k16) | answered Aug 11 '09, 4:46 p.m.
JAZZ DEVELOPER
Can please provide more details on which data sets you are using and which columns from those data sets do not match?

permanent link
Glenn Poston (7194) | answered Aug 18 '09, 10:54 a.m.
Can please provide more details on which data sets you are using and which columns from those data sets do not match?


Hello rjaouani. Thanks for your response!

Here is what I'm trying to do. I'll point out what doesn't seem to be matching up at the end.

Dataset - Tasks (Get the current unresolved tasks)
WORKITEM_SNAPSHOT.LIVE_WORKITEM_CNT
input
iterationID
filters
row Equal to "task"
row Not Equal to "multitool.taskWorkflow.state.s2" <- unresolved

Dataset - Dependencies (Get dependencies)
COMMON.LINKS
filters
row Equal to "com.ibm.team.workitem.linktype.blocksworkitem"

Join Dataset - BlockingTasks (Get blockers)
Inner Join on Tasks.WI_ITEMID and Dependencies.SOURCE_ITEMID

Join Dataset - UnblockedTasks (Get tasks that are not blocked)
Left Outer Join on Tasks.WI_ITEMID and BlockingTasks.Dependencies::TARGET_ITEMID
filters
row is Null

Dataset - Parents (Get parent links)
COMMON.LINKS
filters
row Equal to "com.ibm.team.workitem.linktype.blocksworkitem"

Dataset - Stories (Get current stories)
WORKITEM_SNAPSHOT.LIVE_WORKITEM_CNT
input
iterationID
filters
row Equal to "com.ibm.team.workitem.workitemtype.story"

Join Dataset - ParentStories (Add link to children column to stories table)
Inner Join on Stories.WI_ITEMID and Parents.TARGET_ITEMID

Join Dataset - DependencyReport (Join Parent Stories with the unresolved children tasks)
Left Outer Join on UnblockedTasks.Tasks::WI_ITEMID and ParentStories.Parents::SOURCE_ITEMID

The last dataset is where the failure occurs. I get no links between the unblocked tasks and their parent stories.
I get 22 records from the ParentStories preview.
I get 50 records from the UnblockedTasks preview.
However when I join the two I get no links. (Actually I get all nulls for the ParentStories columns since I am using a left outer join)

I think this could probably be simplified when reproduced, but I wanted to show you everything I am doing.

permanent link
James Moody (3.3k24) | answered Aug 18 '09, 3:30 p.m.
JAZZ DEVELOPER
On 8/18/2009 11:08 AM, BenNjerrysNC wrote:
rjaouaniwrote:
Can please provide more details on which data sets you are using and
which columns from those data sets do not match?

Hello rjaouani. Thanks for your response!

Here is what I'm trying to do. I'll point out what doesn't seem to be
matching up at the end.

Dataset - Tasks (Get the current unresolved tasks)
WORKITEM_SNAPSHOT.LIVE_WORKITEM_CNT
input
iterationID
filters
row Equal to "task"
row Not Equal to
"multitool.taskWorkflow.state.s2"<- unresolved

Dataset - Dependencies (Get dependencies)
COMMON.LINKS
filters
row Equal to
"com.ibm.team.workitem.linktype.blocksworkitem"

Join Dataset - BlockingTasks (Get blockers)
Inner Join on Tasks.WI_ITEMID and Dependencies.SOURCE_ITEMID

Join Dataset - UnblockedTasks (Get tasks that are not blocked)
Left Outer Join on Tasks.WI_ITEMID and
BlockingTasks.Dependencies::TARGET_ITEMID
filters
row is Null

Dataset - Parents (Get parent links)
COMMON.LINKS
filters
row Equal to
"com.ibm.team.workitem.linktype.blocksworkitem"

Dataset - Stories (Get current stories)
WORKITEM_SNAPSHOT.LIVE_WORKITEM_CNT
input
iterationID
filters
row Equal to
"com.ibm.team.workitem.workitemtype.story"

Join Dataset - ParentStories (Add link to children column to stories
table)
Inner Join on Stories.WI_ITEMID and Parents.TARGET_ITEMID

Join Dataset - DependencyReport (Join Parent Stories with the
unresolved children tasks)
Left Outer Join on UnblockedTasks.Tasks::WI_ITEMID and
ParentStories.Parents::SOURCE_ITEMID

The last dataset is where the failure occurs. I get no links between
the unblocked tasks and their parent stories.
I get 22 records from the ParentStories preview.
I get 50 records from the UnblockedTasks preview.
However when I join the two I get no links. (Actually I get all nulls
for the ParentStories columns since I am using a left outer join)

I think this could probably be simplified when reproduced, but I
wanted to show you everything I am doing.


Hi,

Rafik's away at the moment and I wanted to follow up with you about this.

First, a few minor notes on optimization and performance:
1. The first couple of data sets have filters which would be much better
served as hard-coded parameters to the data set. That way, they get
included as conditions in the query. The way you have it written with
filters, *all* rows will get pulled into the report, where BIRT will
process them one by one in memory, applying your filter to each one.
You'll get much better memory and time performance.
2. In the case where you're looking for a particular hard-coded state,
should you instead be using the STATE_GROUP? There are only three state
groups: OPEN_STATE, INCOMPLETE_STATES and CLOSED_STATES - they're sort
of "meta-states" into which all states fall.
3. The Links data set, even when you pass the parameters in as suggested
above, will still pull up all blocking links, regardless of whether they
are from unresolved tasks or some other work items. Instead, you may
wish to force BIRT to process the 1st data set first, and in its
onFetch, populate a javascript array with the work item IDs that are
returned; then supply this javascript variable to the links data set, so
you only pull in links, for example, whose source id is in that array.
This should drastically reduce the size of the data pulled in through
this data set.

But these don't affect the correctness of your report.

From the description of your problem, it's not clear to me what the
problem is - the two data sets you're joining together each have data in
them, but the resulting joint data set does not. Would it be possible to
attach this .rptdesign file (or a stripped down one that you don't mind
being publicly visible) to a work item? That would be the easiest way
for us to debug it.

Hope this helps.

james
RTC Reports Team Lead

permanent link
Glenn Poston (7194) | answered Aug 31 '09, 3:03 p.m.
On 8/18/2009 11:08 AM, BenNjerrysNC wrote:
rjaouaniwrote:
Can please provide more details on which data sets you are using and
which columns from those data sets do not match?

Hello rjaouani. Thanks for your response!

Here is what I'm trying to do. I'll point out what doesn't seem to be
matching up at the end.

Dataset - Tasks (Get the current unresolved tasks)
WORKITEM_SNAPSHOT.LIVE_WORKITEM_CNT
input
iterationID
filters
row Equal to "task"
row Not Equal to
"multitool.taskWorkflow.state.s2"<- unresolved

Dataset - Dependencies (Get dependencies)
COMMON.LINKS
filters
row Equal to
"com.ibm.team.workitem.linktype.blocksworkitem"

Join Dataset - BlockingTasks (Get blockers)
Inner Join on Tasks.WI_ITEMID and Dependencies.SOURCE_ITEMID

Join Dataset - UnblockedTasks (Get tasks that are not blocked)
Left Outer Join on Tasks.WI_ITEMID and
BlockingTasks.Dependencies::TARGET_ITEMID
filters
row is Null

Dataset - Parents (Get parent links)
COMMON.LINKS
filters
row Equal to
"com.ibm.team.workitem.linktype.blocksworkitem"

Dataset - Stories (Get current stories)
WORKITEM_SNAPSHOT.LIVE_WORKITEM_CNT
input
iterationID
filters
row Equal to
"com.ibm.team.workitem.workitemtype.story"

Join Dataset - ParentStories (Add link to children column to stories
table)
Inner Join on Stories.WI_ITEMID and Parents.TARGET_ITEMID

Join Dataset - DependencyReport (Join Parent Stories with the
unresolved children tasks)
Left Outer Join on UnblockedTasks.Tasks::WI_ITEMID and
ParentStories.Parents::SOURCE_ITEMID

The last dataset is where the failure occurs. I get no links between
the unblocked tasks and their parent stories.
I get 22 records from the ParentStories preview.
I get 50 records from the UnblockedTasks preview.
However when I join the two I get no links. (Actually I get all nulls
for the ParentStories columns since I am using a left outer join)

I think this could probably be simplified when reproduced, but I
wanted to show you everything I am doing.


Hi,

Rafik's away at the moment and I wanted to follow up with you about this.

First, a few minor notes on optimization and performance:
1. The first couple of data sets have filters which would be much better
served as hard-coded parameters to the data set. That way, they get
included as conditions in the query. The way you have it written with
filters, *all* rows will get pulled into the report, where BIRT will
process them one by one in memory, applying your filter to each one.
You'll get much better memory and time performance.
2. In the case where you're looking for a particular hard-coded state,
should you instead be using the STATE_GROUP? There are only three state
groups: OPEN_STATE, INCOMPLETE_STATES and CLOSED_STATES - they're sort
of "meta-states" into which all states fall.
3. The Links data set, even when you pass the parameters in as suggested
above, will still pull up all blocking links, regardless of whether they
are from unresolved tasks or some other work items. Instead, you may
wish to force BIRT to process the 1st data set first, and in its
onFetch, populate a javascript array with the work item IDs that are
returned; then supply this javascript variable to the links data set, so
you only pull in links, for example, whose source id is in that array.
This should drastically reduce the size of the data pulled in through
this data set.

But these don't affect the correctness of your report.

From the description of your problem, it's not clear to me what the
problem is - the two data sets you're joining together each have data in
them, but the resulting joint data set does not. Would it be possible to
attach this .rptdesign file (or a stripped down one that you don't mind
being publicly visible) to a work item? That would be the easiest way
for us to debug it.

Hope this helps.

james
RTC Reports Team Lead

Thanks for the suggestions James. I created a workitem and attached the .design file as you requested. I will definitely make the changes you suggested once I get this working.

Here is the workitem: https://jazz.net/jazz/web/projects/Rational%20Team%20Concert#action=com.ibm.team.workitem.viewWorkItem&id=93216

Your answer


Register or to post your answer.