What is Historical Trend Reporting?
Historical Trend Reporting is a niche reporting feature available on Salesforce. It allows for reporting on the standard up-to-the-minute state of the business as well as analyze day-to-day and week-to-week changes in forecasts, cases, opportunities, or values in custom objects. Historical Trend Reporting uses a special custom report type designed to focus on changes between five snapshot dates, for example, five business days or five business weeks. The changes in data can be visually represented in tables, charts and dashboards. Salesforce preserves historical data for four months i.e. three previous months and the current month.
Why use Historical Trend Reporting?
A common use case of Historical Trend Reporting is to help monitor an organization’s sales pipeline to ensure it consists of enough activities to meet current and future sales goals. It can highlight deals whose value increased or decreased in the last three months or deals that moved into or out of a given target period.
These reports can compare historical and current values of key aspects of opportunities, such as status, amounts and dates, to see how the sales pipeline has changed over time. Historical Trend Reporting can help focus on deals over a given value. For example, if you want to only highlight deals in your pipeline that are worth more than $10,000, Historical Trend Reporting can filter out any deal worth more than $10,000 in the set timeframe.
Moreover, Historical Trend Reporting can identify deals that have been pushed out i.e. those taking more time to close than anticipated. It can also help identify shrinking deals. For example, targeting the deals in the sales pipeline that have decreased in value since yesterday. The report shows you the deals whose value was greater yesterday than today. (This example is illustrated below.)
NOTE - Every field in a report comes with certain considerations and historical fields are no exception. Following are two key considerations for historical fields:
- Historical fields available to each user depending on the fields that the user can access. If the user’s permissions change and a given field is no longer visible, that field’s historical data also becomes invisible.
- Each historical field has the same field-level security as its parent field. If the field permissions for the parent field change, the historical field’s permissions change accordingly.
How to set up Historical Trend Reporting
We will create a sample Report on Salesforce. In order to build a Historical Trend Report, we need to setup Historical Trend Reporting first. NOTE – Historical Trend Reporting is activated by default for organizations that are created in Winter ‘14 and later.
-
Go to the Setup Screen.
-
From the Quick Find Box, select Historical Trending.
-
Select the objects that you want to perform Historical Trend Reporting on. You can select Cases, Opportunities, Forecasting Items and up to three custom objects. Select Enable Historical Trending.
-
Mention the amount of data you can use to create Historical Trend Reports by using the filters under Configure Data. You can narrow down historic data for Cases, Opportunities and custom objects. For Forecasting Items, the available data is selected for you.
-
Under Select Fields, select up to eight fields to make available for Historical Trend Reporting. Click Save.
After enabling Historical Trending, a new custom report type is available when you want to create new reports. If you enable historical trending on a new field, that field is automatically added to the layout of Historical Trend Report.
NOTE – Longer durations than days or weeks are not recommended. They may result in reports timing out and not returning data.
How to create a Historical Trend Report
Let’s build a sample Historical Trend Report to track changes in an organization’s Sales Pipeline. For this example, we will build a summary report that highlights opportunity amounts and close dates that have changed since yesterday. In other words, let’s identify shrinking deals.
-
Go to the Reports tab and click on New Report.
-
Select the Report Type as Opportunities and expand It. Select Opportunities with Historical Trending. Click on Create.
-
In the Filters Pane, for Show, select All Opportunities.
-
In the Filters Pane, click on Add and select Historical Field Filter.
-
Set Amount (Historical) to Any Historical Date and greater than. Set the last field to Field. Here, we are comparing the historical amount with whatever amount is in the Amount – Today column, and not with a specific amount. Click OK, and then Run Report.
We have created a simple report that flags deals that have shrunk since yesterday. In this report, current and historical values are shown side by side for easy comparison. Changed dates and amounts are highlighted in red or green. Typically, decrease in value is indicated by red color.
NOTE – You can change the color-coding for amount changes. Click the down arrow above the Change column and click Reverse Colors.
Limitations on Historical Trend Reporting in Salesforce
- Salesforce retains data only for the last three months and the current month.
- Each Historical Trend Report can accommodate up to 11 fields. In Opportunities reports, it includes the standard pre-selected fields which cannot be disabled.
- Formula Fields are not supported.
- The Summary report format is not supported.
- The Report Wizard is not supported. Historical Trend Reports can be created only with the Report Builder.
Historical Trend Reporting in Chartio
Another way to do Historical Trend Reporting with more flexibility is in a modern business intelligence application like Chartio. In Chartio you can have access to all fields of all Salesforce objects and build appropriate logic using Visual SQL and field values in data marts to help identify historical trends over time.