Accessing RTC Timesheet Reports tables?

Alexander Brown (6●2) | asked Oct 18 '16, 11:04 a.m.
edited Oct 19 '16, 4:03 a.m.

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

Most liked answers ↑|Newest answers|Oldest answers

permanent link

anand krish (21●1) | answered Oct 18 '16, 11:26 a.m.

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

Comments

Alexander Brown commented Oct 18 '16, 11:42 a.m. | edited Oct 18 '16, 11:53 a.m.

Hi Anand, thanks for you response. Unfortunately this is not what we're looking for. We are looking for a db2 web query solution. We are able to use the ODS and CALM schema(RTC schema), which was able to help us produce a report similar to this but sometimes returned partially inaccurate data about time spent on work items during the day which isn't good enough. Thanks for your help anyways.

Alexander Brown commented Oct 18 '16, 11:44 a.m.

FYI: the inaccuracies were for only a very few work items but we expect 100% correctness.

Alexander Brown commented Oct 18 '16, 12:09 p.m.

We found the TIMESHEET table in the ODS schema but it was missing entries when compared to the actual RTC generated report. We also found this out from the TIMESHEET_LIVE_SNAPSHOT and WORKITEMS_SNAPSHOT snapshots found on the reports using the Birt tool.

permanent link

Luis Peregrina (181●1●4) | answered Oct 19 '16, 10:28 a.m.
JAZZ DEVELOPER

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

Comments

Alexander Brown commented Oct 24 '16, 4:13 a.m.

Hi Luis, could you please clarify what schemas/views/tables are to be replaced by placeholders MODEL,LINKS,SOURCE.etc. If possible can you provide me with the statement using default schema/views and tables, that'll be great as its quite possible that the data warehouse used by RTC is using the defaults. Sorry to be a pain, but in future posts directed towards me can you please use descriptive comments for code,queries as it helps and its also good practice.

Thanks

Luis Peregrina commented Oct 24 '16, 1:29 p.m.

JAZZ DEVELOPER

Sure Alexander, basically the schema "LINKS" with table "AUDITABLE_LINK" has the reference for both the work item (column "SOURCE_REF_REFERENCED_ITM_TM_D") and the timesheet entry (column "TARGET_REF_REFERENCED_ITM_TM_D"). Notice one column is "target" and the other is "source".

The previous sample sql query does a sum of total time spent (SUM(), added from all the time sheet entries for each work item) and the work item number. The query is using default schemas/tables for Derby, I'm not sure if it changes depending on the DBMS.

Luis Peregrina commented Oct 24 '16, 1:29 p.m.

JAZZ DEVELOPER

Relationships on a "join" operation are described by "SELECT * FROM [table1] JOIN [table2] ON [table1.column]=[table2.column];", this means that table1.column has a reference to table2.column. In the sample query I made, "ON LINKS2.TARGET_REF_REFERENCED_ITM_TM_D=ENTRIES.ITEM_ID" means "links2.target... is referenced in entries.ITEM_ID", so the value for the target column is the same as the entries' item_id column.

Alexander Brown commented Oct 24 '16, 5:38 p.m.

Thanks Luis, this is very useful. I'll be trying it out tomorrow morning (UK time). Thanks for all your help. i'll let you know if i get stuck.

It's all about the answers!

Related questions

Accessing RTC Timesheet Reports tables?

2 answers

Your answer