51黑料不打烊

Workfront Data Connect query examples

To help you better utilize your Workfront Data Connect data, this page contains basic example queries you can use to familiarize yourself with the syntax and structure of specific kinds of queries.

Custom data query

This example demonstrates how you can compose a query to return your custom data in Workfront, such as custom forms and custom fields.

Scenario

Your organization utilizes a custom form named Finance Integration. The form is attached to every project, and contains the following fields:

You need to include this information in the response for a query against Data Connect. Custom data values for a record in the data lake are contained in a column titled parametervalues. This column is stored as a JSON object.

Query

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

Response

The above query returns the following data:

Explanation

When querying the parametervalues JSON object, each custom data field can be accessed as a column using the following:

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

Time in status query

This example demonstrates how to measure the time a project spent in previously assigned statuses. It can easily be adapted to measure task or issue time in a status, or it can be adapted to measure how long an object had any other attribute (including custom data values) applied to it.

Scenario

Your organization leadership believes you are spending too much time in each stage of your work lifecycle. Before making recommendations to improve the process, you want to create a baseline measurement of how often a project status changes over time and how many days a project stays in any given status.

You鈥檙e going to use the PROJECTS_EVENT data view to pull in a list of each status change against the project object. You鈥檒l compare the new status to the previous status, grab the effective time range for the previously assigned status, and then calculate the days spent in that status.

Using this raw output of time spent in each status per project, you can start to build visualizations or aggregate the data further to build status duration averages by status, project type, or time of year. This baseline is then used to set a benchmark you can measure against to meet your leadership鈥檚 expectations.

The following query uses the Data Connect PROJECTS_EVENTS data view to compare each project status change event and display the time in status.

Query

-- 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鈥1e1'
        )
        WHERE prev_status != status
    )
    order by status_change_date;

Response

The above query returns the following data:

Explanation

The query uses Data Connect鈥檚 change event tracking capabilities.鈥� It determines the date an event that had a new status value different from the previous event was triggered.

Examining the query from the inside out: