Use Reportable REST API to obtain Child Work Item attributes based on Parent and Child attributes
BACKGROUND:
I need to retrieve two custom attributes from all work items of a certain type based on the State and Planned For of the parent items. I may also want to return attributes from the parent along with each child work item row. Note that the work item type of the children items (the rows I want to return - a custom type) is different than the work item types of the parents (defect, business need, etc.).
QUESTION:
Is it possible to achieve this using the REST API?
EXAMPLE output:
|
Planned For (based on Parent item)
|
State [or Resolution]
(based on Parent item)
|
Custom Attribute 1 (from child)
|
Custom Attribute 2 (from child)
|
Parent ID
|
Child ID
|
|
8.7 |
Waiting String
|
FRM_DEMO
|
1.2
|
64
|
102
|
|
8.6 |
Waiting String
|
FRM_CLST
|
1.13
|
25
|
93
|
|
8.6 |
Waiting ST
|
FRM_CLST
|
1.5
|
25
|
76
|
|
8.6 |
Waiting UAT
|
FRM_NOTE
|
1.9
|
13
|
85
|
Accepted answer
You can check out this jazz.net wiki for CCM reportable REST API:
https://jazz.net/wiki/bin/view/Main/ReportsRESTAPI
Please find a sample URL below. You can try it out and see if it gives the information you are looking for.
https://<server:port>/ccm/rpt/repository/workitem?fields=workitem/workItem/(id|summary|state/(name|group)|parent/*|children/*|customAttributes/*)
Comments
Thank you. I know about the REST wiki but thanks for making sure I did. While I look into your other suggestion, let me ask a follow-up question.
How does RRDI (https://jazz.net/library/article/1269) relate to these REST APIs? Is it the same thing but with a query design interface or is it different? Should I consider RRDI for what I am trying to do here?
How does RRDI (https://jazz.net/library/article/1269) relate to these REST APIs? Is it the same thing but with a query design interface or is it different? Should I consider RRDI for what I am trying to do here?
The REST API's access the CCM database directly. So any time you are using the REST API's, you are putting load on the CCM server. The REST API's are used by the "ETL" (extract/transform/load) jobs, that periodically query the CCM database to extract information from it, and then insert it into the RRDI database. One advantage of using the RRDI database for information gathering is that it does not put load on the CCM database.
Thanks. I think I need to hit the CCM database because I need real time information. It sounds like the RRDI is only updated periodically. I'll have to limit the amount of data I pull at a time to minimize impact to the CCM database. Thanks for the heads-up.
Comments
sam detweiler
Jun 21 '13, 9:12 a.m.after looking at all your queries about the Reportable Rest apis, I would say you could write an application that could assemble this table. but it will not be a single api call for sure. the links to the related workitems (parent/child) are not stored with the workitem data, but in a separate table. so, once u have the workitem ID's you would have to search the links table for links containing the workitem, then filter out if one end is parent of something, the other child of something.
I wrote a prototype birt report a while back, which proposed to show all workitems in other projects, linked to this project one which this project was dependent.
that took 3 or 4 full passes at the database. I didn't have motivation to redo this with Insight/RRDI reporting tools.
I would never have attempted this with raw XML results from the reportable apis.
Michael Taylor
Jun 21 '13, 12:34 p.m.Thank you for the comments and information. I'm not familiar with the BIRT report building tools. What language/method did you use there to make the multiple database calls? Was that capability built in to the BIRT GUI of that tool?
Regarding the need to make multiple API calls to get the information, please see the XML output listed in the following post. That output seems to be listing all the Parent and Child IDs from one API call. It even lists the two custom attributes I want to get at. Now I need to know how to get the actual values of the custom attributes.