Analogies often prove to be helpful and this is no exception. My first introduction to reports in Salesforce was positive and the various videos helpful. The one area that left me without clarity were the relationships among standard reports, custom reports and report types.
“I prefer to think of the report type as a data source object to which a visual control is connected”
Every report is built upon a foundation of … what? Data, of course. But what is the data source, and how is it created? From the developer perspective I prefer to think of the report type as a data source object to which a visual control is connected.
The SQL View
Relational databases permit the creation of views. A view is a subset of the data returned by a stored query. The information is a set of fields (columns) returned in a row set. The query that supports the view can be simple or complex and can touch one or many tables. Clauses may be added to filter, sort and otherwise massage the data.
A report type is essentially a data source with an underlying view. The primary difference between this and a traditional database view is the level of complexity with a report type being the more limited.
Inner and Outer Joins
A report type can have a base object (table) with up to three cascading joins. I use this description because there can be only one join per object. The following is not permissible:
However, this is permitted:
Note that you can specify whether the join is inner or outer at each level.
These joins have limitations. Each joined object must have a lookup or master-detail relationship with the parent above it. This is in contrast to an SQL join that allows multiple filter conditions.
The SELECT clause
A view needs to return a set of columns and Salesforce makes this simple. The above figure, showing accounts, contacts and opportunities, returns all fields on each object by default.
More fields may be added by clicking the Edit Layout button. This allows you to traverse the object tree in the reverse direction, towards the parent.
Assume that you need to query the opportunities object and return data from opportunities and accounts associated with opportunities. While you could use the above query it is actually easier to make the base object opportunities without using joins.
Now we can edit the layout. This is the default field list:
On the right side there is a drop-down list for the included objects. In this case there is only opportunity. The critical option is the link just below the list: “Add fields related via lookup.” This link opens a dialog that allows any field from a parent object to be included in the view.
One of the limitations of both the report type and the report itself is filtering. This is not to say that Salesforce does not provide excellent filtering language in the report because it does. The limitation comes from the restricted join capabilities on the report type.
Know Your Environment
I come from a SQL database background and my goal is to create a separate table or object for anything that can have multiple values. For example, let’s assume that my business sells widgets that can have a finite number of add-ons such as a carrying bag, charger or external speaker. The SQL mindset is that this list could grow, so we do not want to put check boxes on the widget object. Instead, we create a separate accessories object with a many to many relationship with widgets.
Unfortunately, this type of a relationship is difficult if not impossible to construct in a report type and therefore a report particularly if you need to have a filter such as “list all widgets that were purchased with a charger.”
In this instance we would need to include those fields directly on the widget object in order to make them available for filtering on the report.
The take home lesson is become an expert in Salesforce reporting before you design your data structure!