In this example, we'll explore how to join the Payment Applied,
Payment, Job, Invoice, and Invoice Totals data sets to produce a
meaningful report.
Understanding Data Joining
In this guide, we'll walk you through the process of joining data sets to create insightful reports that can help you make informed decisions and improve your business.
Before we dive into the practical steps, let's understand the concept of data joining. Data joining is a method used to merge information from two or more data sets based on a common attribute, typically an ID or a shared column.
Preparing for Report Creation with Joins
|
Selecting the Right Data Sets
We'll explore the following example in this tutorial: how to join the payment applied, payment, job, invoice, and invoice totals data sets.
Follow these steps to practice creating a Report with Joins.
From a blank worksheet, add a table and select the relevant data set and columns to start building the report.
1. To create a table, click the ‘Add Element + symbol’ (upper left corner next to PAGE OVERVIEW).
2. Click ‘TABLE’.
3. Click ‘TABLES AND DATASETS’.
4. Search for a specific data set by typing in a keyword. In this example, we will start with Payment Applied.
5. Click on the Payment Applied dataset to see a preview of the information.
6. Review the ‘Select Columns’ options to include or exclude specific data from the report. In this example, we will deselect everything except for Applied Amount.
TIPs
Double click ‘Select all’ to deselect all boxes. Re-check the boxes according to what data you want displayed in the table.
When selecting columns to include, always select the option that says ‘local’ NOT ‘Utc’. Local is specific to your account.
Columns can be added or removed at any point, even after the report has been created.
7. Click ‘Select’ (bottom right corner) to add the table.
Identifying Common Columns
To successfully join data sets, you'll need to identify common columns between them. Typically, this involves finding matching IDs or keys. This common column serves as a bridge between data sets.
Look for common columns, such as IDs or keys, to establish connections between data sets.
Look for common columns, such as IDs or keys, to establish connections between data sets.
Performing the Data Join
8. Click anywhere on the report. Click the arrow next to Payment Applied (lower left corner) and click Join.
9. Click NEW → TABLES AND DATASETS.
10. Type “Payments” into the search → select the Payments data set.
11. Deselect all columns by double clicking the “Select all” check box.
12. In this example, we will select the following (5) boxes: Payment Number, Payment Date, Payment Type Name, Payment Status, and Billing Customer Name
13. Click “Select” (lower right corner).
Data Set Examination
14. Start by examining the Join keys columns within each data set to find any common attributes.
Payment Applied Data Set Join Keys
Payment Data Set Join Keys
Selecting Common Columns
15. Choose the common column from each data set that you want to use for joining. In this example, we have selected "Payment id" from the "Payment" and “Payment Applied” data sets.
Ensure that you're linking the correct attributes to avoid errors. If a join is successful, data will populate at the bottom of the screen in the JOIN OUTPUT section.
Ensure that you're linking the correct attributes to avoid errors. If a join is successful, data will populate at the bottom of the screen in the JOIN OUTPUT section.
16. Click “Preview Output” (upper right corner).
Data will be color coded by data set. Check a box in the corresponding data set to show or hide data columns.
Above the data shows a visual representation of the Payment and Payment Applied join.
Adding Additional Data Sets To Join
17. Click the + symbol next to SOURCES to join another data set (upper right corner).
18. Click NEW → TABLES AND DATASETS
19. Search for and select the “Job” dataset.
20. Deselect all columns. Select the “Job Number” column. 18
21. Click “Select”.
22.Identify common join keys. Choose the common column from each data set. In this example, we have selected "Job id" from the "Payment Applied" and “Job” data sets.
You may need to change the “Join with” source to find a common join key with the “Selected source” for joins with more than 2 data sets.
23. Click “Preview Output” (upper right corner).
24. Preview your report to see the visual representation of the combined data. Check for successful matches and data population.
The following section repeats steps 23-30 to join the Invoice Totals and Invoice data sets with the report.
⚠️ See Customizing Your Report at the end of this article if you wish to skip this section for final recommendations.
25. Click the + symbol next to SOURCES to join another data set (upper right corner).
26. Click NEW → TABLES AND DATASETS
27. Search for and select the “Invoice” dataset.
28. Deselect all columns. Select the “Invoice Number” column. 23
29. Click “Select”.
30. Identify common join keys. Choose the common column from each data set. In this example, we have selected "Job id" from the "Job" and “Payment” data sets.
You may need to change the “Join with” source to find a common join key with the “Selected source”. In the screenshot below, we have selected the “Job” data set to find a common join key with the “Invoice” data set.
31. Click “Preview Output” (upper right corner).
32.Preview your report to see the visual representation of the combined data. Check for successful matches and data population.
33.Click the + symbol next to SOURCES to join the final data set (upper right corner).
34. Click NEW → TABLES AND DATASETS
35. Search for and select the “Invoice Totals” dataset.
36. Deselect all columns. Select the “Total Amount Paid” column. 37. Click “Select”.
38. Identify common join keys. Choose the common column from each data set. In this example, we have selected "Invoice id" from the "Invoice" and “Invoice Totals” data sets.
39. Click “Preview Output” (upper right corner).
40. Preview your report to see the visual representation of the combined data. Check for successful matches and data population.
41. Click “Done” (upper right corner).
Customizing Your Report
Once you've successfully joined your data sets, it's time to customize your report to make it easily understandable and visually appealing for your audience. See Apply Visual Formatting in How To Create A Basic Report for more information.
Choosing Columns
Select the columns you want to display or hide in your report. Consider what information is most relevant to your audience. Drag and drop columns to rearrange their order.
Formatting
Make your report visually appealing by formatting column names, wrapping text, showing banded rows, and adjusting the layout.
Grouping Data
If applicable, group your data to present it in a more organized manner. 31
FAQs
What are the types of joins I can create on a report?
Click here to learn more about join types from the Sigma Computing website.
You can also click the ? icon during the “Create Join” step to be redirected to the Sigma Computing website article about join types.