Leave a comment at the end of this page or email contact@krishagni.com
Aggregate (Counts) Queries
OpenSpecimen allows you to specify aggregate functions like count, sum, average, etc. on specific fields and also apply filters on aggregate filters.
Why can non-super admins not perform aggregate queries containing PHI fields?
Due to challenges in masking patient sensitive data, Non-Super Admins cannot run aggregate queries containing PHI fields.
Creating aggregate queries
Count of registered participants, visits, and primary specimens by collection protocols
- Create required filters:
- 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.
How to remove aggregate functions?
Create query 'Actions→Columns → Next to go to 'Aggregates', unchecked the aggregates functions that is selected.
- Click on 'Done' to see the aggregate results:
Based on the field selected, aggregate functions available differ. For numeric fields functions like sum, avg etc. are available but for text fields only count is available.
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
Leave a comment at the end of this page or email contact@krishagni.com