It's all about the answers!

Ask a question

Report Builder : How to do reporting between user preferred 2 states in a workitem

Chandan M B (1132861) | asked Dec 07 '21, 3:45 a.m.


My scenario is - Assume we have Change Request workitem
 State workflow is  New -> Analyze
                              Analyzed -> Start Working
                              In-progress -> Review
                              In Review -> Complete

Now with the data warehouse I am able to get the days of the workitem took for state transition.
My use case is something I am interested to do get number of days it took Analyzed -> Review (State 1 -> State m)
or (State m -> State n).
Any help would be appreciated.


2 answers

permanent link
David Honey (1.4k17) | answered Dec 07 '21, 6:11 a.m.

I don't think this is possible in Report Builder. It uses RIDW.VW_REQUEST which is a view. That view contains columns such as CREATION_DATE , CLOSED_DATE , SUBMIT_DATE , RESOLVED_DATE , ASSIGNED_DATE , DUPLICATE_DATE , and ACTIVATE_DATE . So all you know is the last date when a work item went into the corresponding state. You won't know from which state it went into that state. And if the state moves back and forth between a pair of states, you won't know the cumulative time it was in a particular state. Not all of these view columns are defined as meta properties in Report Builder, and I do not think it is possible for a customer to add them. I think the best you can do is add a custom expression using a database vendor specific date difference function, such as DATEDIFF on MS SQL server.

permanent link
David Honey (1.4k17) | answered Dec 09 '21, 1:36 p.m.

I found the following might be a start. For a Data Warehouse data source in Report Builder, you can select Work Item and then add a traceability link to Work Item Status History:

Then in the Format Results tab, add Status and Days in State for the Work Item Status History:

That will give you a report that looks like this:

Chandan M B commented Dec 09 '21, 1:41 p.m.

Thanks David.

But user wants to know based on dynamic input of a status.
Example : In your case 
 Status 1 - Closed
 Status 2 - New 

Then I have to sum New + In Progress + Closed = Total

David Honey commented Dec 10 '21, 5:37 a.m.

What do you mean by "dynamic input of a status".
Given the above example, please give a specific example of that you're looking for in the report result.

Your answer

Register or to post your answer.