Salesforce Reporting:
Traditionally business analysts, database users and application maintenance & support analysts who are used to working with hierarchical databases use Structured Query Language or SQL to create reports. Often these reports require writing complex queries joining multiple tables to produce the desired result. Even though Salesforce is a point and click tool with in-built and custom report generation features, the underlying entity-relationship used by Salesforce is no different to aforementioned hierarchical databases.
Understanding how to generate reports in Salesforce by correlating it to the SQL join methods will help users transition from traditional RDBMS world to Salesforce way of generating reports. This blog is intended to explain that correlation in greater detail.
Let’s assume that we have a parent-child structure in Salesforce, DEPARTMENT being the parent and EMPLOYEE being the child.
With the above structure in place, we can extract five different types of reports as described below:
- Parent records with child records
- Parent records with or without child records
- Parent records without child records
- Child records with or without parent records
- Child records without parent records
Scenario 1: Parent Records with Child Records
In a traditional world, this is called an ‘Inner Join’. If we generate a report for this scenario, it would ONLY include the parent record that has a child record. For the above example, the report would only include Accounting, Sales & Marketing departments. Operations would not be included in this report because the child record for Operations does not exist.
Steps to creating this report in Salesforce:
- Create a report type with Departments as the primary object.
- Select Employees as the related object
- In A to B Relationship, select Each “A” record must have at least one related “B” record.
- Save this report type and create a report using the Reports tab.
- When you generate this report, you will notice that Operations is not included in the Report because it doesn’t have any employee (child) records. You can change the format of the report to Summary.
Scenario 2: Parent Records with or without Child Records
In a traditional world, this is called a ‘Left Outer Join’. If we generate a report for this scenario, it would include the parent record with or without a child record. For the above example, the report would include all the departments.
Steps to creating this report in Salesforce:
- Create a report type with Departments as the primary object.
- Select Employees as the related object
- In A to B Relationship, select “A” records may or may not have related “B” records.
- Save this report type and create a report using the Reports tab.
- When you generate this report, you will notice that Operations is also included in the Report along with other departments.
Scenario 3: Parent Records without Child Records
In a traditional world, this is called a ‘Left Outer Join with empty child condition’. If we generate a report for this scenario, it would include ONLY parent records without any child record. For the above example, the report would include only Operations department.
Steps to creating this report in Salesforce:
- Create a report type with Departments as the primary object.
- Select Employees as the related object
- In A to B Relationship, select “A” records may or may not have related “B” records.
- Save this report type and create a report using the Reports tab.
- Add a Cross Object filter to the report to generate parent records (Departments) without child records (Employees).
- When you generate this report, you will notice only Operations department would be pulled since it doesn’t have any child record.
Scenario 4: Child Records with or without Parent Records
In a traditional world, this is called a ‘Right Outer Join’. If we generate a report for this scenario, it would include the child records with or without a parent record. For the above example, the report would include all the Employees.
Steps to creating this report in Salesforce:
- Create a parent with child (Inner Join) report type.
- Change the report type to ‘Joined’ and click on the Add Report Type button.
- Select the child report as the second report for the Joined Report.
- For the child report, add a filter to set parent as Null.
- When you generate this report, you will notice that all the Employees with or without Departments are extracted.
Scenario 5: Child Records without Parent Records
In a traditional world, this is called a ‘Right Outer Join with empty parent condition’. If we generate a report for this scenario, it would ONLY include the child records without a parent record. For the above example, the report would only include Ryan since all the other employees are assigned to a Department.
Steps to creating this report in Salesforce:
- Create a report for the child.
- Add a field filter to set parent as Null.
- When you generate this report, you will notice only Ryan since he does not have any department.
