Overview
The following vignette outlines how to execute and manage reports in
your Org. Note: These features are still experimental
and are likely to change and/or have bugs. Please take this into
account, and if you run into any issues please consider submitting an
issue HERE
in the GitHub repository so that we can help troubleshoot and fix, if
needed. Thank you!
In order to pull down the results of a report created within
Salesforce all you need to know and have is the report’s Salesforce Id.
If you want to fully leverage the features of the Reports and Dashboards
REST API I recommend reading the following so that you are better
equipped to understand and use the functions within {salesforcer}.
Authenticate
First, load the {salesforcer} package and login either by OAuth 2.0
(SSO) or using your username, password, and security key.
library(dplyr, warn.conflicts = FALSE)
library(salesforcer)
sf_auth()
Running a Report in your Org
In Salesforce there is a dedicated page to displaying the list of
reports in your Org. It typically follows the pattern:
https://na1.salesforce.com/00O/o (replace na1
with your server instance). When you click on a report in the GUI you
should see the results. Below is a screenshot of how a report may look
in your Org. Note the report Id in the URL bar.
Alt
The report Id above ("00O3s000006tE7zEAE") is the only
information needed to pull those same results from an R session, like
so:
my_report_id <- "00O3s000006tE7zEAE"
results <- sf_run_report(my_report_id)
results
#> # A tibble: 14 × 8
#> `Contact ID` `First Name` `test number` `Contact Owner` `Account ID`
#> <chr> <chr> <dbl> <chr> <chr>
#> 1 0036A000002C6MW Rose NA Steven Mortimer 0016A0000035mJ4
#> 2 0036A000002C6MX Sean NA Steven Mortimer 0016A0000035mJ4
#> 3 0036A000002C6MY Jack 99 Steven Mortimer 0016A0000035mJ5
#> 4 0036A000002C6Mb Tim NA Steven Mortimer 0016A0000035mJ8
#> 5 0036A000002C6Mc John 23 Steven Mortimer 0016A0000035mJ8
#> # ℹ 9 more rows
#> # ℹ 3 more variables: `Account Name` <chr>, `Billing City` <chr>,
#> # `Account Owner` <chr>
Note that the sf_run_report() function will, by default,
run the report asynchronously. This means that a report instance will be
requested and then the function will wait to retrieve the results. The
advantage to using an asynchronous report is that the results of such a
report are stored for 24 hours and can be retrieved again saving CPU
resources, if needed. A more in-depth discussion on synchronous
vs. asynchronous reports is available here https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_get_reportdata.htm.
However, for reports with a relatively small number of records a
synchronous report may be faster without having to do the round trip of
creating then querying a report instance.
Filtering a Report on the Fly
The neat thing about using the API is that you can retrieve the
results of a report with different filters applied. This allows you to
obtain exactly the results needed without having to create separate
copies of the same report. It takes some basic understanding of how
report operators need to be supplied to the API, but it is not too
difficult. Below is an example that only includes contact records
created prior to this month and belong to an Account with a non-NULL
Billing City.
# filter records that was created before this month
filter1 <- list(column = "CREATED_DATE",
operator = "lessThan",
value = "THIS_MONTH")
# filter records where the account billing address city is not empty
filter2 <- list(column = "ACCOUNT.ADDRESS1_CITY",
operator = "notEqual",
value = "")
# combine filter1 and filter2 using 'AND' which means that records must meet both filters
results_using_AND <- sf_run_report(my_report_id,
report_boolean_logic = "1 AND 2",
report_filters = list(filter1, filter2))
results_using_AND
#> # A tibble: 14 × 8
#> `Contact ID` `First Name` `test number` `Contact Owner` `Account ID`
#> <chr> <chr> <dbl> <chr> <chr>
#> 1 0036A000002C6MW Rose NA Steven Mortimer 0016A0000035mJ4
#> 2 0036A000002C6MX Sean NA Steven Mortimer 0016A0000035mJ4
#> 3 0036A000002C6MY Jack 99 Steven Mortimer 0016A0000035mJ5
#> 4 0036A000002C6Mb Tim NA Steven Mortimer 0016A0000035mJ8
#> 5 0036A000002C6Mc John 23 Steven Mortimer 0016A0000035mJ8
#> # ℹ 9 more rows
#> # ℹ 3 more variables: `Account Name` <chr>, `Billing City` <chr>,
#> # `Account Owner` <chr>
This second example shows how to return only the Top N number of
records and combine the filter using the logical “OR” instead of
“AND”.
# combine filter1 and filter2 using 'OR' which means that records must meet one
# of the filters but also throw in a row limit based on a specific sort order
results_using_OR <- sf_run_report(my_report_id,
report_boolean_logic = "1 OR 2",
report_filters = list(filter1, filter2),
sort_by = "Contact.test_number__c",
decreasing = TRUE,
top_n = 5)
results_using_OR
#> # A tibble: 5 × 8
#> `Contact ID` `First Name` `test number` `Contact Owner` `Account ID`
#> <chr> <chr> <dbl> <chr> <chr>
#> 1 0033s00001CWqsU Test 1000 Steven Mortimer 0013s00001Aizxg
#> 2 0033s00001CWqsV Test 1000 Steven Mortimer 0013s00001Aizxg
#> 3 0033s00001CWqt7 Test 1000 Steven Mortimer 0013s00001Aizxg
#> 4 0033s00001CWqt6 Test 1000 Steven Mortimer 0013s00001Aizxg
#> 5 0033s00001CWqsW Test 1000 Steven Mortimer 0013s00001Aizxg
#> # ℹ 3 more variables: `Account Name` <chr>, `Billing City` <chr>,
#> # `Account Owner` <chr>
I was able to determine some of the potential ways to filter by first
reviewing the reportFilters element in the existing report
metadata and also reviewing the list of report filter operators.
First, you can always take the report filter specification from the
report metadata and tailor it to your needs. Below is an example showing
how to get that metadata for our report. You can select specific
elements to better understand the structure of the report.
report_details <- sf_describe_report(my_report_id)
report_details$reportMetadata$reportType$type
#> [1] "ContactList"
report_details$reportMetadata$reportFilters
#> [[1]]
#> [[1]]$column
#> [1] "CREATED_DATE"
#>
#> [[1]]$filterType
#> [1] "fieldValue"
#>
#> [[1]]$isRunPageEditable
#> [1] TRUE
#>
#> [[1]]$operator
#> [1] "lessThan"
#>
#> [[1]]$value
#> [1] "2019-07-19T04:00:00Z"
#>
#>
#> [[2]]
#> [[2]]$column
#> [1] "ACCOUNT.ADDRESS1_CITY"
#>
#> [[2]]$filterType
#> [1] "fieldValue"
#>
#> [[2]]$isRunPageEditable
#> [1] TRUE
#>
#> [[2]]$operator
#> [1] "notEqual"
#>
#> [[2]]$value
#> [1] ""
Second, Salesforce has a few API endpoints that tell you the fields
on the report or the report type, more generally, and all the ways you
can declare a filter on a particular field type and. The
reportTypeMetadata element returned on the report
description also has detailed information on how to filter the report.
For example, it already contains the start and end dates that would be
applied when using the “LAST_MONTH” filter value on a date field.
report_details$reportTypeMetadata$standardDateFilterDurationGroups[[6]]$standardDateFilterDurations[[1]]
#> $endDate
#> [1] "2024-10-31"
#>
#> $label
#> [1] "Last Month"
#>
#> $startDate
#> [1] "2024-10-01"
#>
#> $value
#> [1] "LAST_MONTH"
Digging into the metadata of the report will allow you to better
understand what filters you can set when filtering on the fly. In the
example below you should notice that the field names on the report do
not match the names of the typical API field names for the object, so
please review carefully the fields on the report. For example, the
CREATED_DATE report field is based on the
CreatedDate object field.
# report fields
report_fields <- sf_list_report_fields(my_report_id)
head(names(report_fields$equivalentFieldIndices))
#> [1] "CONTACT_CREATED_ALIAS" "Contact.My_External_Id__c"
#> [3] "IS_EMAIL_BOUNCED" "REPORTS_TO"
#> [5] "ADDRESS2_ZIP" "LAST_ACTIVITY"
report_filters <- sf_list_report_filter_operators()
unique_supported_fields <- report_filters %>%
distinct(supported_field_type) %>%
unlist()
unique_supported_fields
#> supported_field_type1 supported_field_type2
#> "date" "address"
#> supported_field_type3 supported_field_type4
#> "string" "double"
#> supported_field_type5 supported_field_type6
#> "picklist" "textarea"
#> supported_field_type7 supported_field_type8
#> "encryptedstring" "percent"
#> supported_field_type9 supported_field_type10
#> "int" "url"
#> supported_field_type11 supported_field_type12
#> "reference" "datetime"
#> supported_field_type13 supported_field_type14
#> "boolean" "phone"
#> supported_field_type15 supported_field_type16
#> "datacategorygroupreference" "currency"
#> supported_field_type17 supported_field_type18
#> "location" "html"
#> supported_field_type19 supported_field_type20
#> "id" "time"
#> supported_field_type21 supported_field_type22
#> "email" "multipicklist"
# operators to filter a picklist field
picklist_field_operators <- report_filters %>%
filter(supported_field_type == "picklist")
picklist_field_operators
#> # A tibble: 9 × 3
#> supported_field_type label name
#> <chr> <chr> <chr>
#> 1 picklist equals equals
#> 2 picklist not equal to notEqual
#> 3 picklist less than lessThan
#> 4 picklist greater than greaterThan
#> 5 picklist less or equal lessOrEqual
#> # ℹ 4 more rows
Managing your Reports
The API also allows you to perform many admin functions like
creating, copying, updating, or deleting reports and report instances.
Take advantage of these functions as needed to keep your Org’s report
list well-maintained. Below is a simple flow of creating, updating, and
deleting a single report, but the amount you’re able to customize is
completely up to you. The {salesforcer} package should support any
operation that the Reports and Dashboards REST API supports.
# first, grab all possible reports in your Org
all_reports <- sf_query("SELECT Id, Name FROM Report")
# second, get the id of the report to update
this_report_id <- all_reports$Id[1]
new_report <- sf_copy_report(this_report_id)
#> Naming the new report: 'ReportName - Copy'
# third, update the report (2 ways shown)
my_updated_report <- sf_update_report(new_report$reportMetadata$id,
report_metadata =
list(reportMetadata =
list(name = "Updated Name!")))
my_updated_report$reportMetadata$name
#> [1] "Updated Name!"
# alternatively, pull down its metadata and update the name
report_details <- sf_describe_report(new_report$reportMetadata$id)
report_details$reportMetadata$name <- paste0(report_details$reportMetadata$name,
" - UPDATED AGAIN!")
# update the report by passing the metadata
my_updated_report <- sf_update_report(new_report$reportMetadata$id,
report_metadata = report_details)
my_updated_report$reportMetadata$name
#> [1] "Updated Name! - UPDATED AGAIN!"
# fourth, delete that report using its Id
success <- sf_delete_report(new_report$reportMetadata$id)
success
#> [1] TRUE