| Table of Contents |
|---|
...
Aufgaben- und Aktivitätsbezogene
Dieser Artikel bietet Beispiele für aufgaben- und aktivitätsbezogene Analysen mit dem Dashboard.
...
Aufgabenbezogen
Alle offenen Aufgaben für einen bestimmten Benutzer
| Code Block | ||
|---|---|---|
| ||
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>'
|
Anzahl der erstellten, abgeschlossenen und offenen Aufgaben für jede Prozessdefinition
| Code Block | ||
|---|---|---|
| ||
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 |
...
Aktivitätsbezogen
Durchschnittliche, minimale und maximale Zykluszeit aller Aktivitäten einer bestimmten Prozessdefinition (in h) pro Monat
| Code Block | ||
|---|---|---|
| ||
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')); |
...