Retrieves data from SSRS URLs. — read

SQL Server Reporting Services (SSRS) built by PHSA enables an alternative way for users to retrieve public health data containing identifiers.

Usage

read_ssrs(
  url = "",
  ...,
  username = NULL,
  format = c("CSV")[1],
  .explore = list(F, "verbose")[[1]],
  .skip = 0,
  .in_memory = T,
  .return_url = F
)

Arguments

url

SSRS url.

...

SSRS reports' built-in filters. See Details.

username

User ID (without email domain). Not required if you are on PHSA network. However, providing one allows one to set up batch jobs for scheduled runs. See Details.

format

Some SSRS reports offer multiple formats to download. Currently only csv is supported and is the default value. May be extended in the future.

.explore

Defaults to FALSE. If you are unsure what filters you could use for your SSRS report, set this to TRUE and a list of currently supported filters and their default values (if available; no values will be shown if dependent on other values, ie. query-based) will be printed in the console.

Additionally, set to verbose will instead of printing to console, return a data.frame of detailed parameter information, which can be used to assist users to design their scripts.

.skip

SSRS reports in csv format may contain lines above the headers (meta info, descriptions, etc). You may not wish to have this in your data frame. If in your first run you noted there are lines above the headers, you can enter number of lines to skip here.

.in_memory

If the body of the response is too large for your environment, you will run into curl::curl_fetch_memory() error. In this case, set this parameter to FALSE, and a tempfile will be created for you to temporarily store the response body while being parsed into a csv. Alternatively, provide a full path with file name to explicitly direct the tempfile to.

.return_url

For developer troubleshooting.

Value

A tibble object.

Details

This function provides an interface to pull data into R environment by leveraging the following packages/tools:

keyring: handles user credential elegantly.
httr2: handles HTTP requests and responses following Microsoft documentation on REST APIs for Reporting Services (https://learn.microsoft.com/en-us/sql/reporting-services/developer/rest-api?view=sql-server-ver16).

There are some helper parameters to assist users with the report's built-in filters and output formats. However, how the report is set up may be very different from one to another. Please always double check to ensure what you get is what you intended.

The helper to determine filters is .explore. If you set it to TRUE, you may get something similar to the following message printed in your console:

  Default User Input:

    health_authority: No input detected; possibly checkbox?
    death_date_from : 1/1/2015
    death_date_to   : 5/20/2025

To use filters in this function, simply refer to what's printed above, and add them as part of the function:

read_ssrs(
  url             = YOUR_SSRS_URL,
  death_date_from = '1/1/2015',
  death_date_to   = '5/20/2025'
)

Your user credential, if provided, is managed by keyring package. This prevents you from entering your credentials in the console or saving it in the script, which are both not ideal practices for security.

keyring will leverage your operating system's credential manager to handle your saved credential.

Author

Brendan Bakos contributed the implementation of the crucial authentication/negotiation, and is instrumental in the design of the API handling.