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 CommunityPulse package, we created a template Tableau Dashboard to be downloaded and used by any organizations deploying the CommunityPulse app. For more information on the Tableau product and pricing, check out their website.


Demo the live dashboard on Tableau Public

Download the template

You can access the live template and download the file directly from our Tableau Public page. You'll need the Tableau software to open the file on your computer. If you don't already have it, you can download Tableau Public Desktop for free from the link.

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.

Report and indicator descriptions

This dashboard is a sample of what kind of information you can extract from your CommunityPulse template app. See the indicator and report descriptions below, and get inspired to grow and customize your dashboard to best meet your team's needs.



Dashboard item

DescriptionIndicator Calculation

Total number of CMI registrations in each month.Count of cmi case by date_opened

Total number of screenings occurred in each month

Count of Form Screening submissions (formid) across the 3 forms:

  • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
  • CMI with phone > Daily Screening,
  • CMIs without phones > Daily Screening

Grouped by the completed_time

Total number of warnings triggered each month

Count of submissions (formid) with the property warning_status = yes, from the following forms:

  • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
  • CMI with phone > Daily Screening,
  • CMIs without phones > Daily Screening,

Grouped by the completed_time

% of CMI cases with warnings

Numerator:

    • Count of distinct case_id warning submissions with the property warning_status = yes, from the following forms:
      • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
      • CMI with phone > Daily Screening,
      • CMIs without phones > Daily Screening,

Denominator:

    • Count of the total number of cmi cases that are open

Total number of warning reviews each month

Count of submissions (formid) from the form:

  • Alerts and Warnings (Messaging config) > Warnings

Grouped by the completed_time


% warnings reviewed monthly

Numerator:

    • Count of warning forms from Alerts and Warnings (Messaging config) > Warnings

Denominator:

    • Count of all alerts triggered (daily screening forms with warning_status = yes)

Total number of alerts triggered each month

Count of formid from the Daily Screening Forms were the alert_status = yes. The daily screening forms are the following:

    • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
    • CMI with phone > Daily Screening,
    • CMIs without phones > Daily Screening, Value: alert_status = yes

% of CMIs with alerts this month

Numerator:

    • Count of distinct case_id warning submissions with the property alert_status = yes, from the following forms:
      • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
      • CMI with phone > Daily Screening,
      • CMIs without phones > Daily Screening,

Denominator:

    • Count of the total number of cmi cases that are open

Total number of alert reviews/responses each month

Count of submissions (formid) from the form:

  • Alerts and Warnings (Messaging config) > Alerts

Grouped by the completed_time

% alerts reviewed monthly

Numerator:

    • Count of distinct case_id alert submissions with the property alert_status = yes, from the following forms:
      • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
      • CMI with phone > Daily Screening,
      • CMIs without phones > Daily Screening,

Denominator:

    • Count of the total number of cmi cases that are open

monthly opt-outs

Count of submissions (formid) from the following forms that have the respective CMI case in open status (closed = '') :

  • CMIs with phones > Stop Screening
  • CMIs without phones > Opt-out
Grouped by the completed_time

% opted out

Numerator:

  • Count of unique case_ids from the following forms that have the respective CMI case in open status (closed = '') :
    • CMIs with phones > Stop Screening
    • CMIs without phones > Opt-out

Denominator:

  • Count of all opened cmi cases

Overview of the indicators detailed below in the graph

CMI registered:

  • total number of cmi cases

Screenings done

  • Count of submissions (formid) , from the following forms:

    • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
    • CMI with phone > Daily Screening,
    • CMIs without phones > Daily Screening,


Warnings triggered

  • Count of submissions (formid) with the property warning_status = yes, from the following forms:

    • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
    • CMI with phone > Daily Screening,
    • CMIs without phones > Daily Screening,

Warnings Reviewed:

  • Count of submissions (formid) from the Alerts and Warnings (Messaging config) > Warnings

Alerts triggered:

  • Count of formid from the Daily Screening Forms were the alert_status = yes. The daily screening forms are the following:

      • Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
      • CMI with phone > Daily Screening,
      • CMIs without phones > Daily Screening, Value: alert_status = yes

Alerts Reviewed:


  • Count of submissions (formid) from the form: Alerts and Warnings (Messaging config) > Alerts

Opt-outs:

    • Count of unique case_ids from the following forms that have the respective CMI case in open status (closed = '') :
      • CMIs with phones > Stop Screening
      • CMIs without phones > Opt-out


Setting up your dashboard

If you are on an 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 Community Messaging: CommunityPulse page as you dig into your data.


To create a manual dashboard

  1. In your CommCare project space where you've imported your imported Community Messaging: CommunityPulse app, go to DataGo to Data > View All
  2. Click "Export Case Data"
  3. Create a new case export for both the cmi case and the following forms:
    1. Daily Screening for CMIs without phones > Daily Screening for CMIs without phones,
    2. CMI with phone > Daily Screening,
    3. CMIs without phones > Daily Screening,
    4. CMIs with phones > Stop Screening,
    5. CMIs without phones > Opt-out.
  4. Click "Prepare Export"
  5. Click "Download"
  6. In Tableau Desktop, open the worksheet and import the worksheet
  7. Go to the Data Source tab and edit each of the connections to match the files downloaded in step 5.
  8. If you notice any calculated fields which were referring to the old data source become invalid and are marked with an exclamation point. You can quickly resolve the problem by replacing the field’s references following these instructions (see Replace Field References section).



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.

  1. In your CommCare project space where you've imported your imported Community Messaging: CommunityPulse app, go to Data > PowerBi/Tableau Integration and add an Odata Feed per case type and form you want to feed into your dashboard. See step 3 in the 'To create a manual dashboard' section above for the specific case types and forms to create odata feeds for.

  2. Download the worksheet in the .twbx file above and open it in the Tableau Desktop software

  3. Once you have opened the Worksheet go to Data Source tab and edit each of the connections, Search for Odata

  4. Input the URLs from step 1 and login using your commcare username and API key as your password as per instructions provided here.
  5. If you notice any calculated fields which were referring to the old data source become invalid and are marked with an exclamation point. You can quickly resolve the problem by replacing the field’s references following these instructions (see Replace Field References section).

To help get you started, check out this demo video on creating the Odata feed from CommCare HQ to connect to your Tableau dashboard.