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 (1133975) | 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.8k17) | 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.

permanent link
David Honey (1.8k17) | 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.

Your answer

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