One of CommCare's many built-in integration options is the Odata feed integration with Power BI and Tableau, which allows programmatic teams to automatically feed their CommCare data into either BI tool with a few easy steps.
As part of the WHO FFX template package, we created a template Power BI Dashboard to be downloaded and used by any organizations deploying the WHO FFX app. For more information on Microsoft's Power BI product, check out their website.
If you don't already have it, you can download Power BI Desktop for free from the link.
Report and indicator descriptions
This dashboard is a sample of what kind of information you can extract from your FFX template app. See the indicator and report descriptions below, and get inspired to grow and customize your dashboard to best meet your teams needs.
|Dashboard item||Description||Indicator calculation|
Map of COVID-19 case GPS locations.
The patient_location case property stores the latitude, longitude, elevation, and precision of the case's GPS location (space-separated). Parse this into 4 columns and load the latitude and longitude columns into your Power BI map.
Total number of confirmed COVID-19 cases.
Count of of covid_19_case cases with patient_case_status = "confirmed"
Total number of suspected COVID-19 cases.
Count of covid_19_case cases with patient_case_status = "suspected"
Total number of confirmed COVID-19 cases who have recovered.
Count of covid_19_case cases with outcome_status = "recovered"
Total number of deaths among confirmed COVID-19 cases.
Count of of covid_19_case cases with patient_case_status = "confirmed" and current_status OR outcome_status = "dead"
Total number of suspected and confirmed COVID-19 cases over time (cumulative).
Cumulative total of covid_19_case cases by date_of_first_symptom_onset, grouped by patient_case_status (confirmed/suspected).
Daily number of suspected and confirmed COVID-19 cases over time.
Individual count of covid_19_case cases by date_of_first_symptom_onset, grouped by patient_case_status (confirmed/suspected).
Total number of confirmed COVID-19 cases who were hospitalized.
Count of covid_19_case cases with patient_case_status = "confirmed" and patient_was_ever_hospitalized = "yes"
Percentage of cases who were ever hospitalized.
Values of the patient_was_ever_hospitalized property among covid_19_case cases with patient_case_status = "confirmed"
Of confirmed cases, how many were followed up with (indicated by Form A2 being completed).
Count of of covid_19_case cases with form_completed_a2 = "yes" / count of of covid_19_case cases with form_completion_date_a1 = "yes"
Of contacts registered, how many were followed up with (indicated by Form B2 being completed).
Count of of covid_19_case cases with form_completed_b2 = "yes" / count of of covid_19_case cases with form_completion_date_b1 = "yes"
Setting up your dashboard
If you are on a Advanced CommCare subscription or higher, you can download this dashboard and set it up to automatically sync the FFX app data from your project space via the Odata feed integration. If you're on a Community plan, you can still use this dashboard and use a manually downloaded CommCare data export as your data source.
Find the instructions for your configuration steps below. Remember to reference the case type and properties documentation on the COVID-19 Template App: WHO FFX Protocol page as you dig into your data.
To create a manual dashboard
- Go to Data > View All
- Click "Export Case Data"
- Create a new case export for both the covid_19_case and contact case types
- Select a Date Range for the data you want to export
- Click "Prepare Export"
- Click "Download"
- In Tableau Desktop, go to Get Data > Excel to pull in your excel case exports
- Because your reports are all pointing to fields in the queries that came with the template download, you'll need to recreate the calculated fields in your new queries from the excel sources and update your dashboard to reference those new fields
- Once this is all working, remove the original queries that came with the template
To create a dashboard integration
Please note that this requires a paid software plan that is an "Advanced Plan" or higher for CommCare (see pricing here). If you are not currently on a paid plan, please apply for a pro bono subscription here.
In your CommCare project space where you've imported your template WHO FFX app, go to Data > PowerBi/Tableau Integration and add an Odata Feed per case type and form you want to feed into your dashboard. This template was built using the covid_19_case and contact case types.
Download the .pbix file above and open it in the Power BI Desktop software
In Power BI Desktop, go to the Data tab 'Fields' column in the right corner where you'll see the contact and covid_19_case queries. Click on the three dots > Edit query to open the Query Editor where you can edit the Source Odata feed URL. Do this for each to load your data into the template dashboard.
If you want to add new Odata feeds to create new queries, go to Get Data > OData feed > insert URL
Login via the basic authentication, using the API key as indicated on this page.
To help get you started, check out this demo video on creating the Odata feed from CommCare HQ to connect to your dashboard.