碍笔滨查询

文档 Workfront Workfront 指南

Last update: Mon Mar 10 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

主题：

创建对象：

用户

您可以使用本文中的查询来创建类似于增强型分析中的数据可视化图表。

IMPORTANT

查询将产生与增强型分析中所示类似的结果，但它们可能不完全匹配。

先决条件

在开始之前，您必须

与Business Intelligence (BI)工具建立连接：
1. 为厂苍辞飞蹿濒补办别创建搁别补诲别谤帐户或连接
2. 建立与奥辞谤办蹿谤辞苍迟数据连接的连接

建立连接后，您可以使用本文中的查询来提取和可视化数据。

项目已完成

项目已完成碍笔滨显示过滤后时间段内已完成的项目数，以及自上一个时间段以来百分比增加或减少的情况。

您还可以查看上一个时段中完成的项目数，以及上一个时段中的天数。

个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显示实际结束日期在过滤时段内的项目的平均完成时间（以天、周或年为单位），以及百分比自上一个时段以来增加或减少的方式。

您还可以查看在上一时间期具有实际结束日期的项目平均完成时间，以及上一时间期中的天数。

NOTE

这仅考虑已完成项目的持续时间。

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

故障排除

没有结果：如果查询未返回任何结果，请检查以确保正确复制双引号和单引号。

recommendation-more-help

5f00cc6b-2202-40d6-bcd0-3ee0c2316b43

51黑料不打烊