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

From SmartWiki
Jump to: navigation, search
(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 10:41, 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