Hi
I am trying to create a report on work item State Aging using report studio. Our requirement is to get "Days in State " in hrs or get a report to show the value of aging in hours as my request state changes within a day.
I have created a report for a particular WorkItem State aging i.e., report to know how many hours,minutes the state was there before transition.
Example :
New : 12 minutes 50 seconds 469 milliseconds
Inprogress : 1 hour 37 minutes 31 seconds 930 milliseconds
Below is the procedure I tried to achieve and got stuck at a point.Need suggestion on this
State Tranistion is as below
New -> InProgress
InProgress -> complete
complete -> closed
Closed -> reopened
reopened -> Inprogress
Inprogress -> complete
Process:
In a Report page, values from ODS are used as below
Request Area -> Request -> Project Name
Request Area -> Request -> Name
Request Area -> Request State History -> Request state
Request Area -> Request State History -> Last Updated
Request Area -> Request State History -> Request state ID
Similarly created QUERY2 considering the values as below
Request Area -> Request -> Project Name
Request Area -> Request -> Name
Request Area -> Request State History -> Pevious Request state
Request Area -> Request State History -> Last Updated
Request Area -> Request State History -> Previous Request state ID
Created a Join Between Query1 and Query2 where Request State ID = Previous Request State ID and Lastupdate(previous request state) - Lastupdate(request state)
Where is showing the needed output as below.
But when the workitems undergo another loop in the workflow.It is calculating the earlier timing also.Let me make it understand clearly with a example
With respect ot above condition,calculation happens this way.
Request State ID = Previous Request State ID and Lastupdate(previous request state) - Lastupdate(request state)
In Progress : Feb 25, 2015 11:10:41 AM - Feb 25, 2015 11:01:25 AM = 9 minutes 15 seconds 720 milliseconds
In progress state in a second loop : Feb 25, 2015 12:38:57 PM - Feb 25, 2015 12:31:15 PM = 7 minutes 41 seconds 855 milliseconds
These is what we expected as a output.But the below calculation is happening. How can we avoid this.
(lastupdate of previous request in secondloop) Feb 25, 2015 12:38:57 PM - Feb 25, 2015 11:01:25 AM(Lastupdate of request state in 1st loop)
Marked as Yellow in attachment.
How can I avoid the above mentioned of miscalculation .Can Anyone help me in this?