Difference between revisions of "Reports Based on Multiple Tables - Overview"
From SmartWiki
(→Inner and Outer Joins) |
|||
| Line 28: | Line 28: | ||
* An '''inner join''' displays the intersection between the two tables. | * 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 [[Adding a Table to a Report|add tables to a report]]. | + | :*This is the most common type of join used, and is the default join type when you [[Adding a Table to a Report|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. | + | :*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 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. | + | :*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. | * 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. | + | :*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'''. | 1. Open the '''Report Builder'''. | ||
| Line 44: | Line 44: | ||
2. Add the '''following fields''': | 2. Add the '''following fields''': | ||
| − | [[Image:Reps98.png]] | + | :[[Image:Reps98.png]] |
| − | The '''Field''' list now contains fields from both tables. | + | :*The '''Field''' list now contains fields from both tables. |
3. '''Build''', '''save''', and '''preview''' the report. | 3. '''Build''', '''save''', and '''preview''' the report. | ||
| − | [[Image:Reps97.png]] | + | :[[Image:Reps97.png]] |
| − | The company name, city, and state information is displayed from the company table for each matching record from the contact table. | + | :*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. | + | :*No company details will be displayed if there are no contacts. |
{{PrevNextStart}} [[Creating a Public – Internet Enabled Report]] | {{PrevNextStart}} [[Creating a Public – Internet Enabled Report]] | ||
| − | {{PrevNextMid}} [[Using an Outer Join]] | + | {{PrevNextMid}} [[Using an Outer Join]] {{PrevNextEnd}} |
| − | {{PrevNextEnd}} | ||
[[Category:Reports]] | [[Category:Reports]] | ||
Revision as of 09:40, 30 August 2013
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.
