Generate Report using metric and databse view

[servicenowt7048]

How can I calculate the duration between specific state transitions in a record, such as the time spent from 'Open' to 'Work in Progress' and from 'Work in Progress' to 'Closed'? I want to achieve this using Metric Definitions  Any guidance or best practices would be appreciated."
Note: avoid creation of field in form for keep the value of duration.

Thanks in advance

[Dr Atul G- LNG]

Hi @servicenowt7048 

Best is use PA else metric on state filed and the metric and table DB view. 

https://INSTANCENAME.service-now.com/now/platform-analytics-workspace/kpi-library

[Mark Manders]

You can create a simple metric definition for that (if not already OOB there, depending on the table):

Create a new record on the metric_definition table.

Select the table and the field (state). Add a name and select 'field value duration' as type. 

That's it. Your metric definition will start creating metric_instance records for every change in state on the records for your table. 

This screenshot is from the OOB one for Case:

[servicenowt7048]

Thanks for your response
I need to generate a report to calculate the duration specifically between the 'New' and 'Work in Progress' states for records. However, I don't want to create Metric Instance records for every state change, as Metric Definitions typically capture all state transitions. How can I restrict Metric Instances to only track the duration from 'New' to 'Work in Progress'? Any guidance would be helpful."

[Mark Manders]

Why don't you want metric instances for the other state changes? When you start reporting on 'new to in progress' someone will come along asking about 'work in progress to resolved'. 

The metric records aren't in the way, so you can just report on the new to work in progress ones and leave the rest out. 

If you are really set on just getting these (and really, trust me, you will be asked to create others), you will need to script to get the 'start' on 'new' and 'end' on 'work in progress'.