This page walks through building an Excel dashboard with a mobile tool called CommCare. Similar to how Excel helps people build spreadsheets, CommCare enables anyone to build a free customized mobile app - no IT background required. Once you sign up for CommCare and build your free app, you can download all of the data you collect with your app into Excel and create your own Excel dashboard.

Pricing Plan

Please note, that while CommCare itself is free, the use of Excel Dashboards requires a Standard Plan or higher. For more information regarding pricing plans and available features, please proceed to https://www.commcarehq.org/pricing/.

Warning to Mac Users

Mac users should be aware that Excel dashboard integrations are currently not well supported by Macs, and we encourage other data extraction methods such as the Data Export Tool, manual exports, or the daily saved export.

 

Overview

This tutorial will walk you through how to use Microsoft Excel to build a simple dashboard using CommCareHQ data exports. By the end of the tutorial you will be able to create a dashboard that:

  1. Requires upfront work by an administrator just once, and then is easy to maintain going forward
  2. Refreshes automatically from CommCareHQ when you reopen the report (or set time based refresh).
  3. Allows users to filter the dashboard for information relevant to them without detailed knowledge of how Excel tables work.
  4. Enables an administrator to quickly make new reports and send to end users.

The intended audience for this page is a CommCare administrator who is familiar with CommCareHQ and has some experience in Excel. The end result will contain the following:

**** Download the Example Dashboard **** 

 

A graphical dashboard

Individual pages with data:

User definable filters:

Getting Started

Excel Version:  This tutorial was written for Microsoft Excel 2010 . Most of the functionality described is also available in earlier versions of Excel though may be called different names.

Data Files:

CommCare Dashboard.xlsx: The Dashboard file.  Open this to see the final product. 

Raw Data.csv: The raw data downloaded from CommCare for reference.

Step 1: Create Custom Export in HQ for the project

  1. Proceed to the Data section of CommCareHQ.
  2. Select Excel Dashboard on the left-hand side.
  3. See this Data Export Overview page for details on creating the data export:
    1. Name your export 
    2. Choose Automatically convert dates for Excel
    3. Be sure to rename your Display columns to how you want them to display in your reports.
  4. 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:

  1. In the main Exports section you will see your saved daily export.
  2. Click Update Data button to generate the first report. You may need to refresh the page to confirm that the update is complete.
  3. Select the Copy Dashboard Feed Link button. 
  4. Select the Copy Dashboard Feed Link button again to copy the URL. This will copy the URL of the export file so you can paste into Excel in the next step.
    1. 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.

  1. In Excel, Create a New File.
  2. You will have a blank Excel file now.
  3. Go to a blank tab in your workbook. Rename that sheet "Raw Data".
  4. Select the top-left cell (A1).
  5. Now we will pull in the data from the Excel file
  6. Choose the Data menu
  7. Choose From Web in the "Get External Data" section
  8. Be sure you have Copy link address from step 2 above so it is on your clipboard.
  9. The "New Web Query" window appears.  
  10. In the "Address" bar at the top, Paste the link you address you copied in Step 2 above.  It should be something like "https://www.commcarehq.org/a/excelreportingdemo/reports/export/saved/download/96c41fd6fb9be6119ad32467254cbc9a/"
  11. Click Go
  12. The "Windows Security" window appears. Type your CommCareHQ username and password.
  13. The export will be displayed to you in the Web Query Window
  14. 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.
  15. Click Import
  16. The "Import Data" window appears.
  17. Click Properties
  18. The "External Data Range" window appears
    1. In the top of the window, name your export "Raw Data"
    2. Choose Save query definition
    3. Choose Enable background refresh
    4. Choose Preserve Cell Formatting
    5. Choose Adjust column width
    6. Choose Overwrite existing cells with new data, clear unused cells
    7. Choose Fill down formulas in columns adjacent to data
    8. Click  OK



  19. Click OK again
  20. The data is pasted into the empty worksheet

Step 4: Name your raw data for easy reference later

  1. Now that your data is in the Raw Data tab, we want to name it so we can refer to the data later
  2. Select all the columns that have data in them by clicking on the top of the columns while holding shift.
  3. With the columns selected, click in the text box above column A (It likely says "A1" in it)
  4. This is where you type the name of the raw data for reference later. Name it "raw_data".
  5. 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.

  1. Create a new tab called "Expected Delivery Date"
  2. Select the top-left cell (A1) in this tab
  3. In the Insert menu choose Pivot Chart from the PivotTable icon
  4. In the window that pops up, click  "Table/Range" text box
  5. 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.
  6. Click Ok.
  7. 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)
  8. Click the pivot table on the left
  9. In the Field list on the right, do the following:
    1. Drag Expected Delivery Date to "Row Labels"
    2. Drag District to "Column Labels"
    3. Drag District to "Values"
    4. Note: If you don't see the "Pivot Table Field List" on the right, click the Options menu at the top and choose Field List in the top right.
  10. This will be the result:
  11. We now have the data we want but it needs to be formatted:
    1. You can drag the graphical chart to the side to get it out of the way
  12. Next, we need to display the dates as months instead of days
      1. Right-click on any of the dates in the pivot table and choose Group
      2. Select Months in the list the appears and choose OK
      3. This groups all the EDDs into monthly buckets
  13. The graph defaults to a bar chart, but you can change it:
    1. Right-click the graph and choose Change Chart Type
    2. Select the line graph you'd like
  14. Set the table to update automatically
    1. Highlight the pivot table
    2. Choose the Options menu and in the top-left type the name "EDD Graph"
    3. Click Options below where you just typed
    4. On the Data Tab select Refresh data when opening the file
  15. Finally, rearrange the layout by inserting rows above table to move it down and drag the chart to the top of the page.
  16. You can add titles and legends with the Layout menu
  17. You're final result should look like this:

Step 6: Create Additional Pivot Charts

  1. Repeat all the steps in Step 5 as many times as you'd like to create more charts
  2. In this example, see the screen shot for how the "Patients Received Vaccines" chart was made.
  3. Be sure to set all table to update automatically (Step 15 in the section above)

Step 7: Create Filters

  1. Excel uses "Slicers" to enable users to filter the reports in a user-friendly way.
  2. For the first pivot table you created do the following:
    1. Select the chart you created
    2. Choose Analyze menu at the top
      1. You may need to select Options menu instead
    3. Choose Insert Slicer from the menus
    4. Select the items you will want to filter your reports by and click OK.
    5. Drag the filters and resize them to fit the layout as you like

  3. Now we want this filter to apply to all charts in our Worksheet, so we connect the filters to the other tables by:
    1. Select one of the filters you just created
    2. Choose Options then PivotTable Connections
    3. Check all the boxes for the other tables you created.
    4. Now changing this filter will change the filters on all the tables you created.
    5. Repeat this step for all filters
  4. 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

  1. Create a new tab called Dashboard
  2. Copy and Paste the charts from other tabs in to the dashboard tab
  3. Copy and Paste the filters you created, if you'd like
  4. 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:

  1. "Protect" all data
    1. Right-click each tab and choose Protect Sheet
    2. Scroll to the bottom of the list and check the box for Edit Objects and Use PivotTable Reports
      1. This will allow users to change filters but not edit the data
      2. Do NOT protect the "Raw Data" tab or the data will not update automatically
  2. Hide the Raw Data tab (if desired)
    1. If you'd like to hide the raw data tab, you can right-click it and choose Hide
    2. 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.
  3. Choose PivotTable option in Step 4
    1. Instead of putting the raw data on a tab you can directly make pivot tables when importing.
    2. 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:

  1. Choose Data menu
  2. Click Refresh All
  3. 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.

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

  1. In the Data menu choose Connections
  2. Select the Connection you made and choose Remove
  3. 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)

    1. Chosse File - Save As
    2. Select PDF as the File Type
  • No labels

12 Comments

  1. Anonymous

    This is an excellent tutorial. Thank you

  2. Anonymous

    Awesome one. please keep posting more tutorials on dashboards Thanks

  3. Anonymous

  4. Anonymous

    Very useful tutorial. Thank you

  5. Anonymous


    Thank you so much, this was the best information ever!!!  My VP of IT gave me a project to create a dashboard for the company printers, cost, etc.  He asked if I knew how to make a dashboard and I said no.  He told me to Google it or phone a friend, lol.  I did that and wasn't able to find good free information that wasn't on U-tube, which the job has blocked.  I'm the type of learner that has to go over things a couple of times to really get it...  With the information you provided I was able to do my regular work while trying to teach myself how to make these dashboards.  I feel that with the practice I am armed and ready to make the printer dashboards for my VP.

    Keisha B.

  6. Anonymous

    very helpful..Thank You (smile) you made my work very easy

  7. Hi Nick,

    Great Tutorial.

    As you may know, I am an avid Google fan, so I tried to reproduce your tutorial using Google Spreadsheet and Google Pivot Report. I just copied and pasted your raw data into a Google Spreadsheet, and gave the Pivot Report a run. Here is what I found.

    • No grouping function (or any other "data massaging" function") in Google report. E.g., if I want a chart by month, I need to add a colum with the month value. So what I would need to do, is
      1. Define what values I need in my chart
      2. Go back to CommCare and include them as extra column (with calculated value) in my export
    • Google Charts doesn't have as many different line and bar charts as excel. Howver they do have some pretty coll charts that could be useful for Commcare users, and which have been removed from Excel - such as GeoMap, Motion Chart (see data evolve over time), and gauge dashboards
    • Use of Google spreadhseets, Pivot Tables and Charts does take away some of the admin hassle, such as login, automatic update - that is all included / automatic.

    I.e. in a nutshell, not as sophisticated yet as Excel, but worth keeping in mind / trying it ouy, especially if you need GeoMap features, Motion Charts,... You can try it out easily (just copy and paste data over for now).

    Hope that helps, thx again for great tutorial!

     

  8. Anonymous

    Thanks a lot.. By far the best tutorial... Please come up with more dashboards... Thanks a lot


     

  9. Anonymous

    Hi, my Teachers on Python, Jonathan Jackson and Cory Zue, is a wonderful dashboard. Thanks alot

  10. Anonymous

    Excellent Tutorial! Thank you so much!

  11. Anonymous

    Very good and informative tutorial. I was looking for a tutorial that is not on YouTube which is blocked during office hrs and this did the trick!!

  12. Anonymous

    I am trying to create an Excel dashboard, but when I get to the part where i have to import my data onto Excel (Step 3), Excel asks if I want to open the file or save it.  I cannot seem to import it.  Can you help me figure this out?  Thanks.