Process-Related
This article offers examples for instance and definition related analyses with the dashboard.
Instance-Related
Number of started instances per month
SELECT concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month, SUM(1) AS Count FROM view_instance inst GROUP BY concat(DATEPART(yy, inst.creationTime), '-', DATEPART(mm, inst.creationTime))
Average number of started instances per day per month (22 working days a month)
SELECT concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month, ROUND(SUM(1)/22,1) AS "Average per day" FROM view_instance inst GROUP BY concat(DATEPART(yy, inst.creationTime), '-', DATEPART(mm, inst.creationTime))
Number of started and completed instances for each definition
SELECT inst1.definitionname AS Prozess, COUNT(DISTINCT(inst1.id)) AS 'Number of started instances', COUNT(DISTINCT(inst2.id)) AS 'Number of completed instances' FROM view_instance inst1 LEFT JOIN view_instance inst2 ON inst1.id=inst2.id AND inst2.instanceEnd IS NOT NULL INNER JOIN view_activity act ON act.instanceid=inst1.id GROUP BY inst1.definitionname;
Number of started, completed, aborted and running instances for each process definition
SELECT definitionName AS "Definition name", SUM(1) AS started, SUM(CASE WHEN isArchiv = 'false' AND instanceEnd is NULL THEN 1 ElSE 0 end) AS running, SUM(CASE WHEN isArchiv = 'true' And instanceEnd is NULL THEN 1 ELSE 0 END) AS aborted, SUM(CASE WHEN instanceEnd is NOT NULL THEN 1 ELSE 0 end) AS completed FROM view_instance GROUP BY definitionName
Activities, tasks and assigned users of running instances
SELECT
inst.instanceName AS 'Instance',
inst.definitionName AS 'Process',
STRING_AGG(act.activityName, ', ') WITHIN GROUP (ORDER BY act.activityName) AS "Activity",
STRING_AGG(task.taskName, ', ') AS "Tasks",
CASE WHEN ident.ID IS NULL THEN ident2.IDENTITYNAME ELSE CONCAT(ident.FIRSTNAME, ' ', ident.LASTNAME,' (', ident2.IDENTITYNAME,')') END AS "Username"
FROM view_instance inst
INNER JOIN view_activity act
ON act.instanceid = inst.id
AND act.activityStart IS NOT NULL
AND act.activityEnd IS NULL AND act.activityType = 'K'
INNER JOIN view_task task
ON task.activityId = act.id
LEFT JOIN view_identity ident
ON task.actorId = ident.id
LEFT JOIN view_identity ident2
ON task.POOLEDACTORID = ident2.ID
WHERE inst.isArchiv = 'false'
AND inst.instanceEnd IS NULL
GROUP BY inst.id, inst.instanceName, inst.definitionName, act.activityName, task.taskName, ident.identityName, ident.id, ident.firstname, ident.lastname, ident2.identityname
ORDER BY inst.definitionName, act.activityName, inst.instanceName, task.taskName;
Average cycle time (in h) of all instances of all process definitions per month
SELECT CONCAT(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month, AVG(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Cycle Time' FROM view_instance inst WHERE inst.instanceEnd IS NOT NULL GROUP BY CONCAT(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime))
Number of started instances per month (columns) per year (rows) for a definition
SELECT Year as "Year", SUM(CASE WHEN Month LIKE '1' THEN 1 ELSE 0 END) AS "January", SUM(CASE WHEN Month LIKE '2'THEN 1 ELSE 0 END) AS "February", SUM(CASE WHEN Month LIKE '3' THEN 1 ELSE 0 END) AS "March", SUM(CASE WHEN Month LIKE '4' THEN 1 ELSE 0 END) AS "April", SUM(CASE WHEN Month LIKE '5' THEN 1 ELSE 0 END) AS "May", SUM(CASE WHEN Month LIKE '6' THEN 1 ELSE 0 END) AS "June", SUM(CASE WHEN Month LIKE '7' THEN 1 ELSE 0 END) AS "July", SUM(CASE WHEN Month LIKE '8' THEN 1 ELSE 0 END) AS "August", SUM(CASE WHEN Month LIKE '9' THEN 1 ELSE 0 END) AS "September", SUM(CASE WHEN Month LIKE '10' THEN 1 ELSE 0 END) AS "October", SUM(CASE WHEN Month LIKE '11' THEN 1 ELSE 0 END) AS "November", SUM(CASE WHEN Month LIKE '12' THEN 1 ELSE 0 END) AS "December" FROM (SELECT DATEPART(mm, creationTime) AS Month, DATEPART(yy, creationTime) AS Year FROM view_instance WHERE definitionName = '<name_of_process_definition>' ) AS subqu GROUP BY Year
Cumulative number of started instances per month (columns) per year (rows) for a definition
SELECT Year as "Year", SUM(CASE WHEN Month LIKE '1' THEN 1 ELSE 0 END) AS "January", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' THEN 1 ELSE 0 END) AS "February", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' THEN 1 ELSE 0 END) AS "March", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4' THEN 1 ELSE 0 END) AS "April", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4' OR Month LIKE '5' THEN 1 ELSE 0 END) AS "May", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' THEN 1 ELSE 0 END) AS "June", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' THEN 1 ELSE 0 END) AS "July", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' THEN 1 ELSE 0 END) AS "August", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' THEN 1 ELSE 0 END) AS "September", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' OR Month LIKE '10' THEN 1 ELSE 0 END) AS "October", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' OR Month LIKE '10' OR Month LIKE '11' THEN 1 ELSE 0 END) AS "November", SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' OR Month LIKE '10' OR Month LIKE '11' OR Month LIKE '12' THEN 1 ELSE 0 END) AS "December" FROM (SELECT DATEPART(mm, creationTime) AS Month, DATEPART(yy, creationTime) AS Year FROM view_instance WHERE definitionName = '<name_of_process_definition>' ) AS subqu GROUP BY Year
Minimum, maximum and average cycle time (in h) of instances per process definition
SELECT concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month, AVG(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Average cycle time', MIN(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Min cycle time', MAX(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Max cycle time' FROM view_instance inst WHERE inst.instanceEnd IS NOT NULL GROUP BY concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime))
Definitions-Related
Number of loops (>0) per definition
SELECT defName AS Process, SUM(lCount) AS NumberOfLoops FROM (SELECT inst.definitionName AS defName, inst.instanceName AS instName, act.loopCount AS lCount, inst.id AS piId FROM view_instance inst, view_activity act WHERE act.loopCount IS NOT NULL AND act.instanceId = inst.id GROUP BY inst.id, inst.definitionName, inst.instanceName, act.loopCount ) AS subqu GROUP BY defName;