KPI查询
创建对象:
- 用户
您可以使用本文中的查询来创建类似于增强型分析中的数据可视化图表。
先决条件
在开始之前,您必须
-
与Business Intelligence (BI)工具建立连接:
建立连接后,您可以使用本文中的查询来提取和可视化数据。
项目已完成
项目已完成KPI显示过滤后时间段内已完成的项目数,以及自上一个时间段以来百分比增加或减少的情况。
您还可以查看上一个时段中完成的项目数,以及上一个时段中的天数。
查询
WITH completedProjectsInRange as (
SELECT
COUNT(t0.PROJECTID) as PROJECT_COUNT
FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2025-01-01'
AND t0.ACTUALCOMPLETIONDATE <= '2025-01-31'
), completedProjectsPreviousRange as (
SELECT
COUNT(t0.PROJECTID) as PROJECT_COUNT
FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2024-12-01'
AND t0.ACTUALCOMPLETIONDATE <= '2024-12-31'
), rawChange as (
SELECT
(a.PROJECT_COUNT - b.PROJECT_COUNT) as CHANGE_FROM_PREVIOUS_PERIOD
FROM completedProjectsInRange a, completedProjectsPreviousRange b
), percentChange as (
SELECT
CASE
WHEN a.PROJECT_COUNT = b.PROJECT_COUNT THEN 0.00
WHEN b.PROJECT_COUNT > 0 THEN ((a.PROJECT_COUNT - b.PROJECT_COUNT) / b.PROJECT_COUNT * 100)
END AS PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM completedProjectsInRange a, completedProjectsPreviousRange b
)
SELECT
a.PROJECT_COUNT,
b.PROJECT_COUNT as PREVIOUS_PROJECT_COUNT,
c.CHANGE_FROM_PREVIOUS_PERIOD,
d.PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM completedProjectsInRange a, completedProjectsPreviousRange b, rawChange c,
percentChange d
按时完成的项目
按时完成的项目KPI显示在筛选的时间期内,按时完成的项目百分比,以及自上一时间段以来该百分比如何增加或减少。
您还可以查看上一个时间段按时完成项目的百分比,以及上一个时间段的天数。
WITH completedProjectsInRange as (
SELECT
COUNT(t0.PROJECTID) as PROJECT_COUNT
FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2025-01-01'
AND t0.ACTUALCOMPLETIONDATE <= '2025-01-31'
), completedOntimeProjectsInRange as (
SELECT
COUNT(t0.PROJECTID) as PROJECT_COUNT
FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2025-01-01'
AND t0.ACTUALCOMPLETIONDATE <= '2025-01-31'
AND t0.PROGRESSSTATUS = 'ON'
), percentOntimeProjects as (
SELECT
CASE
WHEN a.PROJECT_COUNT = 0 THEN 0
ELSE ROUND(b.PROJECT_COUNT/a.PROJECT_COUNT) * 100
END as ONTIMEPROJECTPERCENT
FROM completedProjectsInRange a, completedOntimeProjectsInRange b
), completedProjectsPreviousRange as (
SELECT
COUNT(t0.PROJECTID) as PROJECT_COUNT
FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2024-12-01'
AND t0.ACTUALCOMPLETIONDATE <= '2024-12-31'
), completedOntimeProjectsPreviousRange as (
SELECT
COUNT(t0.PROJECTID) as PROJECT_COUNT
FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2024-12-01'
AND t0.ACTUALCOMPLETIONDATE <= '2024-12-31'
AND t0.PROGRESSSTATUS = 'ON'
), percentOntimeProjectsPreviousRange as (
SELECT
CASE
WHEN a.PROJECT_COUNT = 0 THEN 0
ELSE ROUND(b.PROJECT_COUNT/a.PROJECT_COUNT) * 100
END as ONTIMEPROJECTPERCENT
FROM completedProjectsPreviousRange a, completedOntimeProjectsPreviousRange b
), rawChange as (
SELECT
(a.ONTIMEPROJECTPERCENT - b.ONTIMEPROJECTPERCENT) as CHANGE_FROM_PREVIOUS_PERIOD
FROM percentOntimeProjects a, percentOntimeProjectsPreviousRange b
), percentChange as (
SELECT
CASE
WHEN a.ONTIMEPROJECTPERCENT = b.ONTIMEPROJECTPERCENT THEN 0
WHEN b.ONTIMEPROJECTPERCENT > 0 THEN ((a.ONTIMEPROJECTPERCENT - b.ONTIMEPROJECTPERCENT) / b.ONTIMEPROJECTPERCENT * 100)
END AS PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM percentOntimeProjects a, percentOntimeProjectsPreviousRange b
)
SELECT
a.ONTIMEPROJECTPERCENT,
b.ONTIMEPROJECTPERCENT as PREVIOUS_ONTIMEPROJECTPERCENT,
c.CHANGE_FROM_PREVIOUS_PERIOD,
d.PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM percentOntimeProjects a, percentOntimeProjectsPreviousRange b, rawChange c,
percentChange d
平均 项目持续时间
平均 项目持续时间KPI显示实际结束日期在过滤时段内的项目的平均完成时间(以天、周或年为单位),以及百分比自上一个时段以来增加或减少的方式。
您还可以查看在上一时间期具有实际结束日期的项目平均完成时间,以及上一时间期中的天数。
WITH averageProjectDurationInRange as (
SELECT
AVG(t0.ACTUALDURATIONMINUTES) as AVERAGE_PROJECT_DURATION
FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2025-01-01'
AND t0.ACTUALCOMPLETIONDATE <= '2025-01-31'
), averageProjectPreviousRange as (
SELECT AVG (t0. ACTUALDURATIONMINUTES) as AVERAGE_PROJECT_DURATION FROM PROJECTS_CURRENT t0
WHERE t0.ACTUALCOMPLETIONDATE >= '2024-12-01'
AND t0.ACTUALCOMPLETIONDATE <= '2024-12-31'
), rawChange as (
SELECT ((a.AVERAGE_PROJECT_DURATION - b.AVERAGE_PROJECT_DURATION) / 480) as CHANGE_FROM_PREVIOUS_PERIOD FROM averageProjectDurationInRange a, averageProjectPreviousRange b
), percentChange as (
SELECT
CASE
WHEN a.AVERAGE_PROJECT_DURATION = b.AVERAGE_PROJECT_DURATION THEN 0
WHEN b.AVERAGE_PROJECT_DURATION > 0 THEN ((a.AVERAGE_PROJECT_DURATION - b.AVERAGE_PROJECT_DURATION) / b.AVERAGE_PROJECT_DURATION) * 100
END AS PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM averageProjectDurationInRange a, averageProjectPreviousRange b
)
SELECT
a.AVERAGE_PROJECT_DURATION,
b.AVERAGE_PROJECT_DURATION as PREVIOUS_AVERAGE_PROJECT_DURATION,
c.CHANGE_FROM_PREVIOUS_PERIOD,
d.PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM averageProjectDurationInRange a, averageProjectPreviousRange b, rawChange c,
percentChange d
每个项目平均任务
“每个项目的平均任务KPI”显示在过滤的时间段内分配给项目的平均任务数,以及自上一个时间段以来该百分比如何增加或减少。
您还可以查看上一个时段分配给项目的平均任务数,以及上一个时段中的天数。
每个项目
WITH tasksPerProjectInRange as (
SELECT
COUNT(t0.TASKID) as TASK_COUNT
FROM TASKS_CURRENT t0
LEFT JOIN PROJECTS_CURRENT t1 ON t1.PROJECTID = t0.PROJECTID
WHERE
(
t1.PLANNEDSTARTDATE >= '2025-01-01'
AND t1.PLANNEDSTARTDATE <= '2025-01-31'
)
OR (
t1.PLANNEDCOMPLETIONDATE >= '2025-01-01'
AND t1. PLANNEDCOMPLETIONDATE <= '2025-01-31'
)
OR (
t1.PLANNEDSTARTDATE <= '2025-01-01'
AND t1. PLANNEDCOMPLETIONDATE >= '2025-01-31'
)
GROUP BY t0.PROJECTID
), averageTasksPerProjectInRange as (
SELECT AVG(TASK_COUNT) AS AVERAGE_TASK_COUNT FROM tasksPerProjectInRange
), tasksPerProjectInPreviousRange as (
SELECT
COUNT(t0.TASKID) as TASK_COUNT
FROM TASKS_CURRENT t0
LEFT JOIN PROJECTS_CURRENT t1 ON t1.PROJECTID = t0.PROJECTID
WHERE
(
t1.PLANNEDSTARTDATE >= '2024-12-01'
AND t1.PLANNEDSTARTDATE <= '2024-12-31'
)
OR (
t1.PLANNEDCOMPLETIONDATE >= '2024-12-01'
AND t1. PLANNEDCOMPLETIONDATE <= '2024-12-31'
)
OR (
t1.PLANNEDSTARTDATE <= '2024-12-01'
AND t1. PLANNEDCOMPLETIONDATE >= '2024-12-31'
)
GROUP BY t0.PROJECTID
), averageTasksPerProjectInPreviousRange as (
SELECT
AVG(TASK_COUNT) AS AVERAGE_TASK_COUNT
FROM tasksPerProjectInPreviousRange
), rawChange as (
SELECT
(a.AVERAGE_TASK_COUNT - b.AVERAGE_TASK_COUNT) as CHANGE_FROM_PREVIOUS_PERIOD
FROM averageTasksPerProjectInRange a, averageTasksPerProjectInPreviousRange b
), percentChange as (
SELECT
CASE
WHEN a.AVERAGE_TASK_COUNT = b.AVERAGE_TASK_COUNT THEN 0
WHEN b.AVERAGE_TASK_COUNT > 0 THEN ((a.AVERAGE_TASK_COUNT - b.AVERAGE_TASK_COUNT) / b.AVERAGE_TASK_COUNT) * 100
END as PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM averageTasksPerProjectInRange a, averageTasksPerProjectInPreviousRange b
)
SELECT
a.AVERAGE_TASK_COUNT,
b.AVERAGE_TASK_COUNT as PREVIOUS_AVERAGE_TASK_COUNT,
c.CHANGE_FROM_PREVIOUS_PERIOD,
d.PERCENT_CHANGE_FROM_PREVIOUS_PERIOD
FROM averageTasksPerProjectInRange a, averageTasksPerProjectInPreviousRange b, rawChange c, percentChange d
故障排除
- 没有结果:如果查询未返回任何结果,请检查以确保正确复制双引号和单引号。