When you create reports, you join together fields from different datasets in Rippling into a single report. You'll use different types of joins to get the results you want.
What is a join?
A join is a way to combine data from different datasets together, matching it up to create a new combined dataset. In Rippling, datasets are referred to as objects. But for the purposes of this article, we’ll use the term datasets when explaining joins.
Think of a dataset as a table with columns and rows. When you have two datasets you want to join, think of these as two tables.
For example, say we have a table where each row represents an employee. Each employee row has columns that contain the employee’s ID and name.
| Employee ID | Employee Full Name |
|---|---|
| 93 | Lou Cormier |
| 34 | Jarrod Steuber |
| 36 | Amy Bhatia |
We also have a table where each row represents a device. Each device row has columns that contain the device operating system and the assigned employee’s ID.
| Device OS | Employee ID |
|---|---|
| macOS | 93 |
| Windows | 34 |
| Linux | 36 |
When you join the two tables, the result is a joined dataset that includes data from both tables.
| Employee ID | Employee Full Name | Device OS |
|---|---|---|
| 93 | Lou Cormier | macOS |
| 34 | Jarrod Steuber | Windows |
| 36 | Amy Bhatia | Linux |
Join types
Rippling supports three types of joins.
- Left. The results will contain one row for every field from the left dataset. If there are no matching variables in the right dataset, the columns in those rows will be empty.
- Right. The results will contain one row for every field from the right dataset. If there are no matching variables in the left dataset, the columns in those rows will be empty.
- Inner. The results will only contain rows that include values for both the left and right datasets. Rows where either value is empty will not be included in the report.
Let’s extend our example and say we want to build a report that includes a list of employees and the operating systems of their assigned devices. We can use different join types to get different results.
With a left join, the report will include a row for every employee. If they have a device assigned, its operating system will be listed. If they don’t have a device assigned to them, the column will be empty.
| Employee ID | Employee name | Device OS |
|---|---|---|
| 93 | Lou Cormier | macOS |
| 34 | Jarrod Steuber | Windows |
| 36 | Amy Bhatia | Linux |
| 98 | Norton West |
With a right join, the report will include a row for every device. If a device is assigned to an employee, the assigned employee’s ID and name will be listed. If the device isn’t assigned, the employee ID and name will be empty.
| Employee ID | Employee name | Device OS |
|---|---|---|
| 93 | Lou Cormier | macOS |
| 34 | Jarrod Steuber | Windows |
| 36 | Amy Bhatia | Linux |
| macOS |
With an inner join, the report will only contain rows for employees with assigned devices. There will be no empty columns in this report. Any employee without an assigned device, or device that isn’t assigned to an employee, will be excluded from the report.
| Employee ID | Employee name | Device OS |
|---|---|---|
| 93 | Lou Cormier | macOS |
| 34 | Jarrod Steuber | Windows |
| 36 | Amy Bhatia | Linux |
Join keys
When datasets are joined together, a field in each dataset is selected to act as the join key. The join key is a unique identifier that tells Rippling how to link the two datasets and match up the data.
In our previous example, the Employee ID field is used as the join key because it exists in both datasets. We know that rows in each table that have the same Employee ID belong to the same employee.
Let’s look at a different example. Say you want to create a report that matches up data from Rippling with data from Zendesk. In Zendesk, there are people associated with different actions. They may create tickets, be assigned to tickets, or close tickets. Rippling understands that the people completing actions in Zendesk are the same people who are employees in Rippling and is able to automatically link the two together.
When you join data from Rippling and Zendesk in a report, Rippling automatically uses the Employee and Assignee fields as join keys because they’re unique values that represent the same person.
Joining on multiple keys
You can add multiple join keys if you want to further limit the results in the report. The report will only include results that match all of the join keys you add. This can be useful if you need to add more fields to help you uniquely identify something in the results.
Let’s use our example of employees and device operating systems. Let’s say we have a dataset with a list of employee names, their start dates, and their levels. Because the company is large, there are multiple people with the name John Smith.
| Employee Full Name | Start Date | Level |
|---|---|---|
| John Smith | 01/23/2021 | 4 |
| John Smith | 04/13/2019 | 7 |
We also have a dataset where each row represents a device. Each device row has columns that contain the device operating system, the assigned employee’s name, and the date the device was assigned to the employee.
| Device OS | Employee Name | Date Last Assigned |
|---|---|---|
| macOS | John Smith | 01/23/2021 |
| macOS | John Smith | 04/13/2019 |
| Windows | John Smith | 04/13/2019 |
If we left join these datasets using the employee name and include the Employee Name, Device OS, and Level fields in the report, we’ll end up with a dataset that looks like this.
| Employee Name | Device OS | Level |
|---|---|---|
| John Smith | macOS | 4 |
| John Smith | macOS | 4 |
| John Smith | Windows | 4 |
| John Smith | macOS | 7 |
| John Smith | macOS | 7 |
| John Smith | Windows | 7 |
Because the join key wasn’t unique, the results include multiple rows and we’re unable to identify who is who. However, if we include Start Date + Date Last Assigned as secondary join keys, our results will be filtered so that each row is also matched up based on those dates. The resulting dataset will look like this.
| Employee Name | Device OS | Start Date | Level |
|---|---|---|---|
| John Smith | macOS | 01/23/2021 | 4 |
| John Smith | macOS | 04/13/2019 | 7 |
| John Smith | Windows | 04/13/2019 | 7 |
Multiple joins
You can join multiple datasets together by performing multiple joins in a single report.
For example, let’s say you want to create a report showing which employees are enrolled in medical insurance, dental insurance, vision insurance, life insurance, a Health Savings Account (HSA), and a Flexible Spending Account (FSA). You can do this using multiple joins.
Scenario 1: Report with all employees, regardless of enrollment
First, we’ll join the Employee dataset with the Insurance dataset. The Insurance dataset has information about which employees are enrolled in medical, dental, vision, and life insurance. We want all employees to be included in the report, so we’ll use a left join.
At this point, the results will have one row for each employee and columns showing enrollment in medical, dental, vision, and life. Because we used a left join, the report is showing rows for all employees, regardless of whether they’re enrolled in any of the insurance plans.
Next, we’ll join the newly created combined dataset with the HSA dataset to see who is enrolled in an HSA. We’ll use a left join so that all employees are included in the report, even if they’re not enrolled in an HSA.
At this point, the report has one row for each employee and one column showing enrollment in medical, dental, vision, life, and HSA. Because we used a left join, the report includes rows for employees who aren’t enrolled in an HSA.
Last, we’ll join the resulting dataset with the FSA dataset to see who is enrolled in an FSA. We’ll use a left join so that all employees are included in the report, even if they’re not enrolled in an FSA.
Now the report has one row for each employee and one column for each type of enrollment: medical, dental, vision, life, HSA, and FSA. Because we used left joins, the report includes rows for employees who aren’t enrolled in any particular plan.
Scenario 2: Report with employees who are enrolled in FSA and have any combination of medical, dental, vision, or life enrollment
Let’s say instead you want your report to only include rows for employees who are enrolled in an FSA plus any one of these types of insurance coverage: medical, dental, vision, or life.
First, we’ll use a left join to combine the Employee dataset with the Insurance dataset so the report contains one row for each employee, regardless of their enrollment in medical, dental, vision, or life.
Next, we’ll use an inner join to combine the resulting dataset with the FSA dataset. As a result, the report will include one row for each employee who:
- Is enrolled in any combination of medical, dental, vision, or life insurance
AND
- Is enrolled in an FSA
Employees who aren’t enrolled in an FSA and at least one of the other types of insurance will not be included in the report.
Automatic join behavior
When you create a report that uses data from multiple objects, Rippling automatically makes some choices about how to join the data. You can change the joins to get the results you want.
Automatic joins and join keys
Because Rippling understands the relationships between the different fields in the apps it integrates with, it’s able to automatically link together related fields and use them as join keys.
- Rippling automatically creates a left join using the employee. Depending on the data you’re including, this could be the employee field, a ticket assignee, an ATS candidate, etc.
- If you’ve included date fields, Rippling automatically may also use the dates as join keys.
If the join keys that Rippling automatically creates don’t work for you, you can change them at any time.
Automatic aggregation
Rippling automatically aggregates the data in your report when:
- You created the report using a recipe that includes aggregation.
- The fields you included in the report have to be aggregated before they can be joined. Typically this happens when you’ve included date fields. The data will first be grouped by date and then aggregated. Non-numeric fields will be counted, and numeric fields will be summed.
Again, because Rippling understands the relationships between the fields in different modules, it’s able to intelligently decide what makes sense to aggregate. If the aggregations that Rippling automatically creates don’t work for you, you can change them.
Review the joins in a report
To review the joins in a report, click the edit icon next to Joins at the bottom of the available data panel.
On the left, you’ll see details about how the report data is joined together. On the right, you’ll see a preview of your report.
Whenever you add data from a new object to your report, Rippling automatically creates another join. You can use the steps below to modify the join if needed.
Modify the joins in a report
To change the way data in a report is joined, follow these steps:
- Click the edit icon next to Joins. A screen will open that shows the joins in your report.
- To change the join type or join keys, find the join you want to change and click the Edit icon.
- To reorder the joins, grab the six dots on the left side of a box and drag it to its new position. Rippling will automatically recalculate the join keys. If needed, you can then adjust them manually.
- Click Continue to save your changes.
When you’re editing a join or aggregation, some of the options might be grayed out if other joins or aggregations rely on them. For example, if you have multiple joins in a report, you won’t be able to change the left and right datasets for a join that results in a combined dataset used by another join.