It's all about the answers!

Ask a question

Using Work Item Id as parameter for a data set

Matthias Buettgen (23612332) | asked Jun 28 '12, 8:53 a.m.

I'm still struggling with the Jazz Data Source related to Work Items Ids. Within a data set it's possible to create a kind of SQL in clause on string fields like WORKITEM_ITEMID. This works as expected and helped me many times in the past designing my reports. 
Now I've the problem that I need to define a report based on  WC_STRING_EXT which contains the Work Item Id (WI_ID) but no not the item id. As an input parameter I've got a couple of Work Item Ids but it's obviously not possible to create an IN-CLAUSE on integer fields. Am I right with this or is there a way which I didn't really see here?

Thanks for your help 

Accepted answer

permanent link
James Moody (3.3k24) | answered Jun 29 '12, 2:42 p.m.
Hi Matthias,

Unfortunately you are correct, you can't use the same trick to use the in-style clause with integer fields. Without knowing more about your report I don't know for sure, but often in cases like this where we need to fetch a number of work items by name/number/id, we use a table embedded in a table (in the report design) kind of like a for loop - the outer table produces a row for each work item, the inner table calls WC_STRING_EXT for that particular row, passing the id for that row as a parameter. You can hide the table from the output, and store the results of each row into a javascript array. Just a thought.

Matthias Buettgen selected this answer as the correct answer

Matthias Buettgen commented Jul 05 '12, 2:28 a.m.

Hi James,

Thanks for the information, I think this seems to be a good work-around. I'll give it a try.


Matthias Buettgen commented Aug 07 '12, 11:16 a.m.

Hi James,

I've implemented the report related to your proposal which means I've created a dataset based on a work item query and use the resultset's workitem id [WI_ID] as an input parameter for WC_STRING_EXT. When I execute the report in the RTC Full Client all workitem entries are fetched from WC_STRING_EXT as expected and the report shows the expected result. Once the report has been deployed to server a couple of workitems in WC_STRING_EXT are skipped which means they are not fetched from the database. I don't see any pattern related to that behavior. What could be the reason for that because usually I would have expected the missing workitems in the Full Client and not on the server due to the maximum number of row limitation.

Many thanks for your help.


Vladimir Amelin commented Aug 08 '12, 5:49 a.m.

We've seen this kind of behaviour when work items are filtered as "archived" (for example, being planned for archived iteration) by default. Have you tried to turn on "include archived" option in web client?

Matthias Buettgen commented Aug 08 '12, 11:41 a.m.

Hi Vladimir,

Yes that's the reason for the strange behavior We recognized that work items are excluded in case the iteration has been archived. From my point of view would this make sense if all work items are affected, but our query which is used as a base for WC_STRING_EXT returns about 81 work items. 20 of them are planned for an archived iteration, but only 3 of them are not considered by the resultset of WS_STRING_EXT and we don't find any pattern why this happens. On the other hand it should be questioned if historical data should be excluded from a report resultset due to the fact the corresponding iteration has been archived. If you run a simple query in the RTC Client they are still visible and could be used, e.g. in Excel Export to calculate certain things externally. Last but not least they are also not visible in the LIVE Data neither in the LIVE_WORKITEM_CNT nor in

Did you recognize the same behavior that some work items are shown and others not?

Regards Matthias

Vladimir Amelin commented Aug 08 '12, 2:30 p.m.

I've met notes about different behavior when using non-html output:

I think it's defect if not fixed yet.

We encourage users to check "include archived" always on to ensure data are filtered in report only. So it's hard to say if the issue is reproduced in 3.0.1 latest fixpack or 4.0 and which formats are affected. I see why such feature was introduced in 3.0.1, additional pre-filtering can help if you have a lot of non-actual (closed, archived etc) work items. But the solution is not transparent for users and cause a lot of questions while testing and generating reports. I believe we have a large-scaled repository with 100+project areas, 1500 users and about 80k work items so far. And still this check box brings more pain than ease. Maybe we should send a note to @jmoody and @rjaouani to reconsider this feature or at least turn it off by default.

Your answer

Register or to post 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.