Reports Based on Multiple Tables - Overview
Click here to watch a video on SmartSimple's integrated reporting subsystem.
In this article, you will create one of the most useful reporting features – the ability to relate the information from two tables into a single report.
In this process you will explore “joining” multiple tables.
In this exercise, you will create a query that uses the company table and the contact table.
1. Create a new report with the following values:
2. Save the report.
You will now select the two tables required in the report.
3. Scroll to the Table list.
4. Set the primary table to the Companies/Accounts.
5. Set the Contact table as an Inner Join.
Inner and Outer Joins
There are two types of joins supported in SmartSimple – inner joins and outer joins.
- An inner join displays the intersection between the two tables.
- This is the most common type of join used, and is the default join type when you add tables to a report.
- When you join the company table and the contact table with an inner join, the report will find the matching record(s) from the contact table related to each company.
- If no match is found, the record from the company is not included in the results.
- If multiple results are found in the contact table, the company information will be repeated.
- An outer join is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" company table.
- This means that if there are no records in the contact table, a row in the report will still be returned for that company, but with NULL values for each column from contact.
1. Open the Report Builder.
2. Add the following fields:
- The Field list now contains fields from both tables.
3. Build, save, and preview the report.
- The company name, city, and state information is displayed from the company table for each matching record from the contact table.
- No company details will be displayed if there are no contacts.