Common Joins Reference Guide
This article contains: |
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 |
|---|---|---|---|---|---|
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 |
|---|---|---|---|---|---|
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 |
|---|---|---|---|---|---|
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 |
|---|---|---|---|---|---|
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.
INNER | Risk_RiskLinks_STND | STRATEGYID | Planning_PlanningHierarchy_STND | STRATEGYID |
INNER | Risk_RiskLinks_STND | OUTCOMEID | Planning_PlanningHierarchy_STND | OUTCOMEID |
INNER | Risk_RiskLinks_STND | GOALID | Planning_PlanningHierarchy_STND | GOALID |
To see the services, business units or directorates linked to this Risk, one of the below joins must be created.
INNER | Risk_RiskLinks_STND | SERVICEID | Planning_OrgHierarchy_STND | SERVICEID |
INNER | Risk_RiskLinks_STND | BUSINESSUNITID | Planning_OrgHierarchy_STND | BUSINESSUNITID |
INNER | Risk_RiskLinks_STND | DIRECTORATEID | Planning_OrgHierarchy_STND | DIRECTORATEID |
6. Audit |
Common Audit related relationships.
Join Type | Data Object | Join Field | Foreign Data Object | Join Field | Notes |
|---|---|---|---|---|---|
INNER | Audit_AuditDetails_STND | AUDITID | Audit_AuditFindingDetails_STND | AUDITID | Allows getting Findings linked to this audit. |
INNER | Audit_AuditDetails_STND | AUDITID | Audit_AuditRecommendationDetails_STND
| AUDITID | Allows getting Recommendations linked to this audit. |
INNER | Audit_AuditDetails_STND | AUDITID | Audit_AuditLinks_STND | AUDITID | Allows linking Audits with various other elements e.g. Risks. |
INNER | Audit_AuditFindingDetails_STND
| AUDITFINDINGID | Audit_AuditFindingLinks_STND | AUDITFINDINGID | Allows linking Audit findings with various other elements e.g. Risks. |
INNER | Audit_AuditRecommendationDetails_STND
| RECOMMENDATIONID | Audit_AuditRecommendationLinks_STND | RECOMMENDATIONID | Allows linking Audit recommendations with various other elements e.g. Risks. |
7. Incident |
Common Incident related relationships
Join Type | Data Object | Join Field | Foreign Data Object | Join Field | Notes |
|---|---|---|---|---|---|
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_Investigate_STND | INCIDENTID | Allows getting Investigation details for the Incident. |
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_Actions_STND | INCIDENTID | Allows getting Action details for the Incident. |
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_Documents_STND | INCIDENTID | Allows getting Document details for the Incident. |
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_Documents_STND | INCIDENTID | Allows getting Document details for the Incident. |
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_RootCauseAnalysis_STND | INCIDENTID | Allows getting Root Cause Analysis details for the Incident |
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_CustomObject_STND | INCIDENTID | Allows getting Custom Object details for the Incident. |
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_Links_STND | INCIDENTID | Allows linking Incidents with various other elements e.g. Risks. |
INNER | Incident_IncidentDetails_STND | INCIDENTID | Incident_Close_STND | INCIDENTID | Allows getting Document details for the Incident. |
