The following document, copied below, is a quick guide to using RStudio as the tool to querying the APPROACH Online Reporting platform
...
a. Install the Query Utility R Package for APPROACH. This package is not public, and only available on the RStudio server. To install, run the following command in the console
install.packages("/home/cru/approach_0.1.1.tar.gz", source = TRUE, repos = NULL)
If successful, the console should return the following
Installing package into ‘/home/<your-username>/R/x86_64-pc-linux-gnu-library/3.6’
(as ‘lib’ is unspecified)
*installing *source* package ‘approach’ ...
** using staged installation
** R
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded from temporary location
** testing if installed package can be loaded from final location
** testing if installed package keeps a record of temporary installation path
*DONE (approach)
b. Import the installed APPROACH library, running the following command in console
library(approach)
c. Create an ApproachAPI instance/object as follows
api = ApproachAPI()
This api variable will be used to authenticate yourself, and send PostgreSQL queries to the database
...
a. Logging In
Before submitting a query, the API must authenticate all subsequent requests. Authenticate yourself first using the ApproachAPI $login() function as below
api$login()
You will be prompted for your APPROACH reporting platform credentials.
...
b. Submitting Queries
Warning - Some tables have several 100,000 + records stored. Try to refrain from selecting all from large data sets.
Ex. Running
SELECT * FROM approach.patient;
would return 200,000+ records. This would be too much data for the database, RSTudio, as well as your computer to be processing and rendering frequently.If you are unsure if a query will return too many rows to process, you can limit the number of rows returned using the LIMIT clause at the end of any query.
i.e.,
SELECT * FROM approach.patient LIMIT 1000
Build your PostgreSQL query and submit it using the ApproachAPI $query(string) function
The model explorer tool available in the reporting platform can be used to determine tables, field names and relationships used to build your queries.
See here: https://test-front.approach.org/model-explorer (must be logged in)
e.g.,
api$query("SELECT count(*) as c, bmi FROM approach.patient WHERE bmi IS NOT NULL and bmi > 0 GROUP BY bmi ORDER BY c DESC;")
The query function will render the query’s results into a table
...
c. Logging Out
To close off your session with the API, run the $logout function as below
api$logout()
A confirmation message will be displayed once logged out
...