OpenSpecimen allows you to specify aggregate functions like count, sum, average, etc. on specific fields and also apply filters on aggregate filters. 

Non-Super Admin Limitation on Performing Aggregate Queries with PHI Fields

The restriction preventing non-super admins from executing aggregate queries that involve PHI fields is in place to uphold data security and privacy standards. PHI (Protected Health Information) is sensitive data that requires strict access controls to ensure compliance with regulations such as HIPAA.

By limiting non-super admins' ability to perform aggregate queries on PHI fields, organizations can mitigate the risk of unauthorized access to confidential health information. Super admins, who have elevated permissions, are entrusted with the responsibility of handling such sensitive data to prevent any breaches or misuse.

This restriction serves as a safeguard to protect the confidentiality and integrity of PHI, thereby maintaining a secure environment for healthcare data management.

Creating aggregate queries

<Add introduction for aggregate functions>

<List down all the aggregate functions available>

<For each function add an example with expand macro>

Count of registered participants, visits, and primary specimens by collection protocols

• Create required filters:

Open

• Click on 'View Records' and from results view, click on 'Actions→Columns'

• Select the columns needed in the report and click on Next to go to 'Aggregates' step. Select the appropriate function, like 'Count' in this case. The aggregate column can be renamed as needed.
  
  
  

Open

• Click on 'Done' to see the aggregate results:

For pivot table types of summary reports, refer to examples documented in 'Summary reports (Pivot table)'.

Applying conditions on aggregate values

Conditions can also be applied on aggregate functions. This can be done by going to 'Actions' → 'Columns' and going to last step 'Reporting Options'.

Find all participants of participant diagnosis who have more than 10 aliquots available 

Expression: count(Specimen.id)>10

Find all participants who have less than 10 ml of plasma aliquots stored

Expression: sum(Specimen.availableQty)<10

Find all participants who have more than one visit

Expression: count(SpecimenCollectionGroup.name)>1