1 Task and Activity-related
- 1.1 Task-Related
  - 1.1.1 All open tasks for a specified user
  - 1.1.2 Number of created, completed, and open tasks for each process definition
- 1.2 Activity-Related
  - 1.2.1 Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month

Task and Activity-related

This article offers examples for task and activity related analyses with the dashboard.

Task-Related

All open tasks for a specified user

SELECT
   ident.LASTNAME AS "Lastname",
   ident.FIRSTNAME AS "Firstsname",
   ident.IDENTITYNAME AS "Username",
   task.TASKNAME AS "Task",
   inst.INSTANCENAME AS "Instance name",
   inst.DEFINITIONNAME AS "Processname"
FROM view_activity act, view_task task, view_identity ident, view_instance inst
WHERE act.ID = task.ACTIVITYID
   AND task.ACTORID = ident.ID
   AND act.ACTIVITYEND IS NULL
   AND task.TASKEND IS NULL
   AND inst.ID = act.INSTANCEID
   AND inst.ISARCHIV = '0'
   AND inst.INSTANCEEND IS NULL
   AND ident.FIRSTNAME = '<specific_user_firstname>'
   AND ident.LASTNAME = '<specific_user_lastname>'

Number of created, completed, and open tasks for each process definition

SELECT
  inst.DEFINITIONNAME AS "Definition name",
  SUM(1) AS "Number of created tasks",
  Sum(CASE WHEN task.TASKEND IS NOT NULL THEN 1 ELSE 0 END) AS "Number of completed tasks",
  SUM(CASE WHEN task.TASKEND is NULL AND task.ISARCHIV = 'false' THEN 1 ELSE 0 END) AS "Number of open tasks"
FROM view_task task
  INNER JOIN view_instance inst ON task.INSTANCEID=inst.ID
  GROUP BY inst.DEFINITIONNAME

Activity-Related

Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month

SELECT
    CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')) AS Month,
    act.activityName AS "Activity",
    ROUND(AVG(24*ROUND(to_number(to_date(to_char(act.activityEnd, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(act.activityStart, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Average cycle time",
    ROUND(MIN(24*ROUND(to_number(to_date(to_char(act.activityEnd, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(act.activityStart, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Min cycle time",
    ROUND(MAX(24*ROUND(to_number(to_date(to_char(act.activityEnd, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(act.activityStart, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Max cycle time"
FROM view_activity act
LEFT JOIN view_instance inst ON act.instanceId=inst.id
WHERE act.activityEnd IS NOT NULL  
  AND act.activityStart IS NOT NULL
  AND act.activityType IN ('K','S')
  AND inst.definitionName = '<name_of_process_definition>'
GROUP BY CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')), act.activityName
ORDER BY CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm'));

Documentation EN

Oracle Analyses related to tasks and activities

Analytics

Task and Activity-related

Task-Related

All open tasks for a specified user

Number of created, completed, and open tasks for each process definition

Activity-Related

Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month

Related content