Category Archives: reporting

Opportunity Historical Trend Reporting – Missing Opportunities

Here was a real conundrum. A client asked me to resolve why she (a sysad) couldn’t see any Opportunities in her Opportunity Historical Trending report when all her other Opportunity reports showed all Opportunities without issue.

I did the usual first principles checks:

  1. Was Opportunity Historical Trending even enabled? YES
  2. Were the right fields being tracked for trending? YES
  3. Was there actually Field History Tracking enabled for Opportunities? YES
  4. Were there actually rows in the Opportunity__hd Historical Tracking SObject? YES
  5. Could I reproduce the problem as a System Admin? YES

The usual rule of thumb when something isn’t visible in an Opportunity report is either something about the running user’s role or the sharing model. In this org, the sharing model was public read/write. The running user was a System Administrator who has View ALL Data, but wait …. the system administrator’s role was on a leaf at the end of one of two tree main branches. The sysad’s branch was a sibling to the sales org’s branch. The org had no top level role.

Org Roles

But why would the system administrator be able to see all Opportunities when running a standard Opportunities report if their role was buried deep inside a disconnected-from-the sales-org branch?

The answer lies here in the SFDC Help. I quote (note, the org was not using Territory Management):

If the Organization has the Opportunity Organization-wide Defaults set to “Public Read/Only:”
A Standard Report Type will show all the Opportunities the Running User can see, and that meet the criteria.

A Custom Report Type will only show Opportunities owned by a User with the same Role as or a Role below them in the Hierarchy. In this case if the missing Opportunities are owned by a User with a Role higher in the Hierarchy, the Running User will need to click on that Role in the Hierarchy selector (this can be found in the report detail page under its name).

OK, so this explains why standard reports can show all data that the sysad can see, regardless of where their role sits. But isn’t an Opportunity Historical Trend report a standard report? Doesn’t it come out-of-the-box from Salesforce?

The answer is No, Historical Trend Reports are not standard reports, they are custom report types.
Evidence for this:

So, the verbiage in the SFDC Help re: Custom Report Types applies – the running user’s role matters and since their role in the hierarchy was in a sibling branch to the sales org’s branch, there was not even a way to click the Hierarchy chain to see the data.

Bottom line

For your average SMB org, the out-of-the-box Opportunity reports may never be supplemented with Custom Report Types so it is easy to be lulled into thinking that any report you create for users on Opportunities will at least, let you, the sysad, see all data. But that odd duck – Historical Trending Reports – is treated as a Custom Report Type, and, when used for Opportunities (a common use case), your placement in the role hierarchy matters. In this case, the sysads were not at a top level in the hierarchy (and there was no top level either) so the problem ensued.