Project treemap queries

Documentation Workfront Workfront Guide

Last update: Tue Mar 11 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

Topics:
Reports and Dashboards

CREATED FOR:

User

You can use the queries in this article to create data visualizations similar to those in Enhanced Analytics.

IMPORTANT

Queries will produce similar results to those shown in Enhanced Analytics, but they may not match exactly.

Prerequisites

Before you begin, you must

Establish a connection with your Business Intelligence (BI) tool:
1. Create a reader account or connection for Snowflake
2. Establish a connection to Workfront Data Connect

Once you establish a connection, you can use the queries in this article to extract and visualize data.

Projects planned hours retired

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

Projects planned hours retired: burndown

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

Projects planned duration retired

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

Projects planned duration retired: burndown

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