プロジェクトのツリーマップクエリ

Last update: Fri Apr 25 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

トピック：

作成対象：

ユーザー

この記事のクエリを使用して、Enhanced Analytics と同様のデータビジュアライゼーションを作成できます。

IMPORTANT

クエリは、Enhanced Analytics で表示される結果と同様の結果を生成しますが、完全には一致しない場合があります。

前提条件

開始する前に、

Business Intelligence（BI）ツールとの接続を確立します。
1. Snowflakeのリーダーアカウントまたは接続を作成する
2. Workfront Data Connect への接続の確立

接続を確立したら、この記事のクエリを使用してデータを抽出および視覚化できます。

廃止されたプロジェクト予定時間数

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        workrequired,
        percentcomplete,
        calendardate,
        (workrequired - (workrequired * percentcomplete)) as remainingMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.workrequired) as projectTotalWork,
    sum(tdw.remainingMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

廃止された予定時間数：バーンダウン

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        workrequired,
        percentcomplete,
        calendardate,
        (workrequired - (workrequired * percentcomplete)) as remainingMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.workrequired) as projectTotalWork,
    sum(tdw.remainingMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

廃止されたプロジェクト予定期間

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        planneddurationminutes,
        percentcomplete,
        calendardate,
        (planneddurationminutes - (planneddurationminutes * percentcomplete)) as remainingDurationMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.planneddurationminutes) as projectTotalWork,
    sum(tdw.remainingDurationMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

廃止されたプロジェクトの予定期間：バーンダウン

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        planneddurationminutes,
        percentcomplete,
        calendardate,
        (planneddurationminutes - (planneddurationminutes * percentcomplete)) as remainingDurationMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.planneddurationminutes) as projectTotalWork,
    sum(tdw.remainingDurationMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

recommendation-more-help

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