Ageing report for custom application

[Prudhvi Raj Y]

I have a requirement where we have a record producer which creates a request on the custom table, next approval will get attached, once approved few child tasks will get created and each child task will get approvals attached. Once all the child tasks approved workflow goes back to the main request and attach another approval. In this process approver can assign back the request to requestor for additional clarifications which will change the status of the request.

Now the request is to pull the request of the Main request and child tasks to see how long the request was in each state. Is there a way to set-up the logic to create the report.

Thanks in advance.

Prudhvi 

[JackieZhang]

you can create metric definition for both mainprocess and sub-task. then you can create report from metric_instance table. 

[G Ponsekar]

Hi @Prudhvi Raj Y ,

Here's how you can approach setting up this reporting:

1. Metric definitions

• For the main request (custom table):
  • Create a metric definition on your custom table specifically for the "State" field.
  • Configure this metric as a "Field value duration" type to track the duration each time the state changes.
  • Give it a descriptive name like "Custom Request State Duration".
  • This metric will create records in the metric_instance table, recording the start and end times and the duration for each state change of your custom request.
• For child tasks:
  • If your child tasks are on a table that extends the task table, you can utilize the existing "Task State Duration" metric definition (if available).
  • If not, create a new metric definition for the state field of your child task table, similar to how you set up the metric for your custom request. 

2. Reporting

• Create a new report:
  • Navigate to Reports > Create New.
  • Data Source: Select the metric_instance table as the source for your report, as it holds the duration data captured by the metrics.
• Filters:
  • Filter by Definition: Filter the report to include only the metric definitions you created (e.g., "Custom Request State Duration" and "Task State Duration").
  • Filter by ID: You can also filter by the ID field which identifies the specific request or task for which the metric was gathered.
  • Timeframe: Utilize report ranges or scripted date/time conditions to analyze data within specific timeframes (e.g., past week, last business quarter).
• Report type and visualization:
  • List report: To view a detailed list of state changes and their durations for each request or task.
  • Bar chart or pie chart: To visualize the average time spent in each state
• Group by and aggregation:
  • Group by: Group the report by the Definition or Value fields to analyze data for each state duration metric.
  • Aggregation: Use "Average" or "Sum" to calculate average or total duration spent in each state.
• Columns: Include relevant columns like Definition, Value, Duration, Start, End, and the ID to provide contextual information about the state changes.

If I could help you with your Query then, please hit the Thumb Icon and mark as Correct !!

Thanks, GP

[Prudhvi Raj Y]

Thanks @G Ponsekar , Let me give a try and update you on the output.

[Prudhvi Raj Y]

Hi @G Ponsekar ,

I have created the metric definition but when I change the status of the request metric instance is not getting created, is there something missing?