Common Joins Reference Guide

1. Overview

Note: Administrators should not set up every possible join between two datasets in the Settings > Data Model > Relationships area. This would confuse users who may not know which one to remove in order to get the desired data. For example if you set up all 3 of the below joins between KPI Links and Org Hierarchy as Default it will only give you KPIs which are linked at all 3 levels which may not be what your end user wants. Expecting end users to figure out which one to remove is not desirable.

INNER

Planning_KPILinks_STND

SERVICEID

Planning_OrgHierarchy_STND

SERVICEID

INNER

Planning_KPILinks_STND

BUSINESSUNITID

Planning_OrgHierarchy_STND

BUSINESSUNITID

INNER

Planning_KPILinks_STND

DIRECTORATEID

Planning_OrgHierarchy_STND

DIRECTORATEID

Therefore, in complex scenarios like this, Administrators are encouraged to define templates with the desired joins saved into them, which can then be used by end users. For example, if your end users need to create reports which show KPIs linked to Business Units you would create a template with the below join built in.

INNER

Planning_KPILinks_STND

BUSINESSUNITID

Planning_OrgHierarchy_STND

BUSINESSUNITID

2. Action and Task

Commonly used Action and Task related relationships are described below.

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

INNER

Planning_SystemPeriod_STND

SYSTEMPERIODID

Planning_ActionDetails_STND

SYSTEMPERIODID

Allows filtering Actions by system period.

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_ActionProgress_STND

ACTIONID

Allows getting progress information of actions from across multiple months/quarter.

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_ActionFilters_STND

ACTIONID

Allows filtering actions by action filters (tickboxes).

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_ActionCustomFields_STND

ACTIONID

Allows getting information from any custom fields configured for Actions.

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_ActionBudgetYTD_STND

ACTIONID

Allows getting YTD budget information of actions.

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_ActionBudgetByPeriod_STND

ACTIONID

Allows getting budget information of actions from across multiple periods.

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_ProjectDetails_STND

LINKEDACTIONID

Allows connecting an action with its linked project to get corresponding information.

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_ActionLinks_STND

ACTIONID

Allows linking actions with various other elements e.g. Risks, KPIs, etc.

INNER

Planning_ActionDetails_STND

ACTIONID

Project_ProjectDetails_STND

LINKEDACTIONID

Allows connecting an action with its linked project to get corresponding information.

INNER

Planning_ActionDetails_STND

ACTIONID

Planning_TaskDetails_STND

ACTIONID

Allows getting task information of Actions.

INNER

Planning_TaskDetails_STND

TASKID

Project_ProjectDetails_STND

LINKEDTASKID

Allows connecting a task with its linked project to get corresponding information.

INNER

Planning_TaskDetails_STND

TASKID

Planning_TaskLinks_STND

TASKID

Allows linking tasks with various other elements e.g. related plans.

INNER

Planning_PlanningHierarchy_STND

STRATEGYID

Planning_ActionDetails_STND

STRATEGYID

Allows connecting an action with the planning hierarchy to get corresponding information.

INNER

Planning_OrgHierarchy_STND

SERVICEID

Planning_ActionDetails_STND

SERVICEID

Allows connecting an action with the org hierarchy to get corresponding information.

Special notes:

  • To get information on all risks linked to an action, the below join must be created.

INNER

Planning_ActionLinks_STND

RISKID

Risk_RiskDetails_STND

RISKID

  • To get information on all KPIs linked to an action, the below join must be created.

INNER

Planning_ActionLinks_STND

KPIID

KPI_KPIDetails_STND

KPIID

  • To get information on all related plans linked to an action, the below join must be created.

INNER

Planning_ActionLinks_STND

RELATEDPLANID

Planning_RelatedPlans_STND

RELATEDPLANID

  • To get parent tasks of tasks, the Task Details dataset must be joined with itself.

INNER

Planning_TaskDetails_STND

TASKID

Planning_TaskDetails_STND

PARENTTASKID

  • To get information on all related plans linked to a task, the below join must be created.

INNER

Planning_TaskLinks_STND

RELATEDPLANID

Planning_RelatedPlans_STND

RELATEDPLANID

3. KPIs

Common KPI related relationships.

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

INNER

Planning_KPIDetails_STND

KPIID

Planning_KPITargetActuals_STND

KPIID

Allows getting target/actual information of KPIs from across multiple months/quarters/years etc.

INNER

Planning_KPIDetails_STND

KPIID

Planning_KPIFilters_STND

KPIID

Allows filtering kpis by kpi filters (tickboxes).

INNER

Planning_KPIDetails_STND

KPIID

Planning_KPICustomFields_STND

KPIID

Allows getting information from any custom fields configured for KPIs.

INNER

Planning_KPIDetails_STND

KPIID

Planning_KPILinks_STND

KPIID

Allows linking KPIs with various other elements e.g. Risks, Projects, etc.

INNER

Planning_KPIDetails_STND

KPIID

Planning_KPISnapshotView_STND

KPIID

Allows getting target/actual information of KPIs for a designated period (single row of values similar to EIS).

Special notes:

  • To get information on all risks linked to a KPI, the below join must be created.

INNER

Planning_KPILinks_STND

RISKID

Risk_RiskDetails_STND

RISKID

  • To get information on all projects linked to a KPI, the below join must be created.

INNER

Planning_KPILinks_STND

PROJECTID

Project_ProjectRegister_STND

PROJECTID

  • To see the strategies, outcomes or goals linked to this KPI, one of the below joins must be created.

INNER

Planning_KPILinks_STND

STRATEGYID

Planning_PlanningHierarchy_STND

STRATEGYID

INNER

Planning_KPILinks_STND

OUTCOMEID

Planning_PlanningHierarchy_STND

OUTCOMEID

INNER

Planning_KPILinks_STND

GOALID

Planning_PlanningHierarchy_STND

GOALID

  • To see the services, business units or directorates linked to this KPI, one of the below joins must be created.

INNER

Planning_KPILinks_STND

SERVICEID

Planning_OrgHierarchy_STND

SERVICEID

INNER

Planning_KPILinks_STND

BUSINESSUNITID

Planning_OrgHierarchy_STND

BUSINESSUNITID

INNER

Planning_KPILinks_STND

DIRECTORATEID

Planning_OrgHierarchy_STND

DIRECTORATEID

4. Project

Common Project related relationships.

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

INNER

Project_ProjectRegister_STND

PROJECTID

Project_ProjectDetails_STND

PROJECTID

Allows getting the fields activated within the first tab of the project details object.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_QuickUpdate_STND

PROJECTID

Allows getting the fields activated for the quick update screen object.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_TaskDetails_STND

PROJECTID

Allows getting the fields activated within the task object.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_ProjectLinks_STND

PROJECTID

Allows linking Projects with various other elements e.g. Risks, KPIs, etc.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_PreliminaryBudget_STND

PROJECTID

Allows getting the fields activated within the preliminary budget object.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_ImplementationBudgetByMonth_STND

PROJECTID

Allows getting the fields activated within the implementation budget object (monthly values).

INNER

Project_ProjectRegister_STND

PROJECTID

Project_ImplementationBudgetByYear_STND

PROJECTID

Allows getting the fields activated within the implementation budget object (yearly values).

INNER

Project_ProjectRegister_STND

PROJECTID

Project_ImplementationBudgetExtended_STND

PROJECTID

Allows getting the fields activated within the implementation budget object (account level breakdown).

INNER

Project_ProjectRegister_STND

PROJECTID

Project_AssessmentModel_STND

PROJECTID

Allows getting the fields activated within the assessment model object.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_SignOff_STND

PROJECTID

Allows getting the fields activated for sign off objects.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_LessonsLearned_STND

PROJECTID

Allows getting the fields activated for the lessons learned object.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_CustomObject_STND

PROJECTID

Allows getting the fields activated for custom objects.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_CustomTable_STND

PROJECTID

Allows getting the fields activated for custom tables.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_ImageGallery_STND

PROJECTID

Allows getting the fields activated for the image gallery object.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_BoardAndTeam_STND

PROJECTID

Allows getting the fields activated for the board and team object.

Special notes:

  • To get information on all KPIs linked to a Project, the below join must be created.

INNER

Project_ProjectLinks_STND

KPIID

Planning_KPIDetails_STND

KPIID

  • To get information on all risks linked to a Project, the below join must be created.

INNER

Project_ProjectLinks_STND

RISKID

Risk_RiskDetails_STND

RISKID

  • To get information on all reference projects or child projects linked to this project, one of the below joins must be created.

INNER

Project_ProjectLinks_STND

CHILDPROJECTID

Project_ProjectRegister_STND

PROJECTID

INNER

Project_ProjectLinks_STND

REFERENCEPROJECTID

Project_ProjectRegister_STND

PROJECTID

  • To get information on the strategies linked to this Project, one of the below joins must be created.

INNER

Project_ProjectLinks_STND

STRATEGYID

Planning_PlanningHierarchy_STND

STRATEGYID

INNER

Project_ProjectLinks_STND

SECONDARYSTRATEGYID

Planning_PlanningHierarchy_STND

STRATEGYID

  • To get information on the parent project of this project, the Project Register dataset must be joined with itself.

INNER

Project_ProjectRegister_STND

PROJECTID

Project_ProjectRegister_STND

PARENTPROJECTID

5. Risks

Common Risk related relationships.

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

Join Type

Data Object

Join Field

Foreign Data Object

Join Field

Notes

INNER

Risk_RiskDetails_STND

RISKD

Risk_RiskDetailsCustomFieldsInitial_STND

(same can be repeated for Revised and Future custom field datasets)

RISKD

Allows getting custom fields activated for the risk screen.

INNER

Risk_RiskDetails_STND

RISKD

Risk_RiskLinks_STND

RISKD

Allows linking Risks with various other elements e.g. KPIs, Projects, etc.

INNER

Risk_RiskDetails_STND

RISKD

Risk_RiskActionDetails_STND

RISKD

Allows getting information from actions belonging to this risk.

INNER

Risk_RiskDetails_STND

RISKD

Risk_RiskControlDetails_STND

RISKD

Allows getting information from controls belonging to this risk.

INNER

Risk_RiskDetails_STND

RISKD

Risk_RiskControlSolutionDetails_STND

RISKD

Allows getting information from control solutions belonging to this risk.

Special notes:

  • To get information on all KPIs linked to a Risk, the below join must be created.

INNER

Risk_RiskLinks_STND

KPIID

Planning_KPIDetails_STND

KPIID

  • To get information on all projects linked to a Risk, the below join must be created.

INNER

Risk_RiskLinks_STND

PROJECTID

Project_ProjectRegister_STND

PROJECTID

  • To get information on all actions linked to a Risk, the below join must be created.

INNER

Risk_RiskLinks_STND

ACTIONID

Planning_ActionDetails_STND

ACTIONID

  • To see the strategies, outcomes or goals linked to this Risk, one of the below joins must be created.