Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

Accessing RTC Timesheet Reports tables?

Questions
Tags
Users
Badges
Alexander Brown
(6●1●2) 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

0 votes

2 answers
4,586 views
0 votes

2 answers

Permanent link
anand krish
(21●4) 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

0 votes

Comments
Alexander Brown
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
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
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
JAZZ DEVELOPER (181●1●4) Oct 19 '16, 10:28 a.m.
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

0 votes

Comments
Alexander Brown
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
JAZZ DEVELOPER Oct 24 '16, 1:29 p.m.

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
JAZZ DEVELOPER Oct 24 '16, 1:29 p.m.

 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
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.

Your answer

Register or log in 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.

Ask a question
Guidelines
FAQ
Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 10,938
× 84
× 18
× 11

Question asked: Oct 18 '16, 11:04 a.m.

Question was seen: 4,586 times

Last updated: Oct 24 '16, 5:38 p.m.

Confirmation Cancel Confirm