Filed under: Administration,Featured

If you are an SQL person, you may have wondered how SQL constructs manifest themselves in Salesforce reports.  Here are some things to consider from an SQL frame of reference.

Report Types are the root of all reports and define the objects and fields that can be used in a report and is analogous to the join portion of a WHERE clause.  Report Types must follow the parent-child relationships between objects defined via Lookup and Master-Detail fields.  It is not possible via Report Types to arbitrarily use any field on one object to link to any field on another object like SQL can. (Joined Reports provide some limited functionality.)

Report Types offer LEFT OUTER JOIN functionality, but a Custom Report Type must be defined. The setup offers “A records may or may not have B records” as an option when defining the join relationship.

RIGHT OUTER JOIN is a bit more complex.  To keep things simple, consider a report that just needs two tables.  First, the Report Type chosen for the report must only include the RIGHT side table so that the report can access all the rows regardless of related records existing.  Then, there are two ways to get the fields from the LEFT side table.  One way is to create cross object formula fields on the RIGHT side table that pull in the LEFT side table fields.  The other way is to use “Add fields related via lookup” feature of Custom Report Types to pull in the LEFT side table fields.  Either way will show blanks if the field used for the join is empty. (BTW, it can be difficult to determine what is in a Report Type just by the name.  That is a different discussion).

FULL OUTER JOIN is available by choosing Joined as the report format in the Report Builder.  Joined reports pull multiple Report Types into one report and the join shows all records from all Report Types.

PIVOT is available by selecting Matrix format in the Report Builder.

SUM, AVG, MAX and MIN are supported in Summary and Matrix reports.

Reports automatically prevent double counting.  Consider a report with two objects.  When summing up a numeric field on the parent record, it will not double count the values even though there are many detail records.

DISTINCT is a painful missing construct in reports.  Suppose Sales Territory is a picklist field on Opportunities and a metric of total Amounts per Sales Territory is needed.  This is not possible since there is no way to count up the number of unique Sales Territory values that occur in the report to use as the denominator of the metric.

Salesforce provides a variant of SQL called SOQL, but reports cannot be based off of an SOQL query.  To base a report on an SOQL query, you might consider using Conga Composer which is an app on the Appexchange.  It can run an SOQL query and put the results in Excel, Word or PowerPoint.  There is a license cost.  Keep in mind, however, that SOQL is not a complete mirror of SQL.  It may still require multiple SOQL queries loaded into Excel and then use Excel features to join them.

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2009-2015 Hayata Takeshita