This page walks through building an Excel dashboard with data from CommCare. Similar to how Excel helps people build spreadsheets, CommCare enables anyone to build a free customized mobile app - no IT background required. After collecting data with your CommCare app (don't yet have an account? Sign up for a free trial here.) you can download your data and load it in Excel to create dashboards.
Excel Dashboards require a CommCare Software Plan
This feature (Excel Dashboards) is only available to CommCare users with a Standard Plan or higher. For more details, please proceed toCommCare Software Plan page.
Be sure to rename your Display columns to how you want them to display in your reports.
Save the export
Step 2: Copy the Web Address of your saved export
We will use the Excel Dashboard Integration report that we created to populate the dashboard. Follow these steps to configure your Excel sheet to pull the latest data from the report:
In the main Exports section you will see your saved daily export.
Click Update Data button to generate the first report. You may need to refresh the page to confirm that the update is complete.
Select the Copy Dashboard Feed Link button.
Select the Copy Dashboard Feed Link buttonagain to copy the URL. This will copy the URL of the export file so you can paste into Excel in the next step.
Alternatively, you could simply highlight the URL to copy it.
Step 3: Create the Excel Connection to your export
The next step is to create the file that will hold the reports.
In Excel, Create a New File.
You will have a blank Excel file now.
Go to a blank tab in your workbook. Rename that sheet "Raw Data".
Select the top-left cell (A1).
Now we will pull in the data from the Excel file
Choose the Data menu
Choose From Web in the "Get External Data" section (NOTE: If you are getting issues when trying to import data to the web, you may want to use From Web (Legacy) instead of the standard From Web. Please see Alternative Web Data Access Method for Step 3, below)
Be sure you have Copy link address from step 2 above so it is on your clipboard.
The "Windows Security" window appears. Type your CommCareHQ username and password. If you are using two factor authentication, review the custom instructions in the box below. If you do not use two factor authentication, proceed to step 13.
Special Instructions for Users with Two-Factor Authentication
This box is only for accounts with two factor authentication enabled. If you do not use two factor authentication, proceed to (13) below.
Click the "Advanced" radio button. The URL supplied from step 10 should remain in the URL field.
Under the "HTTP request header parameters (optional)" field, add "Authorization". NOTE: This is not a value found from the dropdown options.
For the field next to this header, type "ApiKey <username>:<API_KEY>". For example, if your username is "JohnDoe@google.com", and your API Key is 12345, the field should look like: "ApiKey JohnDoe@google.com:12345"
Note: If the wrong username or API_KEY are provided, a retry dialog will display like the following:
With API Key verification, it is necessary to click back to fix the error and try again. Both the "Basic" and "Web API" options will not work.
The export will be displayed to you in the Web Query Window
Click the second Yellow Arrow above the table of data (not the very top one). This will select all the CommCare data in the export.
The "Import Data" window appears.
The "External Data Range" window appears
In the top of the window, name your export "Raw Data"
Choose Save query definition
Choose Enable background refresh
Choose Preserve Cell Formatting
Choose Adjust column width
Choose Overwrite existing cells with new data, clear unused cells
Choose Fill down formulas in columns adjacent to data
Click OK again
The data is pasted into the empty worksheet
Alternative Web Data Access Method for Step 3
Some users experience errors when attempting to import data using the standard "From Web" functionality. Specifically, you may encounter a persistent error similar to:
Details: "The credentials provided cannot be used for the Web source. Please update the credential type through a refresh or in the Data Source Settings dialog to continue. (Source at https://www.commcarehq.org/.)"
If you are encountering this error, please make sure Microsoft Office and your OS are up to date, and that your computer has been restarted since performing these updates. If, after updating everything, you still encounter this error, you may find more success using a legacy version of the From Web tool. The following instructions allow to access the tool in Excel 2016:
Navigate to File -> Options to open the options window.
Click Customize Ribbon in the pane on the left.
In the dropdown under Choose commands from: select All Commands
Find From Web (Legacy) in the scrolling menu on the left.
In the right-hand menu, select the Data.
Right Click on Data, to create custom New Group. Rename it if you want.
Select the group you just created and click Add >>
After following the above steps, you should now have access to From Web (Legacy) in the Data ribbon. Now, whenever you want to import data from the web, instead of clicking From Web in step 7, you will select From Web (Legacy).
Step 4: Name your raw data for easy reference later
Now that your data is in the Raw Data tab, we want to name it so we can refer to the data later
Select all the columns that have data in them by clicking on the top of the columns while holding shift.
With the columns selected, click in the text box above column A (It likely says "A1" in it)
This is where you type the name of the raw data for reference later. Name it "raw_data".
Now, whenever you want to reference the raw data you can type "raw_data" in the formula instead of "='Raw Data'!A:E" like you normally would.
Step 5: Create the first Pivot Chart
The first chart we will create is the Line Graph of Expected Delivery Date. This will plot the number of women expected to give birth in each month, broken down by Clinic.
Create a new tab called "Expected Delivery Date"
Select the top-left cell (A1) in this tab
In the Insert menu choose Pivot Chart from the PivotTable icon
In the window that pops up, click "Table/Range" text box
Type "raw_data" into the box. This references the data range we created in the step above and is a lot easier than selecting all the columns each time.
This creates a blank pivot table. (If this doesn't work it's likely because the range is referencing only to the first tab. Go back to step 4.2, then click "Formulas" at the top, next to "Name Manager" click on "Define Name", enter "raw_data" under "Name" and make sure "Scope" is set to "Workbook" as this will ensure the reference can be used in different tabs, click OK)
Click the pivot table on the left
In the Field list on the right, do the following:
Drag Expected Delivery Date to "Row Labels"
Drag District to "Column Labels"
Drag District to "Values"
Note: If you don't see the "Pivot Table Field List" on the right, clickthe Options menu at the top and choose Field List in the top right.
This will be the result:
We now have the data we want but it needs to be formatted:
You can drag the graphical chart to the side to get it out of the way
Next, we need to display the dates as months instead of days
Right-click on any of the dates in the pivot table and choose Group
Select Months in the list the appears and choose OK
This groups all the EDDs into monthly buckets
The graph defaults to a bar chart, but you can change it:
Right-click the graph and choose Change Chart Type
Select the line graph you'd like
Set the table to update automatically
Highlight the pivot table
Choose the Options menu and in the top-left type the name "EDD Graph"
Click Options below where you just typed
On the Data Tab select Refresh data when opening the file
Finally, rearrange the layout by inserting rows above table to move it down and drag the chart to the top of the page.
You can add titles and legends with the Layout menu
You're final result should look like this:
Step 6: Create Additional Pivot Charts
Repeat all the steps in Step 5 as many times as you'd like to create more charts
In this example, see the screen shot for how the "Patients Received Vaccines" chart was made.
Be sure to set all table to update automatically (Step 15 in the section above)
Step 7: Create Filters
Excel uses "Slicers" to enable users to filter the reports in a user-friendly way.
For the first pivot table you created do the following:
Select the chart you created
Choose Analyze menu at the top
You may need to select Options menu instead
Choose Insert Slicer from the menus
Select the items you will want to filter your reports by and click OK.
Drag the filters and resize them to fit the layout as you like
Now we want this filter to apply to all charts in our Worksheet, so we connect the filters to the other tables by:
Select one of the filters you just created
Choose Options then PivotTable Connections
Check all the boxes for the other tables you created.
Now changing this filter will change the filters on all the tables you created.
Repeat this step for all filters
Copy and Paste the filters to other areas of your Excel sheet if you'd like them to appear in more than one place
Step 8: Build the Dashboard with Copy-Paste
If you'd like to display all the charts on one page, you can copy and paste the reports and table anywhere you'd like
Create a new tab called Dashboard
Copy and Paste the charts from other tabs in to the dashboard tab
Copy and Paste the filters you created, if you'd like
A sample dashboard looks like:
Step 9: Hide the Raw Data
One of the challenges with Excel reporting is ensuring that users you send the reports to do not accidentally corrupt the data or people get confused with too many options. We can mitigate many of these issues with two methods:
"Protect" all data
Right-click each tab and choose Protect Sheet
Scroll to the bottom of the list and check the box for Edit Objects and Use PivotTable Reports
This will allow users to change filters but not edit the data
Do NOT protect the "Raw Data" tab or the data will not update automatically
Hide the Raw Data tab (if desired)
If you'd like to hide the raw data tab, you can right-click it and choose Hide
To unhide, right click on any sheet name, choose Unhide. It would show you the list of hidden sheets and you can pick the one to unhide.
Choose PivotTable option in Step 4
Instead of putting the raw data on a tab you can directly make pivot tables when importing.
This means the raw data tab will not exist but the pivot tables will.
Step 10: Refresh Your Data
Each night CommCare will save the updated data to that file. To have Excel pull in the latest, do the following:
Choose Data menu
Click Refresh All
This will pull the latest data from CommCareHQ. You will be prompted for your password.
If Excel hangs during the refresh, disable Enable Background Refresh on all data connections. This is a known problem for spreadsheets with multiple web-based data connections. Find it under Data -> Connections -> Select Connection, click Properties -> unselect Enable Background Refresh.
NOTE: The data needs to be pulled/downloaded every 35 days (see warning label below) to maintain an active data connection/link. This can be achieved by clicking on Refresh All in the Excel dashboard (see instructions above). Otherwise, the data connection will expire and Step 3 needs to be repeated to re-establish a data connection.
Step 11: Send to Others
If you just send the Excel file to others they will be prompted to enter login credentials in order to refresh the data. If you don't want this to happen, you can do the following:
Option 1: Delete the Connection to the Raw Data file
In the Data menu choose Connections
Select the Connection you made and choose Remove
This file will no longer update automatically but will still retain the most recent data.
Option 2: Export to pdf (might not be available on all computers)