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:
- Requires upfront work by an administrator just once, and then is easy to maintain going forward
- Refreshes automatically from CommCareHQ when you reopen the report (or set time based refresh).
- Allows users to filter the dashboard for information relevant to them without detailed knowledge of how Excel tables work.
- 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:
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.
Report Dashboard.xlsx: The Dashboard file. Open this to see the final product. *** You will get errors upon opening because the Raw Data file is not present, but just say OK and it will still work. See Troubleshooting section at the bottom to point the file to your Raw Data ***
Raw Data.csv: The raw data downloaded from CommCare. Save this to your computer and follow the instructions in the Troubleshooting section at the bottom.
Export from CommCare.zip: The zip file that contains Raw Data.csv that is downloaded from CommCareHQ.
CommCareHQ Application from the App Exchange (Coming Soon): This tutorial uses a very simple "Pregnancy Tracker" application to create the example data. You can download this application from the Exchange to try yourself shortly.
Step 1: Create the data export and download data See this page for details on creating the data export: Data Export Overview For Export Name name it "Raw Data" For Default File Type choose CSV (zip file) as the Default File Type. Excel won't recognize dates properly if you use an Excel file type. Be sure to rename your Display columns to how you want them to display in your reports. Download your export and save the zip file to your computer
Unzip the file you downloaded. It should contain a file called "Raw Data.csv"
If it is not a zip file, you need to edit the export and choose CSV as the default file type Your exported data will look like the below picture:
Step 2: Create the Report File and Import the Data The next step is to create the file that will hold the reports. This is a separate Excel file than your raw data file. This way you can update the raw data without having to change the reports file. In Excel, Create a New File. Save the file as "Reports.xlsx" in the same directory that you save the export in Step 1. You will have a blank Excel file now. Now we will pull in the data from the csv file Choose the Data menu Choose From Text in the "Get External Data" options in the top left Browse and select your "Raw Data.csv" file from step 1 On the first screen choose Delimited and click Next. On the second screen check the box for Comma and uncheck all other boxes. Click Next. On the third screen click Finish. Choose New Worksheet in the next window the click "Properties..." In the Properties window check the box for Refresh data when opening the file
This will make the data update every time you download new data from CommCareHQ Rename the tab "Raw Data" You now have the data in your reports file and it will automatically update each time you open the file with your most recently downloaded data.
Step 1: Create Custom Export in HQ for the project
- See this page for details on creating the data export: Data Export Overview
- For Export Name name it "Raw Data"
- For Default File Type choose CSV (zip file) as the Default File Type. Excel won't recognize dates properly if you use an Excel file type.
- Be sure to rename your Display columns to how you want them to display in your reports.
- Once you create the export, it would be listed under 'Saved Custom Exports'.
- Across the export, click on 'HTML Preview' button. It would show you the raw data in the browser. Copy the URL.
Step 2: Create the Report File and Import the Data
The next step is to create the file that will hold the reports.
- In Excel, Create a New File. Call it "Reports.xlsx"
- You will have a blank Excel file now.
- Now we will pull in the data from the csv file
- Choose the Data menu
- Choose From Web in the "Get External Data" options in the top left
- Paste the URL that you copied in Step 1. Then click Go.
- It might open a popup asking a username and password. Enter you CommCareHQ username and password.
- You would see some data in the window. In the top left of the data, notice a small yellow box with a forward arrow (there might be a couple of them).
- Click on the yellow box, just above the data. This would select the data to be imported, and the box would turn into a green checkbox.
- Click on 'Import'. It's important to click on the yellow box with arrow, before clicking on Import, otherwise things will not work properly.
- Choose New Worksheet in the next window the click "Properties..."
- In the Properties window
- Check the box for Refresh data when opening the file
- Check the box for Refresh every 60 minutes
- This will pull the latest data from CommCareHQ every time you open the file, or after 60 mins.
- Click OK
- Rename the tab "Raw Data"
- You now have the data in your reports file and it will automatically update each time you open the file or after 60 mins.
Step 3: 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 the icon next to "Table/Range" to select the data the pivot table will use.
- Go to the Raw Data tab and select all the columns
- The small window should populate with 'Raw Data'!$A:$E. Click the icon next to that.
- Click Ok.
- This creates a blank pivot table.
- 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, click the 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 chart 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 4: Create Additional Pivot Charts
- Repeat all the steps in Step 3 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 5: 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 6: 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 7: 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.
Step 8: Test it out
To test that everything is updating properly, do the following:
- Make changes to the data in CommCare or directly in your raw data file.
- Download a new version of the export
- Unzip the export and save it as the same name as the old export file.
- Open and close the Reports Excel file.
- Confirm the data was updated.
Step 9: Send to Others
If you just send the Excel file to others they will get errors when they open it since it will look for the raw data file but not find it. You can do the following when sending to others:
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)
- Chosse File - Save As
- Select PDF as the File Type
"Excel cannot find the text file to refresh this external data range"
This means the data export file has either been renamed or does not exist.
- Check that the raw data is in the folder with the correct name.
- If still have an issue, reset the data connection by choosing Data -> Connections and edit the connection you made. Browse to the correct file and select it.