Changes

Jump to: navigation, search

Reports Based on Multiple Tables - Overview

719 bytes added, 15:51, 8 July 2019
no edit summary
{{SeqReportPageHeader}}
 
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 Click the following values:9-square menu icon on the top right of your page.
[[Image:Reps100.png]] 2. '''Save''' the report.: {{Icon-Menu}}
You will now select the two tables required in 2. Under the reporttab '''Tools, '''select '''Reports. '''
The list of available reports in your system will be displayed. 3. Scroll to the Click the '''Table+ icon ''' on the top left, above the [[List View Overview|list]] of reports, to create a '''New Report.'''
4. Set the primary table to the The '''Companies/AccountsNew Report '''page is displayed, with fields for you to fill out.
:: [[ImageFile:Reps99New report pag.png|700px|border]] 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.For this example exercise, fill out the following fields with the corresponding information: 
* An '''inner joinReport Name: ''' displays the intersection between List Companies and Contact* '''Report Description: '''Joins the two tablescompany and contacts table. 
:*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]]5. Click the '''Save '''button.
:*When you join You will now select the company table and the contact table with an inner join, two tables that are required in the report will find the matching record(s) from the contact table related to each company.
:*If no match is found, the record from 6. Click on the company is not included new tab called '''[[Report Recordsets|Recordsets]] '''in the resultstop row of tabs. 
:*If multiple results are found in the contact table, the company information will be repeated: [[File:Recordsets tab.png|750px|border]] 
* An 3. Scroll to the '''outer joinTable''' 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 tablelist.
:*This means that if there are no records in 4. Set the contact primary table, a row in to the report will still be returned for that company, but with NULL values for each column from contact'''Companies/Accounts'''.
[[Image:1. Open the '''Report Builder'''Reps99.png]]
:25. Add Set the '''following fieldsContact''':table as an '''Inner Join'''.
:=Inner and Outer Joins=There are two types of joins supported in [[Image:Reps98.pngSmartSimple]]– inner joins and outer joins. :*The An '''Fieldinner join''' list now contains fields from both displays the intersection between the two tables.
:3* 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]]. '''Build''':* When you join the company table and the contact table with an inner join, '''save'''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, and '''preview''' the reportcompany information will be repeated.
:[[Image:Reps97.png]] :*The company name, city, and state information An '''outer join''' is displayed 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 for each matching record from the contact 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''':: [[Image:Reps98.png]]:* The '''Field''' list now contains fields from both tables.3. '''Build''', '''save''', and '''preview''' the report.: [[Image: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.
{{PrevNextStart}} [[Creating a Public – Internet Enabled Report]]{{PrevNextMid}} [[Using an Outer Join]] {{PrevNextEnd}}
[[Category:Reports]]
2,299
edits

Navigation menu