Difference between revisions of "Reports Based on Multiple Tables - Overview"
From SmartWiki
(→Inner and Outer Joins) |
(→Inner and Outer Joins) |
||
| Line 40: | Line 40: | ||
:*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'''. |
| − | 2. Add the '''following fields''': | + | :2. Add the '''following fields''': |
:[[Image:Reps98.png]] | :[[Image:Reps98.png]] | ||
| Line 48: | Line 48: | ||
:*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]] | ||
Revision as of 09:41, 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.
