---
title: "Working with Reports"
author: "Steven M. Mortimer"
date: "2020-07-19"
output:
  rmarkdown::html_vignette:
    toc: true
    toc_depth: 4
    keep_md: true
vignette: >
  %\VignetteIndexEntry{Working with Reports}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, echo = FALSE}
NOT_CRAN <- identical(tolower(Sys.getenv("NOT_CRAN")), "true")
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  purl = NOT_CRAN,
  eval = NOT_CRAN
)
options(tibble.print_min = 5L, tibble.print_max = 5L)
```

## 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](https://github.com/StevenMMortimer/salesforcer/issues/new?assignees=StevenMMortimer&labels=&template=default-issue-template.md&title=) 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}.

 - <a rel="noopener noreferrer" target="_blank" href="https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_get_reportdata.htm">Running a Report Synchronously or Asynchronously</a>  
 - <a rel="noopener noreferrer" target="_blank" href="https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_factmap_example.htm">Understanding a Report Fact Map</a>   
 - <a rel="noopener noreferrer" target="_blank" href="https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_filter_reportdata.htm#example_requestbody_execute_resource">Filter Reports on Demand</a>  
 - <a rel="noopener noreferrer" target="_blank" href="https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_intro.htm">Reports and Dashboards REST API Documentation Home Page</a>  

----  

## Authenticate

First, load the {salesforcer} package and login either by OAuth 2.0 (SSO) or 
using your username, password, and security key.

```{r auth, include = FALSE}
suppressWarnings(suppressMessages(library(dplyr)))
suppressWarnings(suppressMessages(library(here)))
library(salesforcer)
token_path <- Sys.getenv("SALESFORCER_TOKEN_PATH")
sf_auth(token = paste0(token_path, "salesforcer_token.rds"))
```

```{r load-package, eval=FALSE}
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.

![](./working-with-reports_files/report-screenshot.png)

The report Id above (`"00O3s000006tE7zEAE"`) is the only information needed to pull 
those same results from an R session, like so: 

```{r simple-report}
my_report_id <- "00O3s000006tE7zEAE"
results <- sf_run_report(my_report_id)
results
```

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 <a
target="_blank"
href="https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_get_reportdata.htm">https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_get_reportdata.htm</a>. 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.

```{r filtered-report1}
# 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
```

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".

```{r filtered-report2}
# 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
```

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.

```{r describe-report}
report_details <- sf_describe_report(my_report_id)
report_details$reportMetadata$reportType$type
report_details$reportMetadata$reportFilters
```

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.

```{r report-type-metadata}
report_details$reportTypeMetadata$standardDateFilterDurationGroups[[6]]$standardDateFilterDurations[[1]]
```

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.

```{r report-filter-operators}
# report fields
report_fields <- sf_list_report_fields(my_report_id)
head(names(report_fields$equivalentFieldIndices))

report_filters <- sf_list_report_filter_operators()
unique_supported_fields <- report_filters %>% 
  distinct(supported_field_type) %>% 
  unlist()
unique_supported_fields

# operators to filter a picklist field
picklist_field_operators <- report_filters %>% 
  filter(supported_field_type == "picklist")
picklist_field_operators
```

## 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.

```{r admin-example}
# 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)

# 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

# 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

# fourth, delete that report using its Id
success <- sf_delete_report(new_report$reportMetadata$id)
success
```

## Troubleshooting

If you are having an issue with a report please submit in the {salesforcer} GitHub 
repository at: <a rel="noopener noreferrer" target="_blank" href="https://github.com/StevenMMortimer/salesforcer/issues">https://github.com/StevenMMortimer/salesforcer/issues</a>. As a maintainer, reports can be are tough to debug because every Salesforce 
Org is unique. When filing your issue please make an attempt to understand the 
query and debug a little bit on your own. Here are a few suggestions:  

  1. Slightly modify your function call to `sf_run_report()` to observe the results. Here 
  are a few prompting questions that may assist you:  
  
      - What do you see when you set `verbose=TRUE` argument?
      
      - What happens if you run sync. vs. async. (e.g. async=TRUE vs. FALSE)?
      
      - What happens if you try running a different type of report?
  
  2. Double check Salesforce's <a rel="noopener noreferrer" target="_blank" href="https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_intro.htm">Reports and Dashboards REST API Developer Guide</a> to see whether if your report type would be supported or limited in some way.
  
  3. Review report unit tests at: <a rel="noopener noreferrer" target="_blank" href="https://github.com/StevenMMortimer/salesforcer/blob/main/tests/testthat/test-report.R">https://github.com/StevenMMortimer/salesforcer/blob/main/tests/testthat/test-report.R</a>. 
  These unit tests were written to cover a variety of use cases and to track any 
  changes made between newly released versions of the Salesforce API (typically 
  4 each year). These tests are an excellent source of examples that may be 
  helpful in troubleshooting your own case.
  
  4. Roll up your sleeves and dive into the source code for the {salesforcer} 
  package. The main scripts to review are:  
  
      - <a rel="noopener noreferrer" target="_blank" href="https://github.com/StevenMMortimer/salesforcer/blob/main/R/analytics-report.R">https://github.com/StevenMMortimer/salesforcer/blob/main/R/analytics-report.R</a>
      
      - <a rel="noopener noreferrer" target="_blank" href="https://github.com/StevenMMortimer/salesforcer/blob/main/R/utils-report.R">https://github.com/StevenMMortimer/salesforcer/blob/main/R/utils-report.R</a>
