It's all about the answers!

Ask a question

How Do I Isolate The History of Changes to Specific RTC Work Item Custom Fields in RRDI?

Mike Leipe (346) | asked Jul 17 '14, 10:51 a.m.
I'm using RRDI, specifically Report Studio, to create reports from a heavily customized RTC-based ticketing system.

I'm able to expose all of my custom attributes in Crosstab and List reports, etc.  Now I want to look at the history of my work items.

What I'm working on right now is a report that will tell me how many tickets were assigned to a specific team on a given day.  The assigned-to team is a custom string attribute and so I'm looking for a list of the changes to this attribute across multiple work items on a given day.  Put more simply, I want to be able to determine when this attribute's value has changed.  Once I can do that I can filter, etc. to get the report I want.

Here's what I've done, and it's not working for me.

My theory going in was that I could isolate my custom string attribute by filtering on the appropriate "Name" field in the Request History String Extension table.  I'd expose this in a query and in the same query I'd expose the Request History ID field.

Next step was to create a query from the Request History table that shows me the Request History ID and the Record Date Time.

I'd then join these two queries on the Request History ID field and the result would be a list of the changes to the custom string attribute.

Unfortunately that did not work out and I think I know why.  What I got was multiple records for each work item, each displaying a date and the value of the custom string attribute on that date, whether it changed on that date or not.  My gut says that this is because the Request History table is feeding me a list of changes to *any* attribute of the work item, and by joining that with my Request History String Extension table I'm simply exposing the value of my specific custom attribute on any date on which *any* change to the work item occurred.

So, back to my question:  how can I determine on which date the value of a specific custom string attribute changed?


Accepted answer

permanent link
Jackie Albert (1.6k14946) | answered Jul 17 '14, 11:38 a.m.

Hi Mike,

I've done something similar.  You're on the right track using the Request History String Extension and Request History. 

I used Request History String Extension and Request History, joined into what we'll call [Query 1], making sure to include Request History.Prev Request History ID.   Then I created [Query 2] also with Request History String Extension and Request History,  and joined [Query 1].Prev Request History ID =  [Query 2].Request History ID,  and added a filter where  [Query1].Value  !=  [Query2].Value.

Essentially, this looks through the Request History changes in order and finds the ones where the custom attribute value is different between the two... which will identify the date the value changed in the 'Record Date Time' field.

Hope this wasn't too confusing  :)

Mike Leipe selected this answer as the correct answer

Mike Leipe commented Jul 17 '14, 1:49 p.m.

Hah!  I knew that that "Prev Request History ID" field had to be useful for something and I was starting to suspect that this was it!

It's working brilliantly for me now - thanks very much for your quick response!


Your answer

Register or to post your answer.