It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×42,837

question asked: Aug 25 '11, 7:09 a.m.
question was seen: 12,607 times
last updated: Jun 21 '12, 5:40 a.m.

Related questions

BIRT: joining Timesheet Time Code with Work Items

0
Piotr Aniola (3.7k●1●17●38) | asked Aug 25 '11, 7:09 a.m.
Hello all,

We are trying to create a BIRT report to display the time tracking
information of specific work items. We therefore want to join the
entries from the "Timesheet Time Code" table in the LIVE DATA SNAPSHOT
view with the work item information in the work item table.

Unfortunatly the work item forign ids in the "Timesheet Time Code"
table are not the same format as the ones in the work item table.

Is there any way to join the two tables? Does js scripting need to be involved?

Any help will be deeply appreciated.

18 answers

Most liked answers ↑|Newest answers|Oldest answers

0
permanent link
James Moody (3.3k●2●4) | answered Dec 06 '11, 12:43 p.m.
JAZZ DEVELOPER
I've just tried this on our production server, and have the following results:

1. Create a data source on LIVE_SNAPSHOT
2. Create a data set from that data source, on the com.ibm.team.links.AuditableLink table.
3. Under "Select Columns", pick sourceRef.referencedItem.itemId, and targetRef.referencedItem.itemId.
4. Under "Select Parameters", pick "name".
5. Under "Parameters", set the default value 'com.ibm.team.workitem.linktype.timeSheetEntry' under the "name" parameter.

Preview the data set. I see, for example, two links. For each, there is a sourceRef itemId (e.g. "_i1zMwCUkEeC8dNvDCg7T7A" in my case). There is also a targetRef itemId (e.g. "_7MEg0GOdEeC3XaTJDEUA1A"). I would have thought that one of these would be the itemId of the time sheet entry, and the other would be the itemId of the work item. However, I was unable to find a work item with an itemId of either of those, which I think is consistent with your results.

I will forward these results to the developer who wrote this support, and report back his findings.

Thanks,

james


Hi James,

thank you, you are right I found the "com.ibm.team.links.AuditableLink" table in LIVE DATA SNAPSHOT, however I still could not find any matching ids between the 2 tables. I tried all auditable links fields containing an id to join with the "com.ibm.team.workitem.TimeSheetEntry" itemId.

Regards,
Alexander

How can I access these as "auditable links" in a BIRT report?

Sorry for the delay, I have tracked down the developers who wrote this part of the REST API.

The link between the Timesheet and the Work Item is stored in the auditableLinks resource. You want links with the name "com.ibm.team.workitem.linktype.timeSheetEntry", which describe this relationship. Thus, in Reportable REST query terminology:

/rpt/repository/foundation?fields=foundation/auditableLink/*/*

james
RTC Reports Team Lead

Any news on this?

Hello James,

thanks for your response. Unfortunatly I have a problem now with running BIRT in my environment.

The problem is that "com.ibm.team.workitem.TimeSheetEntry" table in the LIVE SNAPSHOT view does no contain any direct reference to the related work item number. So there must be somewhere another table with a forign key to perform the join.

Can you please help us to find the correct relation?

Thanks,
Alexander


Hello all,

We are trying to create a BIRT report to display the time tracking
information of specific work items. We therefore want to join the
entries from the "Timesheet Time Code" table in the LIVE DATA SNAPSHOT
view with the work item information in the work item table.

Unfortunatly the work item forign ids in the "Timesheet Time Code"
table are not the same format as the ones in the work item table.

Is there any way to join the two tables? Does js scripting need to be involved?

Any help will be deeply appreciated.


Can you provide more information on exactly which tables you are using, and which columns you are looking at for ids? The work item table exposes both the id (e.g. "1") and the itemid (a UUID), so I was expecting that the timesheet table would use one of those.

Thanks,

james
RTC Reports Team Lead
0
permanent link
Alexandra Bellogini (26●1●1) | answered Dec 12 '11, 10:36 a.m.
Hi James,
any update from the developer?
Thanks
Alexandra
0
permanent link
James Moody (3.3k●2●4) | answered Dec 12 '11, 11:02 a.m.
JAZZ DEVELOPER
Hi James,
any update from the developer?
Thanks
Alexandra


Sorry, I don't have an update yet. I'll send another query.

james
0
permanent link
Cesar Sasaki (511●2●89●129) | answered Dec 19 '11, 12:03 p.m.
Hi James,
any update from the developer?
Thanks
Alexandra


Sorry, I don't have an update yet. I'll send another query.

james

Hi

I have managed to unite the WorkItem with the TimeSheetEntry. Is that what you are looking for.

As Mr. Moody said you must find the Link in the "Common Snapshot" under the table "LINKS". There is a Link that is called

"com.ibm.team.workitem.linktype.timeSheetEntry"

Filter that with the parameter or the filter.

Then the Source Item id is the WorkItems Item Id. And the Target Item Id is the timesheet entry Item Id.
0
permanent link
James Moody (3.3k●2●4) | answered Dec 22 '11, 4:38 p.m.
JAZZ DEVELOPER
Hi James,
any update from the developer?
Thanks
Alexandra


Sorry, I don't have an update yet. I'll send another query.

james

Hi

I have managed to unite the WorkItem with the TimeSheetEntry. Is that what you are looking for.

As Mr. Moody said you must find the Link in the "Common Snapshot" under the table "LINKS". There is a Link that is called

"com.ibm.team.workitem.linktype.timeSheetEntry"

Filter that with the parameter or the filter.

Then the Source Item id is the WorkItems Item Id. And the Target Item Id is the timesheet entry Item Id.

What Cesar suggests is consistent with what the developer has told me; the source item id should be the uuid of the work item, and the target item id is the uuid of the timesheet entry. If you have trouble making this work, I'd suggest either contacting IBM support or opening a work item where we can debug it further.

james
CLM Reporting technical lead
0
permanent link
marta olechowska (33●6●7) | answered Dec 23 '11, 4:52 a.m.
Hello all,
Could you put step by step instruction how to track open work item in the project?

I have the same problem with finding DATE and TIME in tables and need to create a report in BIRT :(:(

I have tried to find in "Common Snapshot" table called LINKS but it doesn't exist there:
As Mr. Moody said you must find the Link in the "Common Snapshot" under the table "LINKS". There is a Link that is called

"com.ibm.team.workitem.linktype.timeSheetEntry"

Filter that with the parameter or the filter.

Then the Source Item id is the WorkItems Item Id. And the Target Item Id is the timesheet entry Item Id.

Regards,
Marta
0
permanent link
James Moody (3.3k●2●4) | answered Jan 09 '12, 12:58 p.m.
JAZZ DEVELOPER
Hi James,
any update from the developer?
Thanks
Alexandra


Sorry, I don't have an update yet. I'll send another query.

james


Just to follow up, the developer told me the same thing that Cesar says in this thread, and that I posted earlier.

james
0
permanent link
Alexander Schinko (355●1●18●12) | answered Jun 21 '12, 5:40 a.m.
edited Jun 21 '12, 5:40 a.m.
According the support, BIRT wouldn't support joints of huge tables like the "com.ibm.team.links.AuditableLink" table (>then 10000 rows) out of the box. There must be some scripting involved.

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.