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.
Thanks. - Kevin Zemanek |
7 answers
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 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 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? |
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.