A key contributor to the successful implementation of Salesforce is an informed approach to reporting. I’ve said this many times and have been meaning to complete this short set of posts for a long time. The basic idea is that the delivery of key reports and analytics on-platform using the standard reporting tools should be a primary objective for all implementation projects. This approach may not always be feasible where requirements are complex or atypical, however the standard reporting features should be exhausted before an off-platform business intelligence tool or analytics cloud is employed. Such complementary services come at a significant price point.
In order to maximise the potential of the standard reporting features, both expertise and experience must be applied early in the project. Early consideration of reporting requirements enables a data model design that supports the production of required reports via the standard tools; this should never be an afterthought but often is.
This short series of three posts serves to outline the key concepts, the reporting patterns supported by the standard tools and critically the implications on the data model to be considered. This latter point is imperative as an effective Salesforce physical data model strongly reflects the reporting of data; a key differentiator from a pure physical data model applied to a traditional RDBMS.
The first post in the 3 part series focuses on the foundational concept of Report Types.
Report Types
In the context of outlining the capabilities of the standard reporting tools, the following concepts are significant.
- Standard Report Types
- Custom Report Types
- Historical Trending Custom Report Types
Report types abstract the data model into logical reporting entities on top of which reports are built. This model can’t be circumvented.
Standard Report Types are automatically created for all Standard Objects and for Custom Objects when the “Enable Reports” or “Allow Reports” checkbox is checked. Standard report types can’t be edited, new fields are automatically added.
For unrelated objects, or parent objects in lookup relationships only, a single Standard Report Type is created, named as per the object plural name. E.g. “Rubric Scores”. Note there are exceptions to this where enabled standard features (e.g. Salesforce to Salesforce) add object relationships which result in additional report types (e.g. “Rubric Entries with Connections”).
For parent objects in master-detail relationships multiple Standard Report Types can be created; one per master-detail relationship. E.g. “Rubric Entries with Questions”. Additional report types are added for each lookup relationship on the child object (e.g.”Rubric Entries with Questions and Score”, “Rubric Entries with Questions and Criterion”), where Score and Criterion are parent object relationships of the lookup type. Additional report types are also added for each additional master detail relationship (e.g. “Accounts with Assessments and Courses”).
Standard report types are not created for grand-child relationships, or for child objects in master detail relationships.
Note, the applied naming convention uses the child field label for lookup relationships and the parent object plural label for master detail relationships.
A key feature of Standard Report Types is the ability to navigate up a lookup relationship from Child to Parent. The example above “Rubric Entries with Questions and Criterion”, is logically a parent to child (master-detail), then child to parent (lookup) traversal.
Custom report types (CRT) enable the definition of a report type that includes up to 4 levels of parent-to-child relationship, regardless of whether each relationship is lookup or master-detail type.
Each CRT has a layout which can be configured with custom sections and selective field inclusion across the objects. Objects and Fields can also have CRT specific labels added and a flag set to include selective fields in new reports by default.
Note, an often overlooked feature of Custom Report Types is the ability to add a parent object field regardless of whether the relationship is master-detail or lookup (although the UI indicates lookup only, both work). This de-normalisation technique is incredibly powerful; up to 5 levels of the parent object hierarchy can be traversed.
CRTs therefore enable the production of specialised, convenient report types providing a focused set of denormalised fields for a specific purpose. Many successful Salesforce implementations adopt a model where Salesforce Administrators or Developers produce the required CRTs, and the end users build the reports.
Historical Trending is a reporting feature, added in the Winter ’14 release, which can be enabled and configured on a per-object basis. For each enabled object a special CRT is added (e.g. “Rubric Entries with Historical Trending”), containing up to 8 selected fields (numeric, date, picklist, currency). Up to 3 months of data is available via the CRT.
The next post in the “Salesforce Standard Reporting” series will cover the capabilities of Report Builder to consume the date exposed by report types.