Sync Salesforce Reports
Ambition can translate tabular and summary Salesforce reports into Ambition Metrics, which can be suitable for handling complex multi-Object Metrics.
Reports vs Metric Builder with the Managed Package
If your Organization has installed Ambition's Managed Package, we recommend using the Metric Builder whenever possible as it uploads data faster than through the report sync interface. The report sync intervals, while customizable, will update every 10 minutes+ vs ~1 minute from an Object.
The need to filter by a Date Field or creating a Most Recent Value metric are the most common use case for needing to use Reports.
Date Field Filter example - Desire to track Appointments that were Set and Held the same Week. Wish to give metric credit on Appointment Held Date, but also filter by Appointment Set Date = This Week. This would not be possible with the traditional metrics built on the Salesforce Object, but are the perfect use case for Report metrics!
Below is a comprehensive guide to setting up Salesforce Reports to be used as data sources within Ambition. We advise working through the entire document if this is the first time that your Organization has synced Salesforce Reports with Ambition.
What formats and fields are supported/required?
How will Ambition receive my report data?
How do I set up the Salesforce report?
↳ How do I need to format my Tabular report?
↳ How do I need to format my Summary report?
How do I sync a Salesforce report with Ambition?
What if I have questions about my Salesforce report setup?
Report Requirements
To ensure the best chance of success when syncing reports into Ambition, the following requirements must be met:
- Only Tabular and Summary reports are supported. Matrix and Joined reports are not supported. If the report you wish to sync will be used to create a Most Recent Value metric, a Summary format report is required.
- The report must contain a Lookup(User) Field column indicating who will get credit.
- The report must contain a Date Field column indicating when credit should be applied.
- If the report is Tabular, it must contain an Object ID column to ensure record uniqueness.
- The report cannot contain a chart.
- If the report contains Summary Formula* fields they must only use basic math (+, -, *, /).
↳ Formula Notes/Special Considerations:
- Ambition does support Salesforce's Power of 1 functionality.
- Based on the way data is collated/serialized before pushing to Ambition, we cannot support a Formula Field that only applies to aggregate records ex. a converted ratio. Ambition will need aggregate fields to be included as detail fields, as well.
- *Ambition does not support Row-Level Formulas.
Options for Receiving Report Data
Ambition offers two ways of receiving data from Salesforce reports. The appropriate option will be selected upon sync. You can use a different option per report to fit your organization's needs.
If your Organization has connected Ambition and Salesforce through the API-Only option, the Salesforce API option will be the single route to sync reports with Ambition.
1. Ambition Package = Ambition's Managed Package will be used to push report data to Ambition. This method does not require any API calls and supports reports with up to 2000 rows of data.
Using the Ambition package to receive report data is an appropriate choice if your organization wishes to conserve API calls and/or the report needing to be synced is less than 2000 rows.
Salesforce enforces a limitation of 2000 rows per report export, so any report using the Ambition Package option cannot return more than 2,000 records for the given time range.
If your report is filtered to a Date Range larger than Last 30 days, it will be overridden for compliance with the Salesforce limitation of 2,000 rows for a given time range. If your report is filtered to a Date Range larger than Last 30 Days, and is well below the 2000 row limit, reach out to gethelp@ambition.com for assistance.
Report updates will be pushed to Ambition every 10 minutes.
2. Salesforce API = Salesforce's API will be used to query report data. This method requires 1 API call per 2000 rows and can support a report of up to 75,000 rows. Important to note that Salesforce limits the number of synchronous report runs to 500 per hour. Salesforce's report limit documentation found here.
Using the Salesforce API to receive data report data is an appropriate choice if your organization is flexible with API calls, the frequency of which is configurable per report, and the report needing to be synced is more than 2000 rows of data.
If your report is filtered to a large time frame such as All Time or Last Year, be mindful that any row of data that's dated more than a calendar year in the past will not be processed. Any row of data that's dated before your organization began using Ambition also will not be processed.
Report updates will be queried based on the selected sync interval. Available sync interval options are every 10 minutes, 20 minutes, 1 hour, 3 hours, 6 hours, 12 hours, and 1 day.
Prepare and Format the Report
1. Placing the reports in an Ambition specific folder helps ensure that synced reports will not be accidentally deleted or modified in a way that disrupts the data flow to Ambition
If the desired report already exists, save a copy (because we will be modifying and making potential formatting changes) into the new Ambition folder. Otherwise create a new report.
If an Ambition specific folder does not already exist, create a Report folder in Salesforce called Ambition (DO NOT TOUCH).
|
|
Salesforce Classic |
Salesforce Lightning |
2. Establish report filters and add filter logic as necessary.
Set Date Field to the field correlating to when credit should be received for metric.
If you are using the Ambition Package option for receiving data, set the Range to Today, This Week, or Last 30 Days. If you select a Range larger than 30 days, it will be overridden for compliance with the Salesforce limitation of 2,000 rows for a given time range.
Salesforce Classic |
Salesforce Lightning |
3. Establish the report format and add the appropriate columns and groupings per report type.
Accepted Report Formats
Tabular Format
Add a User Field, Date Field, and Object Id column to the report.
Ensure that the Date Field column matches the Date Field filter used above.
Additional columns of meta data can be included on the report, but at least one User field, one Date field, and the Object ID are required. Columns of meta data like Opportunity Name, Stage, and Type can be seen in the example below.
Salesforce Classic Example
Salesforce Lightning Example
Summary Format
Unlike Tabular reports which will work more like a custom object, a summary report's data will be limited/summarized, to a single row of data as available, per user, per day.
When syncing a summary report, you will select the User and Date field that the report is grouped by.
Group by User Field and then Group by Date Field.
Ensure that the Date Field matches the Date Filter used above.
Add a numerical column to the report, hover over the column header and click the arrow, click Summarize, and then select Sum. Selecting Average is only supported with the Salesforce API transport method.
The data visible in Ambition will be the user field, date field, and value found in the numerical column.
Salesforce Classic Example | Salesforce Lightning Example |
Sync Salesforce Report with Ambition
1. Open the left navigation and click Administration > Data > Integrations.
2. Click Edit in line with the Salesforce integration.
3. Click on Data Configuration. Select Reports.
4. Click + Sync New Report.
5. Search for the desired report by searching for the Report Name or Report ID. Click on the correct search result.
Ensure the proper formatting and field requirements listed above are followed on the selected report.
5. Select the appropriate fields based on the report type.
If Tabular, select the relevant Date Field and Source ID Field (Object ID)
If Summary, select the relevant Date AND User Field that the report is grouped by.
↳ As noted in the above requirements, a Lookup(User) Field is required
6. Select the desired Transport Method.
If using the API-Only Option for connecting Ambition and Salesforce, Salesforce API will be the only Transport Method available.
Ambition Package = Ambition's Managed Package will be used to push report data to Ambition. This method does not require any API calls and supports up to 2000 rows of data.
Salesforce API = Salesforce's API will be used to query report data. This method requires 1 API call per 2000 rows and can support a report of up to 75,000 rows.
If Salesforce API is selected as the transport method, select the appropriate Sync Interval.
Report updates will be queried based on the selected sync interval. Available sync interval options are every 10 minutes, 20 minutes, 1 hour, 3 hours, 6 hours, 12 hours, and 1 day.
7. Click the Sync Report button.
8. If the report successfully syncs with Ambition, you will be prompted to create a metric using the report's field.
A report will not upload until a metric is built using its fields since a metric is needed to create raw records.
Click Create Metric when prompted or click Add under Metric Library at any time to create a metric using the report's data.
Note About Metric Creation for Summary Reports
When selecting Who Gets Credit? and When Do They Get Credit? within the metric form, synced summary reports will display the User Field that the report is grouped by and was chosen upon sync as "User" and the Date Field that the report is grouped by and was chosen upon sync as "Date".
If you need to verify the User or Date Field's name and API name as it's listed in Salesforce, navigate back to Data Configuration > Reports > and click "Edit" on the respective summary report.
9. Once a metric is created, the report will be uploaded into Ambition.
Troubleshooting Report Sync
If you are running into an error upon sync, reach out to gethelp@ambition.com so that we can help diagnose the issue.
Including a screen shot of your report and it's filters in your original reach out will help us identify the issue and assist quickly!
Comments
0 comments
Article is closed for comments.