Workfront Data Connect查詢範例

為協助您更妥善運用Workfront Data Connect資料，本頁包含基本範例查詢，讓您熟悉特定型別查詢的語法和結構。

自訂資料查詢

此範例示範如何撰寫查詢以傳回Workfront中的自訂資料，例如自訂表單和自訂欄位。

情境

您的組織使用名為Finance Integration的自訂表單。 此表單會附加至每個專案，並包含下列欄位：

您需要將此資訊包含在針對Data Connect的查詢的回應中。 資料湖中記錄的自訂資料值包含在標題為parametervalues的欄中。 此欄會儲存為JSON物件。

查詢

SELECT
    projectid,
    parametervalues,
    name,
    parametervalues:"DE:Business Unit"::int as BusinessUnit,
    parametervalues:"DE:Project ID"::int as ProjectID,
    parametervalues:"DE:Expanded Project Name"::text as ExpandedProjectName
FROM PROJECTS_CURRENT
WHERE ExpandedProjectName is not null

回應

上述查詢會傳回下列資料：

解釋

查詢parametervalues JSON物件時，每個自訂資料欄位都可透過下列專案以欄的形式存取：

<field_name>:"<parameter_name>"::<data_type> as <column_name>

狀態查詢中的時間

此範例示範如何測量專案在先前指派狀態中逗留的時間。 它可以很輕鬆地調整以測量狀態中的任務或問題時間，也可以調整以測量物件套用了任何其他屬性（包括自訂資料值）的時間長度。

情境

您的組織領導認為您在工作生命週期的每個階段都花太多時間。 建議改善程式之前，建議您建立基線測量，測量專案狀態在一段時間內變更的頻率，以及專案處於任何指定狀態的天數。

您即將使用PROJECTS_EVENT資料檢視，針對專案物件提取每個狀態變更的清單。 您將比較新狀態與先前狀態、取得先前指派狀態的有效時間範圍，然後計算該狀態所花費的天數。

使用每個專案每個狀態所花時間的原始輸出，您可以開始建立視覺效果或進一步彙總資料，以按狀態、專案型別或一年時間建立狀態持續時間平均值。 然後，此基準會被用來設定基準，以符合領導層的期望，供您衡量。

下列查詢使用Data Connect PROJECTS_EVENTS資料檢視來比較每個專案狀態變更事件並顯示狀態中的時間。

查詢

-- Calculate the begin/end effective timestamp and duration in days

SELECT

    projectid,
    name as project_name,
    prev_status as previous_status,
    status,
    status_change_date as status_begin_effective_timestamp,
    case
        when status_change_date is null then NULL
        else
            nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)
    end as status_end_effective_timestamp,
    datediff('DAYS',status_change_date, nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)) as status_duration_days

FROM
    ( -- Filter to just the records that have changed
     SELECT projectid,
        name,
        prev_status,
        status,
        begin_effective_timestamp as status_change_date
     FROM
        (  -- Calculate records where previous status is different
          SELECT DISTINCT
           pe.projectid,
           pe.name AS name,
           pe.STATUS,
           nvl(lag(pe.STATUS) over (partition by pe.projectid order by pe.BEGIN_EFFECTIVE_TIMESTAMP), status) prev_status,
           begin_effective_timestamp

          FROM projects_event pe
         -- Set any WHERE conditions to limit the results as needed
         --WHERE
            -- pe.PROJECTID = '5ebe…c1e1'
        )
        WHERE prev_status != status
    )
    order by status_change_date;

回應

上述查詢會傳回下列資料：

解釋

查詢使用Data Connect的變更事件追蹤功能。它會判斷事件觸發之新狀態值與先前事件不同之事件的日期。

從內到外檢查查詢：

Planning：單一記錄型別查詢

此範例示範如何查詢Workfront Planning資料，以取得儲存在Data Connect資料湖中的單一記錄型別。

情境

您的組織使用Workfront Planning來追蹤行銷活動。 每個行銷活動記錄包含名稱、狀態、開始日期、結束日期和擁有者。 您想要提取所有作用中行銷活動的清單及其重要詳細資訊，以用於控制面板。

查詢

SELECT
  recordid,
  FIELD_VALUES:"Name"::text AS campaign_name,
  FIELD_VALUES:"Status"::text AS campaign_status,
  FIELD_VALUES:"Start Date"::date AS start_date,
  FIELD_VALUES:"End Date"::date AS end_date,
  FIELD_VALUES:"Owner"::text AS owner
FROM PLANNINGRECORD_CURRENT
WHERE WORKSPACEID = '<your_campaign_workspace_id>'
AND RECORDTYPEID = '<your_campaign_record_type_id>'
AND FIELD_VALUES:"Status"::text = 'Active'
ORDER BY start_date ASC

回應

上述查詢會傳回下列資料：

解釋

Data Connect中的Planning記錄共用單一表格結構，無論記錄型別為何。 RECORDTYPEID欄用於將查詢的範圍設定為特定的記錄型別 — 在此例中為「促銷活動」。 將<your_campaign_record_type_id>取代為您要查詢的記錄型別ID，可在Workfront Planning記錄型別設定中或透過查詢RECORDTYPE_CURRENT找到。

欄位值會儲存為FIELD_VALUES欄中的JSON物件，並使用用於自訂表單資料的相同冒號標籤語法來存取：

<field_column>:"<field_name>"::<data_type> AS <alias>

欄位名稱參照必須與Planning記錄型別欄位組態中定義的欄位名稱完全相符，包括大寫、間距和表情符號。

SELECT
    ID AS recordtypeid,
    DISPLAYNAME AS record_type_name,
    WORKSPACEID
    FROM RECORDTYPE_CURRENT
    ORDER BY record_type_name ASC

Planning：連線記錄型別查詢

此範例示範如何跨兩個連線的Planning記錄型別（父記錄型別及其連線的記錄型別）查詢資料。

情境

您的組織會將Campaign記錄連結至Workfront Planning中的策略記錄。 您想要產生一份報告，顯示每個行銷活動及其相關策略的重要詳細資訊。 他們特別想要顯示策略名稱、策略優先順序和預算分配，以便領導可以檢閱依策略組織的行銷活動活動。

在「資料連線」中，原生Planning記錄型別之間的連線會直接儲存在PLANNINGRECORD_CURRENT的FIELD_VALUES_RAW資料欄。 對於名為「戰術」的參考欄位，值是連線記錄物件的JSON陣列，每個物件都包含具有連線記錄的RECORDID的id屬性。 使用Snowflake的LATERAL FLATTEN將此陣列展開為列，並聯結至連線的記錄型別。

查詢

SELECT
  c.RECORDID AS campaign_id,
  c.FIELD_VALUES:"Name"::text AS campaign_name,
  c.FIELD_VALUES:"Status"::text AS campaign_status,
  t.FIELD_VALUES:"Name"::text AS tactic_name,
  t.FIELD_VALUES:"Strategic Priority"::text AS strategic_priority,
  t.FIELD_VALUES:"Budget Allocation"::float AS budget_allocation
FROM PLANNINGRECORD_CURRENT c,
INNER JOIN REFERENCE_CURRENT R
ON r.FROM_REFERENCEID = c.REFERENCE_IDS:"Tactics"::text
INNER JOIN PLANNINGRECORD_CURRENT t
-- Join to the Tactic record using the connected record ID from the array
ON t.RECORDID = r.TO_RECORDID
WHERE c.RECORDTYPEID = '<your_campaign_record_type_id>'
ORDER BY tactic_name, campaign_name

回應

上述查詢會傳回下列資料：

說明 — 修改的KP

原生Planning記錄型別之間的連線儲存在REFERENCE_CURRENT聯結表格中。 REFERENCE_CURRENT聯結表格用於RecordType之間的聯結。 在RecordType之間連線時，應該使用TO_RECORDID欄位。

PLANNINGRECORD檢視表中的REFERENCE_ID欄位包含適用於該計畫記錄的所有REFERENCEID欄位清單。 您可以使用與field_value相同的JSON標籤法來存取ID。

<reference_ids>:"<reference_name>"::text

REFERENCE_CURRENT檢視包含一或多個記錄，其中TO_RECORDID指向PLANNINGRECORD_*檢視中的其他計畫recordId欄位。

若要將另一個REFERENCE欄位聯結至其他計畫記錄，則上述查詢中會加入相同的聯結至REFERENCE_CURRENT與PLANNINGRECORD_*的模式。

Planning：聯結至Workfront工作流程資料查詢的記錄型別

此範例示範如何使用Planning的原生連線功能（其將外部物件參考儲存在REFERENCE_CURRENT檢視中），將Workfront Planning記錄型別聯結至原生Workfront Workflow物件（在此例中為Project）。

情境

貴組織使用Planning的原生連線功能，將Workfront Planning中的Campaign記錄連線至Workfront專案。 您想要產生一份合併報告，顯示行銷活動詳細資訊以及連結專案的即時執行資料（特別是專案的目前完成百分比、計畫完成日期和指派的專案所有者），以便行銷活動經理可以追蹤傳遞進度，而不離開其Planning工作區內容。

查詢

SELECT
  c.RECORDID AS campaign_id,
  c.FIELD_VALUES:"Name"::text AS campaign_name,
  c.FIELD_VALUES:"Status"::text AS campaign_status,
  conn.TO_EXTERNALID AS linked_project_id,
  p.name AS project_name,
  p.percentcomplete AS project_percent_complete,
  p.plannedcompletiondate AS project_planned_completion,
  p.ownerid AS project_owner_id,
  u.name AS project_owner_name
FROM WORKFRONT.PLANNING.PLANNINGRECORD_CURRENT c
-- Join to the references table to find Workfront Project connections
INNER JOIN WORKFRONT.PLANNING.REFERENCE_CURRENT conn
ON conn.REFERENCE_ID = c.REFERENCE_IDS:"ProjectId"::text
-- Join to the Workfront Projects table on the external ID
INNER JOIN WORKFRONT.WF.PROJECTS_CURRENT p
ON p.projectid = conn.TO_EXTERNALID
-- Join to Users to resolve the project owner name
LEFT JOIN WORKFRONT.WF.USERS_CURRENT u
ON u.userid = p.ownerid
WHERE c.RECORDTYPEID = '<your_campaign_record_type_id>'
AND p.completiontype != 'CPL' -- Exclude completed projects
ORDER BY campaign_name

回應

上述查詢會傳回下列資料：

解釋

從Planning記錄型別到原生Workfront Workflow物件的連線會儲存在REFERENCE_CURRENT中。 此檢視中的每一列代表一個方向連結：TO_EXTERNALID儲存連線的Workfront物件識別碼。 代表Workfront連線的列由TO_EXTERNALCONNECTIONNAME = 'workfront'和對應至Workfront物件型別的API程式碼的TO_EXTERNALOBJECTNAME值（例如，專案的PROJ）識別。

PLANNINGRECORD表格中的REFERENCE_ID欄包含適用於該記錄的所有REFERENCEID欄位清單。 您可以使用與field_value相同的JSON標籤法來存取ID。
PLANNINGRECORD_CURRENT中的單一REFERENCE_ID可能包含REFERENCE_CURRENT表格中的一或多個參照連結，這些連結會連結至Workfront表格中特定物件型別的物件。

<reference_ids>:"<reference_name>"::text

請注意，Planning檢視(PLANNINGRECORD_CURRENT、REFERENCE_CURRENT)位於WORKFRONT.PLANNING架構中，而原生Workfront工作流程檢視（PROJECTS_CURRENT、USERS_CURRENT等）位於 位於WORKFRONT.WF結構描述中。 跨結構描述聯結需要完整的資料表名稱。

查詢會執行三個聯結：

若要將另一個REFERENCE欄位聯結到其他外部記錄，與聯結到REFERENCE_CURRENT和Workfront工作流程檢視的模式相同，將新增到上述查詢。

透過多次聯結至REFERENCE_CURRENT表格並使用適當的聯結模式，可在相同查詢中聯結外部與Planningrecord值。