Documentação Workfront Guia do Workfront

Consultas de atividade por equipe

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

Tópicos:

Criado para:

Usuário

Você pode usar as queries neste artigo para criar visualizações de dados semelhantes às do Enhanced Analytics.

IMPORTANT

As consultas produzirão resultados semelhantes aos mostrados no Enhanced Analytics, mas podem não corresponder exatamente.

Pré-requisitos

Antes de começar, você deve

Estabeleça uma conexão com a ferramenta Business Intelligence (BI):
1. Criar uma conta ou conexão de leitor para o Snowflake
2. Estabelecer uma conexão com o Workfront Data Connect

Depois de estabelecer uma conexão, você poderá usar as consultas neste artigo para extrair e visualizar dados.

Eventos de logon de usuários das equipes padrão

WITH userlogins as (
    SELECT
        userid,
        lastlogindate
    FROM (
        SELECT
            userid,
            lastlogindate,
            lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
        FROM users_event
    )
    WHERE lastlogindate != previous_login
)

SELECT
    uc.hometeamid,
    TO_DATE(ul.lastlogindate),
    count(*)
FROM users_current uc
    INNER JOIN userlogins ul ON uc.userid = ul.userid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(ul.lastlogindate)

Eventos de logon dos usuários da equipe inicial: detalhar

WITH userlogins as (
    SELECT
        userid,
        lastlogindate
    FROM (
        SELECT
            userid,
            lastlogindate,
            lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
        FROM users_event
    )
    WHERE lastlogindate != previous_login
)

SELECT
    uc.hometeamid,
    uc.userid,
    TO_DATE(ul.lastlogindate),
    count(*)
FROM users_current uc
    INNER JOIN userlogins ul ON uc.userid = ul.userid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(ul.lastlogindate)

Eventos de logon de usuários das equipes

WITH userlogins as (
    SELECT
        userid,
        lastlogindate
    FROM (
        SELECT
            userid,
            lastlogindate,
            lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
        FROM users_event
    )
    WHERE lastlogindate != previous_login
)

SELECT
    tmc.teamid,
    TO_DATE(ul.lastlogindate),
    count(*)
FROM teammembers_current tmc
    inner join teams_current tc on tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
    inner join userlogins ul on tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(ul.lastlogindate)

Eventos de logon dos usuários das equipes: detalhar

WITH userlogins as (
    SELECT userid, lastlogindate
    FROM (
        SELECT userid, lastlogindate, lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid  ORDER BY begin_effective_timestamp) as previous_login
        FROM users_event
    )
    WHERE lastlogindate != previous_login
)

SELECT tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate), count(*)
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    INNER JOIN userlogins ul ON tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate)

Eventos de alteração de status de tarefa de usuários da equipe padrão

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status != 'CPL'
    )
    WHERE status != previous_status
)

SELECT
    uc.hometeamid,
    TO_DATE(tsc.begin_effective_timestamp),
    count(tsc.taskid) as task_status_updates
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid,  TO_DATE(tsc.begin_effective_timestamp)

Eventos de alteração de status de tarefa dos usuários da equipe interna: detalhar

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status != 'CPL'
    )
    WHERE status != previous_status
)

SELECT
    uc.hometeamid,
    uc.userid,
    TO_DATE(tsc.begin_effective_timestamp),
    count(tsc.taskid) as task_status_updates
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tsc.begin_effective_timestamp)

Eventos de alteração de status de tarefa dos usuários das equipes

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status != 'CPL'
    )
    WHERE status != previous_status
)

SELECT tmc.teamid,  TO_DATE(tsc.begin_effective_timestamp), count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
    INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid,  TO_DATE(tsc.begin_effective_timestamp)

Eventos de alteração de status de tarefa dos usuários das equipes: detalhar

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status != 'CPL'
    )
WHERE status != previous_status
)

SELECT
    tmc.teamid,
    tmc.userid,
    TO_DATE(tsc.begin_effective_timestamp),
    count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
    inner join teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    inner join assignments_current ac ON tmc.userid = ac.assignedtoid
    inner join task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(tsc.begin_effective_timestamp)

Eventos de conclusão de tarefas de usuários das equipes domésticas

WITH task_status_done as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT
        taskid,
        actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    uc.hometeamid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(tasks_done.task_completion_date)

Eventos de conclusão de tarefas de usuários da equipe interna: detalhar

WITH task_status_done as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT
        taskid,
        actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    uc.hometeamid,
    uc.userid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tasks_done.task_completion_date)

Eventos de conclusão de tarefas de usuários de equipes

WITH task_status_done as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT
        taskid,
        actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    tmc.teamid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
    INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(tasks_done.task_completion_date)

Eventos de conclusão de tarefas de usuários das equipes: detalhar

WITH task_status_done as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT taskid, actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    tmc.teamid,
    tmc.userid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(tasks_done.task_completion_date)

recommendation-more-help

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