| Table of Contents |
|---|
| minLevel | 1 |
|---|
| maxLevel | 6 |
|---|
| outline | false |
|---|
| type | list |
|---|
| printable | false |
|---|
|
Generally, users want to know the data collected for different/ particular collection protocols based on different criterias. E.g., Saliva Samples from Male participants, Participants that are turning 18, Samples from the participants that have consented, etc.
OpenSpecimen reporting modules help the users to search within or across collection protocols and various criteria to get the required results.
Training Video
| Widget Connector |
|---|
| overlay | youtube |
|---|
| _template | com/atlassian/confluence/extra/widgetconnector/templates/youtube.vm |
|---|
| width | 400px |
|---|
| url | https://www.youtube.com/watch?v=-vwGe9Af18A |
|---|
| height | 300px |
|---|
|
Who can access the Query?
| Expand |
|---|
By default Super and Institute Admins have access to query Users with the role of Create, Update, Delete, Import, Export Query privileges can access the Query.
 Image AddedPHI data of the participant is hidden/shown based on the role privilege.
 Image AddedRefer to the wiki page for more information on Roles and Privileges. |
Fields description
| Expand |
|---|
| title | Click here to view fields description |
|---|
|
Field | Description | Select a Collection Protocol | Helps to search through a Collection Protocol/Collection Protocol Group. If none is selected, search is done across all collection protocols. | Info |
|---|
For flexibility and reusability of queries, When a saved query is used in other areas like ‘Catalog' or 'Sub-query’, the CP/CPG selected in this option is not carried over. |
| Add Filter | Used to create queries based on calculations(Temporal Queries). Refer to the wiki page for more details. | Collection Protocol | Helps to search based on fields of Collection Protocol. E.g., Coordinator, sites, etc,. | Participant | Helps to search based on fields/forms of participants, e.g., gender, name, date of birth, race, etc., | Visits | Helps to search based on fields/form of visits, e.g., name, status, event code/label, etc,. | Specimens | Helps to search based on fields/forms of specimens, lineage, type, etc,. | Specimen Kits | Helps to search based on fields of kits, e.g., receiving/sending site, specimen label, etc,. |
|
Operators description
| Expand |
|---|
| title | Click to view operators decription |
|---|
|
Operator | Description |
|---|
Equals | Search based on particular name/value. | Not Equals | Search excluding the single value. | Exists | Searches the data where the field value is entered. | Not Exists | Searches the data where the field value isn’t entered. | All | | Starts with | Searches for the data where the value starts with the mentioned character/number/alphabet. | Ends with | Searches for the data where the value ends with the mentioned character/number/alphabet. | Contains | Searches for the data where the value exists with the mentioned character/number/alphabet. | Is One Of | Searches based on multiple values, where any value specified is true. | Is Not One Of | Searches excluding the multiple values specified. | Between | Searches the data between specified date/numeric range. | Less than | Searches the data that is less than specified date/numeric range. | Greater than | Searches the data that is greater than specified date/numeric range. | Greater than or equals | Searches the data that is greater than or equal to specified date/numeric range. | Less than or equals | Searches the data that is less than or equal to specified date/numeric range. |
|
Example
| Expand |
|---|
| title | Click here to view the example |
|---|
|
User wants to search for female participants in the Skin Cancer Study who have DNA specimens collected and are available. Navigate to Queries -> +Create Select the Collection Protocol Skin Cancer Study from the dropdown.
 Image AddedClick on the Participant → Select Gender. Add a filter with Operator 'Equals' and select 'Female' under the Condition Value.
 Image AddedClick on Specimens → Select Type. Add a filter with Operator 'Equals' and select DNA under the Condition Value.
 Image AddedClick on Specimens → Select Availability. Add a filter with Operator 'Equals' and select Available under the Condition Value.
 Image AddedClick on ‘Get Count’ to get the number of counts of participants, visits, and specimens for the query created.
 Image AddedClick on View Records to view the data pulled for the query.
 Image Added |
Features
Features | Description |
|---|
Create new queries | Create queries based on fields across standard, custom forms, and fields with complex expressions (AND, OR, NOT, intersection, hierarchical, and parenthesis). See: Sample queries |
Get counts (pivot tables) | Run the queries to get the count of participants and specimens that match the filters. |
Get raw data | Run queries to get raw data from the system. See: Query Results view |
Save query | Save query so that it can be reused for later use. |
Folders and sharing | Saved queries can be organized in folders and shared with other users. E.g., 2-3 users are working on shortlisting specimens for a project can share the same set of queries. See: Share queries |
Privileges | Data is filtered as per the user's privileges. E.g., if the user does not have PHI access, the patient identifier fields will be hidden. Access to the Query module can be controlled using the Query privilege. See: Query Privilege (v6.3) |
Data export (CSV) | Data from the query results view can be exported as a CSV file. |
Custom fields and forms | The custom forms and field added via form builder is available for querying the reports. |
Calculated fields | Difference between two dates (e.g., age at the collection, warm ischemia time, etc.) See: Calculated fields (Temporal Queries) |
Hierarchical queries | To support queries like, show any DNA aliquot whose parent specimen's "warm ischemia time" is less than x minutes. |
Import and Export queries | One can import and export queries from one instance to another—E.g., test server to production server. |
Aggregate functions | Supports functions like sum, max, min, etc. |
Dashboards | Create visually appealing dashboards with graphs and numeric counters based on query results. See: Dashboard changes using query |
Default Queries
Query | Description |
|---|
Cart specimens report query | Saved query to use for generating cart specimens report |
Container specimens report query | Saved query to use for generating container specimens report |
Distribution order data query | Saved query to use for creating distribution report when DP specific report is not configured |
Shipment report query | Saved query to use for exporting shipment details |
Specimen kit report query | Saved query to use for exporting specimen kit details |