Forum Discussion

AhmedGul's avatar
AhmedGul
Qrew Member
11 months ago

Create a Dashboard from Multiple Reports with Similar Fields

Hello,

For context: I have 3 separate tables to manage research study data. Let's call them Study 1, Study 2, and Study 3. Each study has follow-up visits at different designated Timepoints. Some Timepoints has a ± 7-day window, and other Timepoints have a ± 14-day window for when the visit can take place. For example:

Study 1, Patient Timepoints

Patient IDStart Date1-Month Window3-Month Window
00101/01/202401/24 - 02/0703/25 - 04/08
00202/01/202402/23 - 03/0704/24 - 05/08
00303/01/202403/24 - 04/0705/25 - 06/08
 
I have fields in each table to calculate each of the Timepoint Start and End Dates based on the patient's start date (1M Start Date, 1M End Date; 3M Start Date, 3M End Date; etc.). I then combined each timepoint's start date + end date into one field to minimize the number of fields in my report (1M Window).

Lastly, I added a formula-text field that returns the Current Timepoint each patient is in, example: 1-Month Visit, 3-Month Visit (when the patient is within a timepoint); or 1-Month Upcoming, 3-Month Upcoming (when the patient is in between timepoints). This allowed me to create a custom report within each Trial/Table filtered for just the patients who are active within a timepoint.

 

I am trying to figure out how to consolidate the custom reports into one dashboard. The problem is, although each trial has identical field names, the fields are calculated differently due to different timepoints and different windows. Any ideas of how I can create a dashboard/report to show all active patients and their windows across all trials?

Dashboards
  • BRIANHINSHAW's avatar
    BRIANHINSHAW
    Qrew Member
    11 months ago

    Create a "summary" table to collect the data for each patient (assuming patient ID is a key). The columns would look like Study 1 Current Timepoint, Study 1 1-Month Visit, etc. Then build a pipeline that triggers on edit to perform a copy records from Study 1 table to the fields in the summary table. Build your dashboard report off of the summary table to show the columns desired. 

    You might could do the same with a relationship from each Study table to the Summary table, but have to get the patient ID (or other key) in the summary table for the lookups to collect the data.

    Depending on how many patients you have, you may want to use a button on your dashboard to take them to the report. Ask some filtering questions first and then display the results.  Each time you have a report on a dashboard home tab it will pull all that data before it can load and display.