...

A graphical dashboard	Individual pages with data:	User definable filters:
Image RemovedImage Added	Image RemovedImage Added	Image RemovedImage Added

Getting Started

...

1. Proceed to the Data section of CommCareHQ.

2. Select Excel Dashboard on the left-hand side.
   

   Image RemovedSee this Data Export Overview page for Image Added

3. See https://dimagi.atlassian.net/wiki/x/5SbKfw 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

...

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. If you are using two factor authentication, review the custom instructions in the box below. If you do not use two factor authentication, proceed to step 13.  
    

    Info
    icon false title 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 Modified Note: If the wrong username or API_KEY are provided, a retry dialog will display like the following: Image Modified 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

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
    

    Image RemovedImage Added

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 RemovedImage Added

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 RemovedImage Added

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:

...