How do I build joins between tables?

This article explains how to build joins between reporting tables in EPM Live Analytics.

1. Navigate to the EPM Live Analytics Reports Page

  1. On the navigation menu, select Reports to open the Business Intelligence Center.
  2. Click EPM Live Analytics.

2. Select New Report

  1. Click the New menu drop down arrow.
  2. Select Report.

3. Simple Selection of Data Sources

Navigate to the Data Sources tab. Here you will select from which reporting database table you want to get data for the report.  This is for simple selection of a single reporting database table, or of multiple tables that are automatically joined.

To join tables in check boxes mode you should just check the tables you want to join one after another. When you check the first table, all tables that cannot be joined to it will automatically be disabled. After you will select the next table to join, the list of available checkable tables will be refreshed.

  1. Select the Distinct check box
  2. If you've already been creating or editing reports, you may select from the Recent Data Sources menu.  
  3. Otherwise, select the check box for the desired table(s).  
  4. Some tables automatically have joins built, so in some cases, multiple tables can be selected.  When a table is selected, any other tables that are available via an automatic join will show with black text and the selection check box still white.  
  5. Any tables that are not available via an automatic join will show as grayed out.  

Note: Refer to the article that explains which reporting database tables contain what type of data: click here.

4. Select Advanced to Build Data Source Joins

Click Advanced to manually join data sources (reporting tables) together.

In Advanced Mode, you will have to select the fields that exist in the tables you are joining, which builds a relationship between the tables.  To join two tables, they must have fields with identical entries. For example, both the LSTProjectCenter and the LSTIssues tables have the same ProjectID field that contains the ID for the project.

5. Select First Table

The Data Sources drop-down menu lists the reporting database tables.  Select the first table.  

6. Insert Row Below and Select Additional Table

  1. Click the Insert Row (below) icon.
  2. Select the second table that will be joined to the first selected table.  

7. Select Field That Exists in Each Table

TheJoin Fielddrop down menu is a list of the fields contained in the selected table. Select the field that has identical entries in both tables.  Typically, this is an ID field, since that guarantees a unique value.  Though, it doesn't always have to be an ID field.

8. Additional Levels of Joins

Data can be joined across multiple reporting tables.  For example, if you wanted a report that showed resource allocation information, and include details about the resource as well, you would join the LSTResourcePool table as well as the EPG tables EPG_RPT_Calendar and EPG_RPT_CapacityPlanner (which include the calendar periods and resource planner allocation data).

9. Function Buttons of Data Sources

  • Delete: Click this button to delete the row.
  • Insert Row button (above): Click this button to insert a row above the row.
  • Insert Row button(below): Click this button to insert a row below the row.



Please sign in to leave a comment.