プロジェクトのツリーマップクエリ
この記事のクエリを使用して、Enhanced Analytics と同様のデータビジュアライゼーションを作成できます。
IMPORTANT
クエリは、Enhanced Analytics で表示される結果と同様の結果を生成しますが、完全には一致しない場合があります。
前提条件
開始する前に、
-
Business Intelligence(BI)ツールとの接続を確立します。
接続を確立したら、この記事のクエリを使用してデータを抽出および視覚化できます。
廃止されたプロジェクト予定時間数
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