Versions Compared

Version Old Version 25 New Version Current
Changes made by

Ashish Vishwakarma

Neha Nimgire

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Expand

  1. By default Super and Institute Admins have access to query

  2. Users with the role of Create, Update, Delete, Import, Export Query privileges can access the Query.

  1. PHI data of the participant is hidden/shown based on the role privilege.

Refer to the wiki page for more information on Roles and Privileges.

Creating Queries

  1. Navigate to Queries from the widget or navigation menu.

...

  1. Click on +Create

Fields description

Expand
titleClick 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,.

...

Expand
titleClick 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.

  1. Navigate to Queries -> +Create 

  2. Select the Collection Protocol Skin Cancer Study from the dropdown.

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

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

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

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

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

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

...