Share this post

Salesforce External Object Reports using OData

Salesforce shops, including ourselves, have been eagerly anticipating external object support with reports. Starting in Winter ’17, you can build native reports with on-demand access to external data sources such as Oracle, SQL Server, or Amazon Redshift. External objects are powered by Salesforce Connect and provide clicks-not-code data access for admins, devs and general users.

And the data that gets connected to Salesforce is only ever accessed in real-time, so we never make a copy of the data in Salesforce.

What are External Objects?

In Salesforce, data is stored in what they refer to as Objects. There are standard objects like Accounts, Contacts, Opportunities and Cases that come out of the box with the Sales Cloud, Service Cloud, Community Cloud and App Cloud. Developers and admins can also build Custom Objects to track data like Projects, Milestones, Timesheets, Budgets. Salesforce Connect introduces a 3rd type of object, an External Object.

External Objects act much like Standard and Custom Objects, only the data isn’t stored in Salesforce, the data is stored in an external system like SAP, Oracle, Workday or Amazon. Salesforce Connect calls out to external OData endpoints, an industry standard for building and consuming RESTful APIs, and gathers information about the data structure it exposes, and then stores metadata within an External Object.

How Reporting works with External Objects

Salesforce users can now run reports that access external data from cloud and on-premise sources including warehouses, data marts, data lakes and more. With Salesforce Connect external objects, you get real-time connectivity that is self-service for Salesforce Report developers.

Who Does What?

Salesforce Connect:

External Object Reports is a feature of Salesforce Connect. Salesforce Connect is an offering from Salesforce that provides seamless integration of data across boundaries.

OData endpoint:

External objects require an OData adapter for reporting databases, and this is provided by Progress DataDirect. My shameless plug: Progress was the first member to join the OData technical committee and provides connectors distributed by eight of the top nine leaders in Gartner’s Magic Quadrant for BI.

Reporting Considerations

There are various reporting considerations when dealing with External objects in Salesforce. I’ve listed these at the bottom of this post

Getting Started With External Object Reporting

Notes: Tutorial assumes an external data source was created using DataDirect Cloud. A self-hosted version of the Salesforce OData service is also available as Hybrid Data Pipeline.

Setup Home > External Objects > Edit

Select “Enable Reports” on the external object.

Enabling reports creates the following in the Other Reports report type category.

•A report type for the external object

•A report type for each lookup relationship in which both objects allow reports

Now we have a report blending standard Salesforce objects with external objects stored outside the platform. When I close the browser, the external data is not stored in the platform and only accessed on demand when running the report.

This feature is not necessarily designed to replace your existing enterprise reporting platform, but it’s a great way to augment Salesforce data and make it available to users who spend a lot of their time in the platform. My employer will be interested in integrating some of our data warehouse information with Salesforce reports.

The most popular reporting databases we’re already getting requests for include:

• When you run a report, your org performs a request callout for each external object in the report.

• When you run a report that’s in the joined format, your org performs separate request callouts for each block.

• If the URL length of a report callout approaches or exceeds 2 KB, your org splits the request into multiple HTTP calls, with each URL being less than 2 KB. However, with the cross-org adapter for Salesforce Connect, report callouts that exceed 2 KB fail and are not split into multiple calls.

• When a report includes an external object, the report fetches up to 2,000 records for the primary object. This limit applies regardless of whether the primary object is a standard, custom, or external object.

• If the report has no child objects, the total number of rows is 2,000 or fewer.

• If the report has a child object, the total number of rows can be greater or less than 2,000, depending on how many child records are fetched.

• As is true for all callouts for external objects, report callouts are limited by the Salesforce Connect adapters in use.

• For large external data sets, report callouts typically access only a subset of the external data.

• If the report results in few or no rows, try customizing the report to obtain more relevant external object rows.

• If the report includes summary fields and formulas, those aggregate values likely reflect only a subset of your data. To improve the accuracy of the aggregate values, try customizing the report to obtain more relevant data.

• Reports can include or reference external objects via lookup, external lookup, and indirect lookup relationship fields. If those relationship fields aren’t Salesforce IDs or the name fields of parent objects, the report returns only empty values for those relationship fields

5 thoughts on “Salesforce External Object Reports using OData”

Also an important limitation is to understand that Salesforce assigns Salesforce IDs to the external object records but these are only temporary assignments and could change. So use the foreign key id’s on the records and not Salesforce IDs for the external object records.

I did a talk on External Objects at Dreamforce last year. Everyone thinks of External Objects as talking to data sources OUTSIDE of Salesforce but never about looking inside. Eg want to create a report on all deleted opportunity records in the Recycle Bin? or all manual sharing rules on a particular object? with External Objects you can: https://www.youtube.com/watch?v=2cTfkhKsvIs

Hello There,
You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.
I am new to payroll module. Could you please tell me How to see the net amount to be paid to each employee in a particular period.
This report will run after the monthly prepayment and payment process run.