It's all about the answers!

Ask a question

Use Reportable REST API to obtain Child Work Item attributes based on Parent and Child attributes


Michael Taylor (7855361) | asked Jun 19 '13, 11:36 a.m.

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

 


Comments
sam detweiler commented 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 commented 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.

Accepted answer


permanent link
Kot T. (1.5k11219) | answered Jun 19 '13, 2:53 p.m.
JAZZ DEVELOPER
Hi Michael,

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/*)
Michael Taylor selected this answer as the correct answer

Comments
Michael Taylor commented Jun 19 '13, 3:12 p.m.

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?


Michael Taylor commented Jun 20 '13, 10:54 a.m.

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?


Geoffrey Clemm commented Jun 20 '13, 2:26 p.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

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.


Michael Taylor commented Jun 23 '13, 7:53 p.m.

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.

One other answer



permanent link
sam detweiler (12.4k6175200) | answered Jun 20 '13, 11:09 a.m.
Also be careful on heavy queries and reports to the CCM database. these can/will cause performance issues on realtime transactions. Workitems open slower, plans load slower etc..


Comments
Michael Taylor commented Jun 20 '13, 12:06 p.m.

Thanks for the observation and warning!

Your answer


Register or to post your answer.