Luke Stevens, Murdoch Children's Research Institute https://www.mcri.edu.au
https://github.com/lsgs/redcap-extended-reports
Provides some extensions & additional functionality for REDCap's built-in Data Exports & Reports functionality.
- Row per record output for reports with longitudinal data or data from repeating instruments.
- Custom SQL report view and CSV export (including via API). Administrator-only set up.
- Adds live filter options for "All Data" report (A) on My Reports & Exports (report list) page
- Action tag
@REPORT-LABEL='...'for specifying alternative field label for reports and exports
The module enables REDCap's standard row-per-record-per-event and row-per-record-per-instance results to be collapsed to row-per-record.
Options for reshaping event rows to columns are:
- Reshape by event then field (default): by event, each field e.g.
event_1_arm_1.var1, event_1_arm_1.var2, event_2_arm_1.var1, event_2_arm_1.var2 - Reshape by field then event: by field, each event e.g.
event_1_arm_1.var1, event_2_arm_1.var1, event_1_arm_1.var2, event_2_arm_1.var2
Options for handling repeating data - repeating events or repeating instruments - are:
- Column per instance (default): the maximum number of instances will be determined and data from each instance will be returned in its own set of columns.
- First instance: one column per field containing only the value from the lowest numbered instance from each record.
- Last instance: one column per field containing only the value from the highest numbered instance from each record.
- Lowest value: one column per field containing only the lowest value across all instances from each record. (Non-functional for checkbox fields.)
- Highest value: one column per field containing only the highest value across all instances from each record. (Non-functional for checkbox fields.)
- Concatenate (space/comma/pipe): one column per field containing all values from each record concatenated into a single string value using the selected character.
With the "Column per instance" option, field names will incorporate event and instance number information in a dot- or space-separated form as follows:
- Raw CSV, repeating event: event_unique_name.instance.fieldname
- Raw CSV, repeating instrument: event_unique_name.fieldname.instance
- Labels CSV, repeating event: EventLabel[ ArmLabel] #Instance.FieldLabel
- Labels CSV, repeating instrument: EventLabel[ ArmLabel] FieldLabel #Instance (Arm label included only if project has multiple arms)
The "Concatenate (space/comma/pipe)" causes a report to be handled as if "Combine checkbox options into single column" is selected irrespective of its actual setting. (There is no obvious way to show the data otherwise.)
Live filters are not supported (mainly because they're not supported by the REDCap::getReport() developer method).
Row-per-record reshaping is not applied to stats package (SAS, SPSS, R, Stata) or ODM exports.
The functionality of this external module differs in some respects from the old (pre-external module framework) "[https://github.com/lsgs/redcap-longitudinal-reports/](Longitudinal Reports)" plugin. For example, this module does not allow you full control of the order of fields, or include schedule dates or survey links as selectable options (you can create fields for these using smart variables if you need them in reports), whereas the old plugin does not integrate fully with REDCap's regular reports for all types of export, including API, nor offer customised reporting with SQL reports. The external module renders the plugin obsolete.
Selecting the "Custom SQL Query" option after entering a report's title and description causes the field/event/filter/sort sections of the report configuration page to be hidden and replaced with a text area into which administrators may enter a custom SQL query.
Notes:
- Report View/Edit sections remain available and control report access in the usual way. "Edit" permission enables the ability to edit the report title, description and access permissions, not the SQL.
- Smart variables such as
[user-name],[user-dag-name],[user-role-name],[project-id]etc. can be utilised within the SQL (include quotes for string values where appropriate). - You can mimic separate field name/field label behaviour by having your SQL return a column name containing a
$character. For example:select record as record_id$Record, ...gives a field name ofrecord_idand field label "Record".select record as 'person_id$Person ID', ...gives a field name ofperson_idand field label "Person ID" (single-quotes are required where your label contains spaces).
- If your project contains DAGs, and an SQL query returns a column named with the project's record id, then default behaviour is to apply DAG filtering of results, i.e. users will see only records from their own DAG. This can be overridden either by returning the column with a different name, or by selecting the checkbox.
- It is not possible to convert a non-SQL report into an SQL report. Just create a new one.
SQL reports are exported only in CSV format via the Data Exports Reports & Stats page, but are additionally available in JSON and XML form via the API.
SQL reports can be created and have their SQL edited only by REDCap administrators. Report title, description and visibility are editable by regular users in the usual way.
- Reshaping is not applied to stats package exports (R, Stata, SAS and SPSS all have built-in commands for the reshaping operation). Stats package exports are performed with no modification.
- SQL reports exports are limited to CSV raw/label formats only, not with stats package syntax files, nor as ODM XML.
- The custom report processing is not able to be injected when a report is run for the purpose of a smart variable parameter, e.g.
[aggregate-count:record_id:R-123ABCDEFG]. Consequently rows returned will be in a report's unreshaped form, and will be empty for SQL reports.