How to create a report that calculates duration

[kartikey]

Hi Everyone,

I want to create a report that calculates how long has a story(rm_story) state been in work in progress, and i wish to calculate it against current date , i.e. i want to group by suppose X number of stories that have been in Work in progress since 3 days till today, and suppose Y number of stories that came to Work in progress and are still in work in progress till today since 2 days?

Can you please help me create the same?

Thanks,
Kartikey

[Omkar Mone]

You can look to have Metric Definition and create a DB view on Metric Instances to capture these. For more, you can look at this thread once - https://www.servicenow.com/community/spm-forum/how-to-track-time-for-a-story/m-p/1004620/page/2

[kartikey]

Hi @Omkar Mone ,

the issue is that Metrics are created suppose once the state has been changed, it would calculate the time once the state has been changed from 'Work in progress', i want to calculate the time even when the state is in Work in progress comparing it with current date, i.e. it would be dynamic , it would keep on changing with each passing day(Till the record is in work in progress state)

Thanks,
kartikey

[Omkar Mone]

Hello @kartikey ,

Then you I would suggest to create a new field which stores the date when the state was changed to WIP and compare it with the current date every day via scheduled job. Does this help?