You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 18 Next »


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. Refreshes automatically when you download a new data export from CommCareHQ.
  2. Allows users to filter the dashboard for information relevant to them.

The final result will contain the following:

A graphical dashboard

Individual pages with data:

User definable filters:

Step 1: Create the data export and download data

  1. See this page for details on creating the data export: Raw Data
    1. For Export Name name it "Raw Data"
    2. For Default File Type choose CSV (zip file) as the Default File Type 
    3. Be sure to rename your Display columns to how you want them to display in your reports.
  2. Download your export and save the zip file to your computer
    1. If it is not a zip file, you need to edit the export and choose CSV as the default file type
  3. Unzip the file you downloaded. It should contain a file called "Raw Data.csv"
  4. 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.

  1. In Excel, Create a New File.
  2. Save the file as "Reports.xlsx" in the same directory that you save the export in Step 1.
  3. You will have a blank Excel file now.
  4. Now we will pull in the data from the csv file
    1. Choose the Data menu
    2. Choose From Text in the "Get External Data" options in the top left
    3. Browse and select your "Raw Data.csv" file from step 1
    4. On the first screen choose Delimited and click Next.
    5. On the second screen check the box for Comma and uncheck all other boxes. Click Next.
    6. On the third screen click Finish.
    7. Choose New Worksheet in the next window the click "Properties..."
    8. In the Properties window check the box for Refresh data when opening the file
      1. This will make the data update every time you download new data from CommCareHQ

    9. Click OK
    10. Rename the tab "Raw Data"
    11. 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 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.

  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 the icon next to "Table/Range" to select the data the pivot table will use.
  5. Go to the Raw Data tab and select all the columns
  6. The small window should populate with 'Raw Data'!$A:$E. Click the icon next to that.
  7. Click Ok.
  8. This creates a blank pivot table.
  9. Click the pivot table on the left
  10. 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.
  11. This will be the result:
  12. 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
  13. Next, we need to display the dates as months instead of days
      1. Right-click on any of the dates in the chart and choose Group
      2. Select Months in the list the appears and choose OK
      3. This groups all the EDDs into monthly buckets
  14. 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
  15. 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
  16. Finally, rearrange the layout by inserting rows above table to move it down and drag the chart to the top of the page.
  17. You can add titles and legends with the Layout menu
  18. You're final result should look like this:

Step 4: Create Additional Pivot Charts

  1. Repeat all the steps in Step 3 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 5: 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 do the following



  • No labels