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.
Image Removed
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.
Image Added
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
...
| Ui expand |
|---|
| title | Report and Indicator Descriptions |
|---|
|
Dashboard item | Description | Indicator 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
|  Image Removed Image Added
| Overview of the indicators detailed below in the graph | CMI registered: - total number of cmi cases
Screenings done
Warnings triggered Warnings Reviewed: Alerts triggered:
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
|
|
...
| Ui expand |
|---|
| title | Steps to create manual dashboard |
|---|
|
- In your CommCare project space where you've imported your imported Community Messaging: CommunityPulse app, go to DataGo to Data > View All
- Click "Export Case Data"
- Create a new case export for both the cmi case and 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,
- CMIs with phones > Stop Screening,
- CMIs without phones > Opt-out.
- Click "Prepare Export"
- Click "Download"
- In Tableau Desktop, open the worksheet and import the worksheet
- Go to the Data Source tab and edit each of the connections to match the files downloaded in step 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).
|
...
| Ui expand |
|---|
| title | Steps to create a dashboard integration (requires a paid plan) |
|---|
|
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 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. Download the worksheet in the .twbx file above and open it in the Tableau Desktop software Once you have opened the Worksheet go to Data Source tab and edit each of the connections, Search for Odata - Input the URLs from step 1 and login using your commcare username and API key as your password as per instructions provided here.
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. |
...
