OS Community Customized Reports

OCTRI IRB Compliance

Displays information about protocols and regulatory information to projects that are lapsing do not receive services while out of compliance.

OHSU

?

Background filters (not available to change) – report only pulls in projects that have an IRB record on it and only shows those that include an IRB review category not equal to “exempt” or “terminated or closed”

In the user interface users can specify a date range for the expiration date of the IRB. This is used to “look out” prospectively for 1 month for upcoming expirations.

Excel Document

SPARC only

Anyone with Reporting rights in SPARC

OCTRI Navigators – Administrators of the system and catalog managers of the system.

OCTRI COVID-19

A list of protocols and services that are on projects associated with COVID-19.

Used to triage and allocate CTSA resources during the pandemic surge.

OHSU

?

Background filters apply to pull only projects with Impact Area = “Other” and in the text box “COVID-19” has been entered.

Excel Document

SPARC Only

Anyone with Reporting rights in SPARC

OCTRI Navigators – Administrators of the system and catalog managers of the system. Requests from leadership to identify high impact or interesting projects during COVID-19.

Expired IRBs Report (Note name is a misnomer)

Shows projects that have requests on them that have IRB records that closed within a given timeframe. Used to identify projects that have IRB contingent services and need to be closed down.

OHSU

?

Background filters include – displays only request statuses not equal to Completed, Withdrawn, Declined – AND -  Regulatory status = complete or terminated.

Excel Document

SPARC Only

Anyone with Reporting rights in SPARC

OCTRI Navigators – Administrators of the system and catalog managers of the system.

Request Review

Primarily set up to monitor requests as they move through the initiation process. Used The report is run with different request statuses depending on what the person is trying to monitor – looking for those in Pending for a particular program or those in Administrative Review or those in Awaiting Requestor response.

OHSU

unknown; probably a customization of the Service Requests report. Added in fields for Review Due Date and Initiation Meeting which we use to ensure things happen in a timely fashion. Removed all the information about the PI institution, college etc.

standard SPARC filters available in the system. Filter by institution, program, provider, core and status, non-clinical service start dates.

Excel Document

SPARC Only

Anyone with Reporting rights in SPARC

OCTRI Navigators – Administrators of the system and catalog managers of the system.

OCTRI Protocol Attributes

Displays “protocol level” information including the impact areas, NCT#, “Research Involving” information, special flags we’ve created and Affiliations with certain institutes at our organization. Used mostly to link up with other reports so we can paint a fuller picture of what our portfolio includes. Often used for APR, EAC, IAB meetings etc.

OHSU

?

Excel document

SPARC Only

Anyone with Reporting rights in SPARC

OCTRI Navigators – Administrators of the system and catalog managers of the system.

OCTRI Services Review

Used for many purposes by many people. Often called the ‘kitchen sink’ report. This allows us to get to the service level to evaluate what is happening at a more granular level. We attribute activity to the start and stop dates of services, this report enables us to see which services are active at a given time and through that which protocols are active. We use this to determine which protocols need to be reported for a grant year. We also use this to determine which projects need to be followed up for funding status, we also use it to see which services are being used most in our portfolio, and to “clean data”. We look for logic such as – if Approved, is there a start date, if Complete is there an end date etc. Providers use it to get a detailed view of what is happening with each project. Used to create visualization of service counts under certain categories for EAC, IAB meetings.

OHSU

?

standard SPARC filters; request status, institution, provider, program, core, non-clinical start dates.

Excel Document

SPARC Only

Anyone with Reporting rights in SPARC

OCTRI Navigators – Administrators of the system and catalog managers of the system.

All OCTRI Services

Runs the same fields as the “OCTRI Services Review” – but does not allow for filters. We often run the “OCTRI Services Review” without selecting any filters as we need to use logic around start and end dates for services to define which projects are active. The native date filters in SPARCReport don’t allow us to filter on end dates, nor does it allow us to use logic joiners like “Or” and “And” or “Is Blank”. Instead we manually filter a huge excel file that dumps all of our projects and services. Because it takes so long to run our developers created a different version of the report that strips out the possibility of filtering in the UI to enhance the performance of the run time.

OHSU

new? I think.

No filters – just a dump of any project in the system with a request and service.

Excel Document

SPARC Only

Anyone with Reporting rights in SPARC

OCTRI Navigators – Administrators of the system and catalog managers of the system.

OCTRI Billing

Used by program managers to create billing – matched up with other data from other sources.

OHSU

?

standard SPARC filters, program, provider, core, statuses, non-clinical start dates.

Note that the “cost” field here is not always helpful, as it displays the base cost from the catalog, not the “your cost” that the project should be charged. We haven’t worked out how to make the report reflect the “your cost” out of SPARC.

Excel Document

SPARC Only

Anyone with Reporting rights in SPARC

Program managers and billing administrators

Service Provider Services Report

a detailed report for their specific provider/core services

This report is created monthly, quarterly, and yearly (based on grant year) and summarize data across that time period.  Additionally, the monthly reports show numbers going back 6 months and the quarterly reports go back 4 quarters.

Case Western

Our reports are new reports.  BASH scripts are used to facilitate the overall reporting workflow.  We use R (inc. RMySQL, rmarkdown, knitr, kable) and LaTeX (inc. tikz, multicol, fancyhdr) to generate PDFs.  Other tasks include copying the production database to our "sparc-reports" server, using rsync to copy the final reports back to the production server, using rclone to upload the reports to Google Drive, and using Python to send notification emails to report recipients.  Also, we run SQL scripts on the "sparc-reports" database to clean up data and, most importantly, create views to help make querying easier.

Our reports are static PDFs and interactive filtering is unavailable.

We provide multiple ways for users to access our reports:

Each person receives an email containing links to their PDFs and Excel files.

Finally, we send each service provider an Excel file.  In the file, there are worksheets containing "New Projects", "New Service Requests", "All Projects", and "All Service Requests". 

The reports are available on our main SPARC server.  Although technically accessible by anyone, the URL's full path is randomized and would be very difficult to guess.  For example:

https://sparc.case.edu/system/20230131/8DFB59F7-jrf16@case.edu/20230131_Summary_Monthly.pdf

Also, note that the user's email address is included in the URL as well.  We are using this as a simple method to track who is downloading the reports.  Multiple people may be sent the same report, but each person is sent a URL that is specific to them.  Prior to this, we would simply attach the files to an email, but we had no way of determining if people were actually looking at the reports.  Now we can simply look at the ssl_access_log to get an idea of who is downloading the reports.

Service Provider

Service Provider

Summary Activity Report

a report summarizing activity across all providers/cores

This report is created monthly, quarterly, and yearly (based on grant year) and summarize data across that time period.  Additionally, the monthly reports show numbers going back 6 months and the quarterly reports go back 4 quarters.

Case Western

Our reports are new reports.  BASH scripts are used to facilitate the overall reporting workflow.  We use R (inc. RMySQL, rmarkdown, knitr, kable) and LaTeX (inc. tikz, multicol, fancyhdr) to generate PDFs.  Other tasks include copying the production database to our "sparc-reports" server, using rsync to copy the final reports back to the production server, using rclone to upload the reports to Google Drive, and using Python to send notification emails to report recipients.  Also, we run SQL scripts on the "sparc-reports" database to clean up data and, most importantly, create views to help make querying easier.

Our reports are static PDFs and interactive filtering is unavailable.

We provide multiple ways for users to access our reports:

CTSC Administrators receive an email with a Google Drive link to all the reports

CTSC Administrators are sent a similar Excel file, except across all providers/cores.

The reports are available on our main SPARC server.  Although technically accessible by anyone, the URL's full path is randomized and would be very difficult to guess.  For example:

https://sparc.case.edu/system/20230131/8DFB59F7-jrf16@case.edu/20230131_Summary_Monthly.pdf

Also, note that the user's email address is included in the URL as well.  We are using this as a simple method to track who is downloading the reports.  Multiple people may be sent the same report, but each person is sent a URL that is specific to them.  Prior to this, we would simply attach the files to an email, but we had no way of determining if people were actually looking at the reports.  Now we can simply look at the ssl_access_log to get an idea of who is downloading the reports.

CTSC Administrator

CTSC Administrator

Executive Summary Report

An executive-level summary report

This report is created monthly, quarterly, and yearly (based on grant year) and summarize data across that time period.  Additionally, the monthly reports show numbers going back 6 months and the quarterly reports go back 4 quarters.

Case Western

Our reports are new reports.  BASH scripts are used to facilitate the overall reporting workflow.  We use R (inc. RMySQL, rmarkdown, knitr, kable) and LaTeX (inc. tikz, multicol, fancyhdr) to generate PDFs.  Other tasks include copying the production database to our "sparc-reports" server, using rsync to copy the final reports back to the production server, using rclone to upload the reports to Google Drive, and using Python to send notification emails to report recipients.  Also, we run SQL scripts on the "sparc-reports" database to clean up data and, most importantly, create views to help make querying easier.

Our reports are static PDFs and interactive filtering is unavailable.

We provide multiple ways for users to access our reports:

Certain key stakeholders can access an "index.html" page containing links to all reports from the past month

The reports are available on our main SPARC server.  Although technically accessible by anyone, the URL's full path is randomized and would be very difficult to guess.  For example:

https://sparc.case.edu/system/20230131/8DFB59F7-jrf16@case.edu/20230131_Summary_Monthly.pdf

Also, note that the user's email address is included in the URL as well.  We are using this as a simple method to track who is downloading the reports.  Multiple people may be sent the same report, but each person is sent a URL that is specific to them.  Prior to this, we would simply attach the files to an email, but we had no way of determining if people were actually looking at the reports.  Now we can simply look at the ssl_access_log to get an idea of who is downloading the reports.

Key CTSC and School of Medicine stakeholders

Key CTSC and School of Medicine stakeholders