Leave a comment at the end of this page or email contact@krishagni.com
Pivot tables- create count based summary reports
Introduction
Pivot tables are primarily used to create count-based reports and summarize data. They are also used to aggregate data based on specific categories you define for rows and columns.
e.g.,
Monthly specimen collection summary by protocol and type
Count of specimens by specimen type and disease
Count of participants by ethnicity and race
Note:
Non-Super Admins cannot run Pivot Tables containing PHI fields. Refer to this wiki for details
Example: Count of participants by race and ethnicity per protocol
Create a ‘New Query’.
Expand the ‘Participants’ section to add a filter as:
Participant >> Participant ID Exists
Click on ‘View Records’ and select the columns as required.
E.g., Collection Protocol→ Short title,
Participant → Participant ID, Race, Ethnicity.
Note: Since you need the count of participants, you will need to include a unique field for all participants, like 'Participant ID' or 'eMPI'.
Click on 'Next' --> Select the field based on which the count should be calculated, i.e., Participant ID.
Click on 'Next'--> Select the report type 'Pivot table' and set the required rows and columns.
Click 'Done' to view the report.
Save the query.