Exemples de requête Workfront Data Connect

Pour vous aider à mieux utiliser vos données Workfront Data Connect, cette page contient des exemples de requêtes de base que vous pouvez utiliser pour vous familiariser avec la syntaxe et la structure de types de requêtes spécifiques.

Requête de données personnalisées

Cet exemple montre comment composer une requête pour renvoyer vos données personnalisées dans Workfront, telles que les formulaires personnalisés et les champs personnalisés.

Scénario

Votre entreprise utilise un formulaire personnalisé nommé Finance Integration. Le formulaire est joint à chaque projet et contient les champs suivants :

Vous devez inclure ces informations dans la réponse à une requête sur Data Connect. Les valeurs de données personnalisées d’un enregistrement du lac de données se trouvent dans une colonne intitulée parametervalues. Cette colonne est stockée en tant qu’objet JSON.

Requête

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

Réponse

La requête ci-dessus renvoie les données suivantes :

Explication

Lors de l’interrogation de l’objet JSON parametervalues, chaque champ de données personnalisé est accessible sous la forme d’une colonne à l’aide des éléments suivants :

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

Durée dans la requête de statut

Cet exemple montre comment mesurer le temps passé par un projet dans des statuts précédemment attribués. Il peut être facilement adapté pour mesurer le temps passé sur une tâche ou un événement dans un statut, ou il peut être adapté pour mesurer la durée pendant laquelle un autre attribut (y compris des valeurs de données personnalisées) a été appliqué à un objet.

Scénario

La direction de votre entreprise estime que vous passez trop de temps à chaque étape de votre cycle de vie professionnelle. Avant de faire des recommandations pour améliorer le processus, vous souhaitez créer une mesure de référence de la fréquence à laquelle le statut d’un projet change au fil du temps et du nombre de jours qu’un projet reste dans un statut donné.

Vous allez utiliser la vue de données PROJECTS_EVENT pour extraire une liste de chaque changement de statut par rapport à l’objet du projet. Vous allez comparer le nouveau statut au statut précédent, saisir la période effective pour le statut précédemment attribué, puis calculer les jours passés dans ce statut.

Grâce à cette sortie brute de temps passé dans chaque statut par projet, vous pouvez commencer à créer des visualisations ou agréger les données pour créer des moyennes de durée de statut par statut, type de projet ou période de l’année. Cette ligne de base est ensuite utilisée pour établir un point de référence par rapport auquel vous pouvez effectuer des mesures afin de répondre aux attentes de votre direction.

La requête suivante utilise la vue de données PROJECTS_EVENTS de Data Connect pour comparer chaque événement de changement de statut de projet et afficher l’heure dans le statut.

Requête

-- 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;

Réponse

La requête ci-dessus renvoie les données suivantes :

Explication

La requête utilise les fonctionnalités de suivi des événements de modification de Data Connect. Elle détermine la date de déclenchement d’un événement dont la nouvelle valeur de statut était différente de celle de l’événement précédent.

Examiner la requête de l’intérieur vers l’extérieur :

Planification : requête de type enregistrement unique

Cet exemple montre comment interroger les données Workfront Planning pour un type d’enregistrement unique stocké dans le lac de données Data Connect.

Scénario

Votre entreprise utilise Workfront Planning pour effectuer le suivi des campagnes. Chaque enregistrement de campagne comprend un nom, un statut, une date de début, une date de fin et un propriétaire. Vous souhaitez extraire une liste de toutes les campagnes actives et leurs détails clés pour les utiliser dans un tableau de bord.

Requête

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

Réponse

La requête ci-dessus renvoie les données suivantes :

Explication

Les enregistrements Planning dans Data Connect partagent une structure de table unique, quel que soit le type d’enregistrement. La colonne RECORDTYPEID permet d’étendre la requête à un type d’enregistrement spécifique (dans ce cas, Campagnes). Remplacez <your_campaign_record_type_id> par l’ID du type d’enregistrement que vous souhaitez interroger, qui se trouve dans les paramètres de type d’enregistrement Workfront Planning ou en interrogeant RECORDTYPE_CURRENT.

Les valeurs de champ sont stockées sous la forme d’un objet JSON dans la colonne FIELD_VALUES et sont accessibles en utilisant la même syntaxe avec deux points que celle utilisée pour les données de formulaire personnalisé :

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

Les références au nom du champ doivent correspondre exactement au nom du champ défini dans la configuration du champ de type d’enregistrement Planning, y compris les majuscules, l’espacement et les émoticônes.

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

Planning : requête sur les types d’enregistrements connectés

Cet exemple montre comment interroger les données de deux types d’enregistrements Planning connectés : un type d’enregistrement parent et un type d’enregistrement auquel il est connecté.

Scénario

Votre entreprise connecte les enregistrements Campaign aux enregistrements Tactiques dans Workfront Planning. Vous souhaitez générer un rapport qui présente chaque campagne avec les détails clés des tactiques associées. Ils veulent en particulier montrer le nom de la tactique, la priorité stratégique et l’allocation budgétaire afin que la direction puisse examiner l’activité de campagne organisée par tactique.

Dans Data Connect, les connexions entre les types d’enregistrements Planning natifs sont stockées directement dans la colonne FIELD_VALUES_RAW de PLANNINGRECORD_CURRENT. Pour un champ de référence nommé « Tactiques », la valeur est un tableau JSON d’objets d’enregistrement connectés, chacun contenant une propriété id avec le RECORDID de l’enregistrement connecté. Utilisez l’APLATISSEMENT LATÉRAL Snowflake pour développer ce tableau en lignes et le joindre au type d’enregistrement connecté.

Requête

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

Réponse

La requête ci-dessus renvoie les données suivantes :

Explication - KP modifié

Les connexions entre les types d’enregistrements Planning natifs sont stockées dans une table de jointure REFERENCE_CURRENT. La table de jointure REFERENCE_CURRENT est utilisée pour les jointures entre RecordType. Lors de la jonction entre RecordType, le champ TO_RECORDID doit être utilisé.

La colonne REFERENCE_ID de la vue PLANNINGRECORD contient la liste de tous les champs REFERENCEID applicables à cet enregistrement de planification. Vous pouvez accéder à l’identifiant en utilisant la même notation JSON qu’une valeur_champ.

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

La vue REFERENCE_CURRENT contient un ou plusieurs enregistrements dans lesquels TO_RECORDID pointe vers d’autres champs de recordId de planification dans les vues PLANNINGRECORD_*.

Pour joindre un autre champ REFERENCE à d’autres enregistrements de planification, le même modèle de jointure à REFERENCE_CURRENT et à PLANNINGRECORD_* des vues sont ajoutés à la requête ci-dessus.

Planification : type d’enregistrement joint à la requête de données de workflow Workfront

Cet exemple montre comment joindre un type d’enregistrement Workfront Planning à un objet de workflow Workfront natif (dans ce cas, un projet) à l’aide de la fonction de connexion native de Planning, qui stocke les références d’objet externes dans la vue REFERENCE_CURRENT.

Scénario

Votre entreprise connecte les enregistrements Campaign dans Workfront Planning aux projets Workfront à l’aide de la fonctionnalité de connexion native de Planning. Vous souhaitez produire un rapport combiné présentant les détails de la campagne ainsi que les données d’exécution en direct du projet lié, en particulier le pourcentage achevé actuel du projet, la date d’achèvement prévue et le propriétaire du projet affecté, afin que les responsables de la campagne puissent suivre la progression de la diffusion sans quitter leur contexte d’espace de travail Planning.

Requête

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

Réponse

La requête ci-dessus renvoie les données suivantes :

Explication

Les connexions d’un type d’enregistrement Planning à un objet de workflow Workfront natif sont stockées dans REFERENCE_CURRENT. Chaque ligne de cette vue représente un lien directionnel : TO_EXTERNALID contient l’identifiant de l’objet Workfront connecté. Les lignes représentant les connexions Workfront sont identifiées par TO_EXTERNALCONNECTIONNAME = 'workfront' et une valeur TO_EXTERNALOBJECTNAME correspondant au code API du type d’objet Workfront, par exemple PROJ pour les projets.

La colonne REFERENCE_ID des tables PLANNINGRECORD contient la liste de tous les champs REFERENCEID qui s’appliquent à cet enregistrement. Vous pouvez accéder à l’identifiant en utilisant la même notation JSON qu’une valeur_champ.
Un ID REFERENCE_ID unique dans PLANNINGRECORD_CURRENT peut contenir un ou plusieurs liens de référence dans la table REFERENCE_CURRENT qui sont liés à des objets d’un type d’objet spécifique dans la table Workfront.

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

Notez que les vues Planning (PLANNINGRECORD_CURRENT, REFERENCE_CURRENT) résident dans le schéma WORKFRONT.PLANNING, tandis que les vues de workflow Workfront natives (PROJECTS_CURRENT, USERS_CURRENT, etc.) résident dans le schéma WORKFRONT.WF ; Les jointures entre schémas nécessitent des noms de table entièrement qualifiés.

La requête effectue trois jointures :

Pour joindre un autre champ REFERENCE à des enregistrements externes supplémentaires, ajoutez le même modèle de jointure à REFERENCE_CURRENT et les vues de workflow Workfront à la requête ci-dessus.

Les valeurs des enregistrements External et Planningrecord peuvent être jointes dans la même requête en joignant plusieurs fois la table REFERENCE_CURRENT et en utilisant le modèle de jointure approprié.