Please enable Javascript to access the support portal. Here are the instructions how to enable JavaScript in your web browser.

What are Common Joins for EPM Live Reporting?

This article highlights the common joins between tables in the Reporting Database.  You can build these and similar joins when creating custom reports in EPM Live Analytics.

Note: As each customer site may be configured differently, the tables (specifically the LST list tables) may be joined differently, depending on which lists have lookup fields to other lists.

1. Project Center & Associated Child Lists

The above examples are three DIFFERENT join examples from three different reports.  

The associated child lists have a lookup to Project Center, so join off the Project lookup field's ID.  Use this same type of join for any Project Center associated list (any list that has a lookup to Project Center).

2. Project Portfolios & Project Center

The associated Project Center list has a lookup to Project Portfolios, so join off the Portfolio lookup field's ID.  Use this same type of join for any Project Center parent list (any list to which Project Center has a lookup field).

2.1. Portfolio, Project, Task Data

Multiple join levels may be added. This join (Project Portfolio > Project Center > Task Center) would allow data from all three lists to show in a single report.  Use this same type of join for any Project Center associated list (any list that has a lookup to Project Center).

3. Resource Plan Data

To report on resource plan data from one calendar period to the next (such as in a pivot table), join with the Calendar table.  Join with the PeriodUID field.

3.1. Resource Plan Data with Additional Project Data

3.2. Resource Plan Data with Additional Resource Data

4. Cost Plan Data

To report on cost plan data from one calendar period to the next (such as in a pivot table), join with the Calendar table.  Join with the PeriodUID field.

5. Cost Plan Data with Additional Project Data

6. Snapshot Data

Initially, the snapshot tables are empty.  When snapshots are taken, either manually or scheduled, that populates the snapshot table with the list data.  The period table has the date the snapshot was taken.

7. Timesheet Data

7.1. Timesheet Data with Additional Project Data

Typically, joins are done with unique identifiers.  However, in the case of timesheets, the project ID is not stored in the timesheet database; data is stored associated to the project name.  So, create a join to the project name/title.

7.2. Timesheet Data with Additional Project & Resource Data

The resource SharePoint Account is the unique identifier between the Resource Pool and the Timesheet table.

Have more questions? Submit a request

0 Comments

Article is closed for comments.