Query for parent and children?

I want to create a query for one or more work items and their children. I then want to export to CSV. I have been unable to determine how to do either one (stuck on the 2nd because I am stuck on the 1st). Any hints would be appreciated.

The exporting support is available in RTC 1.0.1. To do it from the eclipse client:
Eclipse: File>Export>Work Items, the attributes to be exported can be specified on the next wizard page.

To query for work items, you can right click on Work Items in Eclipse and create a new query. Please see this page for help getting started using queries:
https://jazz.net/help/rational-team-concert/1.0.1/index.jsp?topic=/com.ibm.team.workitem.doc/topics/t_finding_work_items.html

This only works for the items themselves, not the children. RTC does not offer any way to unravel the recursive structures
I have had to write custom sql to do it

Brian,

Thanks. I know how to create queries and I know how to export to CSV. What I haven't been able to figure out is how to use the query editor to return a work item and its children. Apparently this is not possible. Rats.

Thanks anyway.

- Kevin

Is there already a work item open for this? We've been struggling with the lack of this type of query support for some time.

We have created custom fields on child work items to represent fields of the parent work items and *manually* fill these in. This is a royal pain but this is the only way we can get the desired query results.

Has anyone written a save action which will does this type of thing (copy parent fields to child fields upon save) and if so, would you be willing to share. I realize it would require customization.

I did add one similar as an enhancement https://jazz.net/jazz/web/projects/Rational%20Team%20Concert#action=com.ibm.team.workitem.viewWorkItem&id=72447, but I ended up writing SQL to pull what we may need. It generates a level based result(deeper than I hoped anyone might ever use); replacing the id changes the starting point as rtc_wi.id=1615
It works on 1.0.1 on DB2

with RTC_ARTIFACTS (ID,ARTIFACT_ID,ARTIFACT_LABEL,PARENT_ID,LEVEL_ID) AS (

select
RTC_WI.id ID,RTC_WI.item_id ARTIFACT_ID, RTC_WI.summary ARTIFACT_LABEL,'' PARENT_ID,
0 as LEVEL_ID
from
model.work_item RTC_WI
where

rtc_wi.id=1615


union all

select
RTC_WI.id ID, RTC_WI.item_id ARTIFACT_ID, RTC_WI.summary ARTIFACT_LABEL,RTC_links.Target_REF_REFERENCED_ITM_TM_D PARENT_ID,
LEVEL_ID+1 as LEVEL_ID
from
model.work_item RTC_WI , LINKS.AUDITABLE_LINK RTC_links, RTC_ARTIFACTS


where rtc_Links.name_col='com.ibm.team.workitem.linktype.parentworkitem' and RTC_links.source_REF_REFERENCED_ITM_TM_D =RTC_WI.item_id and RTC_links.target_REF_REFERENCED_ITM_TM_D =RTC_ARTIFACTS.ARTIFACT_id

)
select
L1.ARTIFACT_LABEL Level1,
COALESCE(L2.ARTIFACT_LABEL,'') Level2,
COALESCE(L3.ARTIFACT_LABEL,'') Level3,
COALESCE(L4.ARTIFACT_LABEL,'') Level4,
COALESCE(L5.ARTIFACT_LABEL,'') Level5,
COALESCE(L6.ARTIFACT_LABEL,'') Level6,
COALESCE(L7.ARTIFACT_LABEL,'') Level7,
COALESCE(L8.ARTIFACT_LABEL,'') Level8,
COALESCE(L9.ARTIFACT_LABEL,'') Level9,
COALESCE(L10.ARTIFACT_LABEL,'') Level10,
COALESCE(L11.ARTIFACT_LABEL,'') Level11,
COALESCE(L12.ARTIFACT_LABEL,'') Level12,
COALESCE(L13.ARTIFACT_LABEL,'') Level13,
L1.ARTIFACT_ID Level1_ID,
COALESCE(L2.ARTIFACT_ID,'') Level2_ID,
COALESCE(L3.ARTIFACT_ID,'') Level3_ID,
COALESCE(L4.ARTIFACT_ID,'') Level4_ID,
COALESCE(L5.ARTIFACT_ID,'') Level5_ID,
COALESCE(L6.ARTIFACT_ID,'') Level6_ID,
COALESCE(L7.ARTIFACT_ID,'') Level7_ID,
COALESCE(L8.ARTIFACT_ID,'') Level8_ID,
COALESCE(L9.ARTIFACT_ID,'') Level9_ID,
COALESCE(L10.ARTIFACT_ID,'') Level10_ID,
COALESCE(L11.ARTIFACT_ID,'') Level11_ID,
COALESCE(L12.ARTIFACT_ID,'') Level12_ID,
COALESCE(L13.ARTIFACT_ID,'') Level13_ID,
L1.ID Level1_short_ID,
COALESCE(L2.ID,0) Level2_short_ID,
COALESCE(L3.ID,0) Level3_short_ID,
COALESCE(L4.ID,0) Level4_short_ID,
COALESCE(L5.ID,0) Level5_short_ID,
COALESCE(L6.ID,0) Level6_short_ID,
COALESCE(L7.ID,0) Level7_short_ID,
COALESCE(L8.ID,0) Level8_short_ID,
COALESCE(L9.ID,0) Level9_short_ID,
COALESCE(L10.ID,0) Level10_short_ID,
COALESCE(L11.ID,0) Level11_short_ID,
COALESCE(L12.ID,0) Level12_short_ID,
COALESCE(L13.ID,0) Level13_short_ID
FROM
(select ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=0
) L1 Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=1
) L2 on L2.PARENT_ID=L1.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=2
) L3 on L3.PARENT_ID=L2.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=3
) L4 on L4.PARENT_ID=L3.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=4
) L5 on L5.PARENT_ID=L4.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=5
) L6 on L6.PARENT_ID=L5.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=6
) L7 on L7.PARENT_ID=L6.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=7
) L8 on L8.PARENT_ID=L7.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=8
) L9 on L9.PARENT_ID=L8.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=9
) L10 on L10.PARENT_ID=L9.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=10
) L11 on L11.PARENT_ID=L10.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=11
) L12 on L12.PARENT_ID=L11.ARTIFACT_ID Left outer join (select
ID,
ARTIFACT_ID,
ARTIFACT_LABEL,
PARENT_ID,
LEVEL_ID
from
RTC_ARTIFACTS
where
LEVEL_ID=12
) L13 on L13.PARENT_ID=L12.ARTIFACT_ID

klzemanek wrote:

I want to create a query for one or more work items and their
children. I then want to export to CSV. I have been unable to
determine how to do either one (stuck on the 2nd because I am stuck
on the 1st). Any hints would be appreciated.

Thanks.

- Kevin Zemanek

Kevin,

You can't add Parent or Children to your query results columns from the
Eclipse client, but you can from the web client. Create your query
there, then run it from the Eclipse exporter wizard.

There's a bug in 1.0.1 which may result in truncated data when
exporting, or a "Gone!" error message in the web client if the Parent
column is added to the query. Make sure your server is running 1.0.1.1
to fix that.

Mark Ingebretson

klzemanek wrote:

I want to create a query for one or more work items and their
children. I then want to export to CSV. I have been unable to
determine how to do either one (stuck on the 2nd because I am stuck
on the 1st). Any hints would be appreciated.

Thanks.

- Kevin Zemanek

Kevin,

You can't add Parent or Children to your query results columns from the
Eclipse client, but you can from the web client. Create your query
there, then run it from the Eclipse exporter wizard.

There's a bug in 1.0.1 which may result in truncated data when
exporting, or a "Gone!" error message in the web client if the Parent
column is added to the query. Make sure your server is running 1.0.1.1
to fix that.

Mark Ingebretson

Mark is currect. I bumped into the "Gone!" error message on some jazz 1.0.1 servers. If it's not appropriate to update server to 1.0.1.1, is there a workaround to export parent/children relationsip?