This post is the second in the Salesforce Standard Reporting series and serves to outline the capabilities and constraints of the standard Report Builder. I’m using the term Report Builder loosely here as a term that groups the majority of the on-platform standard reporting capabilities.
The key message of this series is simple; a solid understanding of how the Salesforce standard reporting tools work in practice, and the reporting patterns supported, can avoid the necessity to employ additional, expensive off-platform reporting tools. The earlier this thinking is applied, the greater the likelihood of success.
Report Builder Capabilities
The following sections outline the key capabilities of the Report Builder with a view to establishing the context within which the supported reporting patterns can be described.
- Fields
- Report Formats
- Filters
- Bucket Fields
- Formulas
- Report Generation
- Report Charts
The right-hand-side of the Report Builder UI displays the sections and fields defined within the selected Report Type. All reports are based on one principal Report Type, whether Standard or Custom.
Note, at a conceptual level it can be useful to mentally picture the data presented by the report type as single-big-table of denormalised data (just rows and columns like a spreadsheet) with the maximum number of rows equating to the number of child records at the lowest level.
Report Builder supports 4 formats;
1. Tabular
A simple view comprised of an ordered set of columns as-per a spreadsheet, with no summarisation of data, the lowest level input records are presented.
2. Summary
Extends the tabular view to enable specified field values to be used to group input records with subtotals per grouping.
3. Matrix
Extends the summary view to enable both row and column groupings, as per Pivot tables in the Excel context.
4. Joined
Enable multiple sub-reports (blocks) to be added to a single report, with each block being linked to a specific report type and configured with its own fields, filters and sorting.
With a joined report input records can be grouped across the blocks using fields from common parent objects; such fields are listed under Common Fields. The common parent object must be applicable across all report types added.
Each report type added to a report has a Dynamic Filter (e.g. “My Accounts”, “My Team’s Accounts”), a date field filter (which requires a date field to be specified, plus a range), and custom filters where any field in the report type can be filtered against static value or relative date value criteria. If a historical trending report type is used then a Historical Date filter is added which allows selection of up to 5 snapshot dates.
Bucket fields can be defined which map a list of input field values against a bucket field output value. The bucket field can then be added as a column, to provide a summarised view of the input data. Bucketing can be useful for use cases such as grouping strategic accounts or extensive ranges of data into smaller distinct set of High, Medium, Low range values. Note bucket fields aren’t available for Joined Reports.
Report Builder supports Custom Summary Formulas and Cross Block Custom Summary Formulas. In the former case, the min/max/sum/average value of numeric fields can be used as inputs to a formula expression, the output of which displays at the selected summary level. Each report can have 5 such formulas. Cross Block Formulas extend the same approach, but enable block specific inputs from multiple blocks to be assembled into a single expression.
Once defined a report can be run to generate the output. In view mode Report Builder supports filter manipulation, data export, a printable view (html) and the ability to schedule future runs with email distribution to named users or public groups.
Report Builder enables summarised report data to be presented on a chart located directly within the report. Summary groups can be added to the chart as rows or columns (depending on the type of report vertical/horizontal bar etc.), with a selected aggregated value available on the opposing axis. Up to 2 levels of grouping can be selected per chart, with combination charts enabling additional aggregated values to be plotted; this potential ranges from 1 to 3 values depending on the type of chart. Cumulative Line Charts enable the aggregate values to be plotted as a cumulative figure.
Report Builder has other capabilities not mentioned in the outline above; multiple currency handling, conditional highlighting and the ability to invoke a parameterised report via URL are notable examples. However for the purpose of setting context, the above provides a sufficient coverage.
Report Builder – Benefits
The Report Builder tool is a classic transactional reporting tool, with a deliberate focus on usability over complexity. In my view this is definitely the right approach; report production should be an end-user concern whenever possible. A technically-oriented, complex report builder would clearly detract from that possibility. Self-sufficient end-users, confident that they can report and track accurately can be the best adoption advocates possible. This best-case scenario has the added benefit of removing any potential resource bottleneck associated with the implementation project/IT team delivering all the reporting outputs. The usability of the report builder correlates to its functional simplicity, which in turn imposes constraints on the type of reporting possible. It is the case however that the Report Builder does provide a good level of coverage for the majority of transactional reporting patterns, some examples are listed below.
- Transactional Reports
- Summary Reports
- Exception Reports
- Embedded Analytics
- Dashboards
- Scheduling and Distribution
- Report Notifications
- Report Snapshots
Basic tabular reports are easy to produce; the WYSIWYG drag-and-drop editor provides a rich, intuitive design environment for basic transactional reports.
Basic summary reports that can be satisfied within the grouping limits are straightforward to produce. For summary reports, 3 levels of grouping can be applied, for matrix reports it is possible to have 2 column groupings and 2 row groupings, so 4 in total. Excel allows an unlimited number of row or column fields in a PivotTable report (limited by system memory); in this light 4 appears restrictive.
An often overlooked capability of Report Builder is the Cross Filter (the Row Limit filter is another example). Cross Filters allow explicit control over the join type (with = inner, without = outer) and enable the production of exception reports such as “Accounts without Cases”. Within a Cross Filter, up to 5 sub-filters can be added that increase the selectivity applied to the related object. This type of filter is typically used in conjunction with rollup summary fields to cover scenarios such as “Closed Opportunities over time, without a particular product”.
Embedded data visualisations within report outputs, or page layouts provide a convenient method for combining the headline statistics and underlying detail in a single view. In the former case, if you then consider the native ability to schedule the generation and distribution of reports, the potential business utility is considerable.
Report Builder produced reports underpin standard dashboard components, enabling a single report to deliver both the transactional view and data visualisations drawn from the same data. The dashboard features are extensive but key features beyond the obvious are Dashboard filters and Dynamic dashboards. In regard to the former, up to 3 filters can be applied to drill into the data, each filter may have up to 10 (50 hard limit) filter options. With the latter it’s possible to define a single dashboard that inherits the record visibility of the viewing user. This relatively new capability removes the historic requirement to maintain duplicate dashboards set to run with different specified users. Dashboard filters can be applied to Dynamic dashboards providing a high degree of flexibility and through consolidation a significant reduction in the number of dashboards required.
Scheduling reports (and dashboards) with automated distribution to Salesforce users is easy to do and a very effective communication tool.
Report Notifications is a recent feature addition and a very interesting one. In short, users subscribe to reports by providing a time or recurrence pattern to run the report, user-defined conditions to evaluate against the generated report data and selected actions (email, post, Salesforce1 Notification, Apex code) to invoke if the condition logic evaluates to true. This powerful capability enables reports, typically passive in nature, to be proactive drivers of action. The notification model also avoids the common issue that scheduled reports, regardless of content, tend to lose their effectiveness over time.
Report Snapshots enable a tabular or summary report output to be mapped to fields on a custom object; scheduled generation of the report populates the custom object with the report data. This feature is limited to the population of up to 2,000 records in the target object per execution.
A final benefit associated with Report Builder is the wealth of packaged reports provided as standard and also available on the AppExchange. It always makes sense to utilise the available reports as far as possible and build any exceptions. It’s surprising how many implementations ignore the packaged reports and expend time (and money) re-creating similar outputs.
Report Builder – Limitations
As introduced in the preceding section, Report Builder is implemented to deliver a robust, simple set of reporting functions focused on end-user usability over complexity. The following set of limitations highlight some of the reporting patterns not directly supported by the Report Builder.
- Object Relationships
- Comparative Analysis
- Date Ranges
- What-if Analysis
- Formulas
- Sub Reports
- Report Distribution
- Historical Trend Reporting
Report Builder consumes report types in the construction of reports, each report type is limited to 4 object relationships meaning parent to child relationships, a further 56 objects can be referenced by lookup. Note, if a single report contains columns from more than 20 objects an error is thrown. The parent to child relationship limit is key to understand when deciding upon the primary object for a custom report type.
Report types do not support the addition of sibling objects, the parent to child object relationships are strictly linear. One common case where this constraint can manifest is the production of comparative analysis style reports which contrast records in one object against another. For example, consider a scenario where date-based, budget values and actual values are held in separate objects, both linked to a parent cost centre object. With a joined report it’s possible to bring the data together and group by cost centre – but there’s no direct means to group the data by date to deliver a side-by-side analysis of actual versus budget per date period. There are techniques to mitigate this issue, the next post in the series will cover such concepts.
Where input records represent a date range between two boundary date fields (Start Date and End Date for example), the Report Builder requires that one of the date fields is used in date-based grouping. Individual dates within the range can’t be referenced. Reporting tools often provide a pivot function with dynamic columns that enables ranges to be exploded within the reporting layer, avoiding the data storage overhead of storing the transactional data at the lowest level.
Report Builder does not enable the input data to be adjusted, thereby precluding scenario-based, what-if analysis type reports. There is of course the option to export the data to Excel, which does cater for this requirement.
Report Builder supports sub-total, grand total and cross-group summary formula fields, but not row-level formula fields. For such requirements, a custom field can be added to the object, but the means to isolate the formula field to a report would help maintain a lean configuration.
Joined reports, dashboards, embedded analytics and report charts provide effective, but simple, capabilities for the delivery of composite report views. Many reporting tools support the concept of sub-reports, or nested data, where multiple transaction level views, with or without common relationships, can be combined.
A common complaint raised in relation to the standard reporting tools is the inability to schedule a report for distribution to individuals who are not Salesforce users.
The Historical Trending Custom Report Types expose data for the last 3 months only, and Reporting Snapshots are limited to 2,000 (summarised) records per execution. One or both of these capabilities will provide an effective historical data analysis solution for many customers, at larger data volumes and for larger organisations the limits may be intractable.
The next and final post in the “Salesforce Standard Reporting” series outlines the key data model techniques that can help maximise the potential of the standard reporting tools.