Reports Based on Multiple Tables - Overview

From SmartWiki
Revision as of 10:41, 30 August 2013 by Arthur Lathrop (talk | contribs) (Inner and Outer Joins)

Jump to: navigation, search

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:


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

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