Get a report's data in tabular format

This function is a convenience wrapper for retrieving the data from a report. By default, it executes an asynchronous report and waits for the detailed data summarized in a tabular format, before pulling them down and returning as a tbl_df.

Usage

sf_run_report(
  report_id,
  report_filters = NULL,
  report_boolean_logic = NULL,
  sort_by = character(0),
  decreasing = FALSE,
  top_n = NULL,
  async = TRUE,
  interval_seconds = 3,
  max_attempts = 200,
  wait_for_results = TRUE,
  guess_types = TRUE,
  bind_using_character_cols = deprecated(),
  fact_map_key = "T!T",
  verbose = FALSE
)

Arguments

report_id

character; the Salesforce Id assigned to a created analytics report. It will start with "00O".

report_filters

list; A list of reportFilter specifications. Each must be a list with 3 elements: 1) column, 2) operator, and 3) value. You can find out how certain field types can be filtered by reviewing the results of sf_list_report_filter_operators.

report_boolean_logic

character; a string of boolean logic to parse custom field filters if more than one is specified. For example, if three filters are specified, then they can be combined using the logic "(1 OR 2) AND 3".

sort_by

character; the name of the column(s) used to sort the results.

decreasing

logical; a indicator of whether the results should be ordered by increasing or decreasing values in sort_by column when selecting the top N records. Note, this argument will be ignored if not specifying Top N. You can sort the records using arrange after the results are returned.

top_n

integer; an integer which sets a row limit filter to a report. The results will be ordered as they appear in the report unless specified differently via the sort_by and decreasing arguments. Note, it is sometimes helpful to specify the top_n argument if a report contains many rows, but you are only interested in a subset of them. Alternatively, you can limit the count of returned rows via the report_filters argument.

async

logical; an indicator, by default set to TRUE, which executes the report asynchronously. If executed asynchronously, this function will return a list of attributes of the created report instance. The results can be pulled down by providing the report id and instance id to the function sf_get_report_instance_results. Refer to the details of the documentation on why executing a report asynchronously is preferred.

interval_seconds

integer; defines the seconds between attempts to check for job completion.

max_attempts

integer; defines then max number attempts to check for job completion before stopping.

wait_for_results

logical; indicating whether to wait for the report finish running so that data can be obtained. Otherwise, return the report instance details which can be used to retrieve the results when the async report has finished.

guess_types

logical; indicating whether or not to use col_guess() to try and cast the data returned in the recordset. If TRUE then col_guess() is used along with anytime() and anydate(). If FALSE then all fields will be returned as character. Specifying FALSE helpful when guessing the column data type will result in NA values and you would like to return the results as strings and then cast in your script according to your unique specifications.

bind_using_character_cols

logical; an indicator of whether to cast the data to all character columns to ensure that bind_rows does not fail because two paginated recordsets have differing datatypes for the same column. Set this to TRUE rarely, typically only when having this set to FALSE returns an error or you want all columns in the data to be character.

fact_map_key

character; string providing an index into each section of a fact map, from which you can access summary and detailed data. The pattern for the fact map keys varies by report format so it is important to know what the reportFormat property of the target report is. See the note below for more details.

verbose

logical; an indicator of whether to print additional detail for each API call, which is useful for debugging. More specifically, when set to TRUE the URL, header, and body will be printed for each request, along with additional diagnostic information where available.

Value

tbl_df

Details

This function is essentially a wrapper around sf_execute_report. Please review or use that function and/or sf_query_report if you want to have more control over how the report is run and what format should be returned. In this case we've forced the reportFormat="TABULAR" without total rows and given options to filter, and select the Top N as function arguments rather than forcing the user to create an entire list of reportMetadata.

Note

Below are the fact map key patterns for three report types:

TABULAR

T!T: The grand total of a report. Both record data values and the grand total are represented by this key.

SUMMARY

<First level row grouping_second level row grouping_third level row grouping>!T: T refers to the row grand total.

MATRIX

<First level row grouping_second level row grouping>!<First level column grouping_second level column grouping>.

Each item in a row or column grouping is numbered starting with 0. Here are some examples of fact map keys:

0!T

The first item in the first-level grouping.

1!T

The second item in the first-level grouping.

0_0!T

The first item in the first-level grouping and the first item in the second-level grouping.

0_1!T

The first item in the first-level grouping and the second item in the second-level grouping.

Salesforce Documentation

• Sync Documentation

• Sync Example

• Async Documentation

• Async Example

• Filtering Results

See also

Other Report functions: sf_copy_report(), sf_create_report(), sf_delete_report(), sf_describe_report(), sf_describe_report_type(), sf_execute_report(), sf_list_report_fields(), sf_list_report_filter_operators(), sf_list_report_types(), sf_list_reports(), sf_query_report(), sf_update_report()

Examples

if (FALSE) { # \dontrun{
# find a report in your org and run it
all_reports <- sf_query("SELECT Id, Name FROM Report")
this_report_id <- all_reports$Id[1]
results <- sf_run_report(this_report_id)

# apply your own filters to that same report
# set up some filters, if needed
# 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' so 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))

# 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)
} # }