Creating a Simple OpenSpecimen Query

OpenSpecimen reporting functions help users to search within or across collection protocols and various criteria.

Overview

• Select a Collection Protocol for the query.

• You can only query for your sites.

• If you do not have access to a CP, the data (specimen/ visits/ participants) related to that CP, will not be visible to you - even in the query results.

• You can add a filter to create queries based on calculations (Temporal Queries).

  • Examples:

    1. Difference between collection time and date of birth (age at collection)

    2. Difference between collection time and received time (warm ischemia time)

    3. Difference between frozen time and received time

    4. All specimens collected "today", "last week" or "last month"

• “Operators' are use to create the logic for your query. Operators include the following:
  
  

  Open

  

   

Creating a Query

Example query: Search for female participants in the Skin Cancer Study who have DNA specimens collected which are available.

1. Navigate to Queries on the left panel and click Create

2. Select the collection protocol
   
   

   Open

   

3. Click on the Participant → Select Gender. Add a filter with Operator 'Equals' and select 'Female' under the Condition Value
   
   

    

4. Click on Specimens → Select Type.  Add a filter with Operator 'Equals' and select DNA under the Condition Value.

5. Click on Specimens → Select Availability. Add a filter with Operator 'Equals' and select Available under the Condition Value.

6. Click on ‘Get Count’ to get the number of counts of participants, visits, and specimens for the query created.

7. Click on View Records to view the data pulled for the query.
   
   

    

8. Save your query to a folder
   A query can be saved for future reuse OR shared with other users.

   1. Click on the 'Actions' button

   2. Select option 'Save'.

   3. Enter the query title and click on the 'Save' button. If you want to copy an existing query, click on the 'Save a copy'.
      
      

9. Create New Folder if desired

   1. Click on Assign → Create New Folder as illustrated in the below image.
      
      

      
      
      

   2. Specify folder name and select users or user groups from the dropdown with whom the folder (therefore queries) needs to be shared.

       

   3. Click the Create button. On successful folder creation, the folder can be visible under the 'My folder' section.

      1. Users with whom this folder has been shared will have access to the queries

      2. In future, if you wish to add a new query to this folder, you will be able to see the folder once you click on 'Assign tab'. Only those folders created by the user will be visible in the modal box

Configure Columns in your Query

The fields or the order of the fields to be displayed on the query results page can be configured

1. From the results page, click on the 'Actions' button. 

2. Select 'Columns'

3. Select the Columns needed in the results.

4. Reorder the columns by using drag and drop (Note: Drag the main tree node. Individual columns cannot be dragged)

   1. Click on +/- beside the object (Participant/visits/Specimen/CP) to expand/collapse the fields of object.
      
      

       

   2. Click Next
      
      

       

5. Configure the header names for the columns

   1. In the ‘Field Labels' section in the right column, input your desired labels to be displayed and click 'Done’

       

Parameterizing Queries

If you need to create queries where some of the fields can be entered by the users realtime when running the query, you can “parameterize” the query filters. Using the parameterized filters, you can create a single query, save it and use the filters in realtime to filter for data. 

Steps to create a parameterized filter:

1. Select filter from any form. E.g. 'Clinical Diagnosis' from the visit form.

2. Add 'Operator' as 'All' or use "is one of" if you want to select specific fields.

3. Select the checkbox on the filter beside 'Parameterized Filter' and click on 'Add'

4. Once done, the fields that are added as parameterized will be displayed on the results page. 

5. User can query for various data on executing the saved query.

   1. E.g. 'Clinical Diagnosis' filter, user can search for any diagnosis value and the results grid will be filtered based on the selection.

Example:

Return all samples for a particular "SPR number" (here SPR number is to be parameterized)

1. Create a filter for 'Path. Number' with operator 'All' and add this as a parameterized filter.
   
   

2. Add another filter for clinical diagnosis with "is one of" for 5 fields as in the screenshot below
   
   

    

3. The filters that are parameterized are now available in the left side. Users can now select one or more values and results on the right grid is displayed accordingly.
   
   

    

4. On expanding the filter, users can type in the path number they are looking for at that particular time.
   
   

5. Same filtering can be use for the clinical diagnosis field: