...

Note: Mac users -  Excel dashboard integrations are primarily designed for PCs. Mac users should consider 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:

...

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 Excel Dashboard

...

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.

...

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.

...

c. 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.

...

Panel
panelIconId atlassian-info panelIcon :info: bgColor #EAE6FF
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" Image Removed on "Basic" to select basic authorization. Enter your CommCare username in the “User Name” field and a valid API key as your password. Image Added Note: If the wrong username or API_KEY are provided, a retry dialog will display like the following: Image Removed Image Added 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.

13. The export will be displayed to you in the Web Query Window.

...

20. 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:

...

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

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.

...

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.

...

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:

...

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:

...

1. Choose File - Save As

2. Select PDF as the File Type

Bulk delete Excel Dashboard Integration Feed Reports

The bulk delete excel dashboard integration feed reports gives you the option to bulk delete multiple exports at once. 

...

• In the Export Dashboard you will find a check box to select all or specific exports

• On checking the box, you will notice a "Delete Selected Export" button becomes available on the left side of the page

• Click on the "Delete Selected Export" button and you will be promoted to confirm the bulk delete action

• Once Delete Export is clicked it will bulk delete the exports selected at once.

...

Common Errors & Troubleshooting Steps

Users can unexpectedly encounter errors when setting up the Excel integration, which can be easily resolved by following this troubleshooting guide.

1. Excel Dashboard integration feed link is disabled and my data is not getting updated

   1. Re-enable the disabled feed.

   2. Try using a different browser.

2. If you face the issue seen in the screenshot below, attempt using “Basic” instead of “Anonymous” or use the From Web (Legacy) option instead:

   

3. If you have Two-Factor Authentication enabled and are experiencing issues with your credentials (API Key):

   1. Navigate to your CommCare project space → My Account Settings → API Keys → Check if the API key is active and not expired. If it is expired, you will need to generate a new one.

4. If your credentials are causing an error on your device not allowing the connection to be made, try setting up the connection on another device.

   1. A common issue is the absence of a Microsoft account on the device, which can prevent the connection from being established.

   2. In this case, check with your internal IT team to see if your Microsoft account might be causing issues with the Excel integration.