It's all about the answers!

Ask a question

Possible to create a Trend report plotting the changes of a RTC custom integer field over time ?

Christophe Lucas (8624344) | asked Oct 14 '13, 4:48 a.m.
edited Oct 14 '13, 4:48 a.m.
 We have a RTC custom work item type ('Project_Information') which contains a custom integer field ('Project_Spent_Budget').

Using a Reporting tool (not BIRT, not RRDI, not Insight) which accesses the RTC databases directly (RTC and DW), we want to create a Report which plots the trend/changes of the Project_Spent_Budget field over time.
So this Report needs to access each 'Project_Spent_Budget' field change, and plot a Report as per picture below.

Is this possible ? 
If Y, which exact DB table(s) should we look at to capture all the Project_Spent_Budget changes (date of change and new value) ?


One answer

permanent link
Christophe Lucas (8624344) | answered Oct 15 '13, 9:25 a.m.
I hands-on'ed this one, i.e. created 1 Project_Information work item, and changed the value (integer) of its Project_Spent_Budget field 3 times.

What I observed looking at the DB2 tables is as follows:
(1) In the DW database, RIODS.REQUEST table, 1 row is created for the Project_Information work item (identified by REQUEST_ID)
(2) In the DW database, RICALM.REQUEST_HISTORY table, 3 rows are created corresponding to the 3 changes to Project_Spent_Budget field (each change/row is identified by 1 REQUEST_HISTORY_ID, all have the same REQUEST_ID identified in (1))
(3) in the DW database, RICALM.REQUEST_HISTORY_INT_EXT, for the 3 rows corresponding to each above REQUEST_HISTORY_ID, I see in the NAME column the name of my changed Project_Spent_Budget field (*.workitem.attribute.Project_Information.Project_Spent_Budget), and in the VAL column the changed value of the field.

So that answers my original question.

Christophe Lucas commented Oct 15 '13, 9:34 a.m.

Just noticed that there is neither a 'Mark this answer as the accepted answer' option, nor a possibility to 'Like' your answer when you answered your own question... so just confirming here that the above answer did answer my original question :-)

Your answer

Register or to post your answer.