Difference between revisions of "Reports Based on Multiple Tables - Overview"

From SmartWiki
Jump to: navigation, search
(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 10:40, 30 August 2013

caption 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:

Reps100.png

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.

Reps99.png

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.
  • 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:

Reps98.png
  • The Field list now contains fields from both tables.

3. Build, save, and preview the report.

Reps97.png
  • 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.




Previous.png Creating a Public – Internet Enabled Report Using an Outer Join Next.png