Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 collected in CommCare and use the exported data in and load it in Excel to create dashboards. 

 

Note
titlePricing Plan

Excel Dashboards requires require a Standard Plan or higher. For more information regarding pricing plans and available features, please proceed to https://www.commcarehq.org/pricing/.

Note that Excel dashboard integrations are primarily designed for PCs. Mac users should consider other data extraction methods such as the Data Export Tool data export tool, manual exports, or the daily saved export.

...

  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 (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)
  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

...

  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.
    Image Modified
  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
        Image Modified
  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:

...

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. 

Image Added

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:

...