DokumentationWorkfrontWorkfront-Handbuch

Projektaktivität-Abfragen

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

Erstellt für:

  • Benutzende

Sie können die Abfragen in diesem Artikel verwenden, um Datenvisualisierungen zu erstellen, die denen in Enhanced Analytics ähneln.

IMPORTANT
Abfragen führen zu ähnlichen Ergebnissen wie die in Enhanced Analytics gezeigten, aber sie stimmen möglicherweise nicht genau überein.

Voraussetzungen

Bevor Sie beginnen, müssen Sie

  1. Stellen Sie eine Verbindung mit Ihrem Business Intelligence-Tool (BI) her:

    1. Erstellen eines Leserkontos oder einer Verbindung für Snowflake
    2. Herstellen einer Verbindung mit Workfront Data Connect

Nachdem Sie eine Verbindung hergestellt haben, können Sie die Abfragen in diesem Artikel verwenden, um Daten zu extrahieren und zu visualisieren.

Anmeldeereignisse der Projektbenutzer

Zeigt die Anzahl der dem Projekt zugewiesenen Personen, die sich an einem bestimmten Tag angemeldet haben.

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
    tds.projectid,
    ads.calendardate,
    count(1)
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid and TO_DATE(ul.lastlogindate) = ads.calendardate
GROUP BY tds.projectid, ads.calendardate

Anmeldeereignisse der Projektbenutzer: Aufschlüsselung

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
    tds.projectid,
    ul.userid,
    ads.calendardate,
    count(1)
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid AND TO_DATE(ul.lastlogindate) = ads.calendardate
group by tds.projectid, ul.userid, ads.calendardate

Aufgabenstatusänderungsereignisse von Projektbenutzenden

Zeigt die Anzahl der Personen, die den Status einer Aufgabe für das Projekt an einem bestimmten Tag geändert haben.

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
    tds.projectid,
    count(tds.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate

Aufgabenstatusänderungsereignisse von Projektbenutzenden: Aufschlüsselung

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        lastupdatedbyid,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lastupdatedbyid,
            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
    tds.projectid,
    tsc.lastupdatedbyid,
    count(tsc.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate

Abschlussereignisse von Projektbenutzenden für Aufgaben

Die Anzahl der Personen anzeigen, die an einem bestimmten Tag eine Aufgabe für das Projekt abgeschlossen haben.

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
    tds.projectid,
    count(tds.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid and tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid and tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate

Abschlussereignisse von Projektbenutzenden: Aufschlüsselung

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        lastupdatedbyid,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lastupdatedbyid,
            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
    tds.projectid,
    tsc.lastupdatedbyid,
    count(tsc.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate
recommendation-more-help
5f00cc6b-2202-40d6-bcd0-3ee0c2316b43