Salesforce Report Data into Snaplogic

@Ajay_Chawda @eric.sou @anilv

We are likely to add a new Report-related Snap to the Salesforce Snap Pack in the coming months but here are some brief notes on using the REST Snap Pack to access SFDC Report Data.

I would also be interested in your feedback - the API output looks rather tricky to deal with (the data and columns are decoupled) and I’d love to learn what feedback you have both to what report data you wish to access, how easy it is to use and any additional comments you may have.

I’m going to reference internal test environments here, so customers may need to adjust for their situation/tenant etc. I’ve also redacted some information that is sensitive.

1. I leveraged an existing SFDC Force Connected App that we use for testing internally. A Connected App can be created by choosing Setup in Salesforce UI Home:

Then choose Apps > App Manager:

and create a New Connected App:

Provide a Connected App Name, and API Name, and a Contact Email (the values for the first two are your choice). Under “API (Enable OAuth Settings)”, enable the “Enable OAuth Settings” checkbox and provide the following under “Callback URLs”: https://elastic.snaplogic.com/api/1/rest/admin/oauth2callback/rest.

In terms of scopes to select, our test app has a large number of scopes selected but start with these are pair down to just what you need:

Leave all other settings untouched. After saving the App, View it to see the Consumer Key and Consumer Secret values you will use in the REST OAuth2 Account.

2. I created a REST OAuth2 account, using the Key and Secret above for the “Client ID” and “Client secret” settings respectively. I’m using our SFDC domain in the OAuth2 Endpoint and Token URL fields:

https://<tenant>.my.salesforce.com/services/oauth2/authorize
https://<tenant>.my.salesforce.com/services/oauth2/token

Clicking the Authorize button, will allow the OAuth dance to begin and an access and refresh token to be acquired and saved to the account.

Salesforce doesn’t use refresh token expirations (hence the -1 above), but our Platform treats it as if it is an hour expiration.

3. A REST GET Snap (with the account created above set) can now be used to attempt to list Reports:

https://<tenant>.my.salesforce.com/services/data/v50.0/analytics/reports

note, I’m not adding any pagination handling here

This will return data that have relative URL references to different reports:

Another REST GET Snap can then be used after a JSON Splitter to call each report:

The output is very verbose:

The factMap field appears to contain the row-by-row data, and the columns look like they are detailed under reportExtendedMetadata.detailColumnInfo

This will require some sophisticated use of SnapLogic Mappers, Splitters and expressions to convert to a key:value-type set of pairings:

That is where I stopped my investigation for - the pipeline .slp file is below and can be imported:

salesforce-sfdc-rest-reports_2021_01_22.slp (7.3 KB)

The SFDC Reports API can be consulted further for specific API use cases: Salesforce Developers

Please do let me know any questions or comments you have.