Accessing RTC Timesheet Reports tables?
Hi all, I've have installed RTC 6.0.2 with DB2 database but am currently unsure about what the schema and tables names there are for RTC work item timesheets. We are aware that the RTC generated report "Personal Timesheet by Work Item" provides enough for most but this currently isn't enough as we use predefined and custom data to make use which means we need access the tables directly. We want to plug in additional data sources which will provide us with a complete up to date report of our timesheets for all our IT staff for the current working week. Is it possible to tell the schemas and tables for the reports because it is not available in main schemas which form the central data warehouse?
We are not looking to use any apis, just a BI solution no matter how complex as long as its possible. We are using DB2 web query
2 answers
hi Alexander,
If you want to receive answers read. i think, it is help to you
https://jazz.net/forum/questions/223347/time-tracking-in-rtc
If you want to receive answers read. i think, it is help to you
https://jazz.net/forum/questions/223347/time-tracking-in-rtc
Comments
The timesheet entries(MODEL.TIME_SHEET_ENTRY [TSE]) are related to work items(MODEL.WORK_ITEM [WI]) by links(LINKS.AUDITABLE_LINK [LINKS]) by
LINKS.SOURCE_REF_REFERENCED_ITM_TM_D = WI.ITEM_ID and LINKS.TARGET_REF_REFERENCED_ITM_TM_D = TSE.ITEM_ID
Sample SQL to ge the sum of time spent per work item:
SELECT SUM(ENTRIES.INTERNAL_TIME_SPENT) AS TOTAL_TIME_SPENT, LINKS2.SOURCE_REF_REFERENCED_ITM_TM_D AS ITEM_ID
FROM LINKS.AUDITABLE_LINK AS LINKS2
JOIN MODEL.TIME_SHEET_ENTRY AS ENTRIES
ON LINKS2.TARGET_REF_REFERENCED_ITM_TM_D=ENTRIES.ITEM_ID
JOIN MODEL.WORK_ITEM AS WORKITEMS
ON LINKS2.SOURCE_REF_REFERENCED_ITM_TM_D=WORKITEMS.ITEM_ID
GROUP BY LINKS2.SOURCE_REF_REFERENCED_ITM_TM_D
ORDER BY LINKS2.SOURCE_REF_REFERENCED_ITM_TM_D