1. This training is not intended to be a Microsoft Excel training and it is expected you have some basic understanding of the different tools that will be discussed. If you need additional training around any of the tools covered in this Knowledge Base, please refer to Microsoft’s extensive training materials.
2. You may want to review the 'Understanding Contact Record Data Export Files' section of the Data Export Knowledge Base in addition to the information below, as you will be most successful with your reporting if you have a thorough understanding of what data points are included in each file. 
3. You may also want to watch the Statistics and Reporting Training Webinar series to learn more, as the information in this Knowledge Base is covered in detail in the ‘Advanced Reporting: Contact Record Data Exports - Part 3’ recording. 

1. Use Case 1: Contacts by Gender and Age Range – these are both custom fields included in the Contact Form Versions included in my Data Export, so both fields are included in the ‘Call Report’ file. Create a PivotTable using the ‘Call Report’ file to get this data. Note, since all the data is in one file, you only need to create a simple PivotTable.
2. Use Case 2: AIRS Problems Needs by Gender – the ‘Gender’ data is found in the ‘Call Report’ file, and ‘AIRS Problems Needs’ data is found in the ‘Call Report Needs Met and Unmet’ file. Create a PivotTable Relationship using the ‘Call Report Needs Met and Unmet’ and ‘Call Report’ files to get this data. Note, since the data is found in two or more files, you will need to create a relationship between tables to create your PivotTable.

Use Case 1: Contacts by Gender and Age Range – Simple PivotTable

1. Download your Contact Record Data Export files and open the ‘CallReports’ file
   
2. Delete the first two rows of the file – this data is related to the export request and doesn’t contain any relevant information for this report
   
3. Format your data as a Table, by completing the following steps:
   
1. Select any cell that will be included in your Table
   
2. Click the Insert tab in the ribbon
   
3. Click the Table button
   
4. Leave the default options selected and click ‘OK’ and notice your data now appears as a table
   
4. Create a PivotTable, by completing the following steps:
   
1. Select any cell in the table that will be included in your PivotTable
   
2. Click the Insert tab in the ribbon
   
3. Click the ‘PivotTable’ button
   
4. Leave the default selections and click ‘OK’ and notice a new tab opens where you add fields to your PivotTable
   
5. Your PivotTable Fields to choose from are listed on the right side of the page, along with the different areas of the PivotTable to place your fields
   
6. Use the search bar to type the field name(s), or find the field(s) in the list you wish to report on; once you find the field, drag it into the appropriate Area (i.e. Filters, Columns, Rows, Values); for this example, we’re looking for Age Range and Gender
   
7. Try placing your Fields in the following Areas; see Image 1 below to see this example:
   
1. Columns: Age Range
   
2. Rows: Gender
   
3. Values: Age Range
   
8. Now move the Fields to the following Areas and notice the differences and similarities in how the data is displayed; see Image 2 below to see this example:
   
1. Rows: Gender
   
2. Rows: Age Range
   
3. Values: Age Range
   
5. Adjust ‘Summarize Value Field By…’ calculation types, by completing the following steps:
   
1. For this example, the default ‘Value’ is set to ‘Count of…’, which is the option you want selected for this report
   
2. To adjust this setting for any report, under the ‘Values’ area click the down arrow appearing next to the field in this area and select ‘Value Field Settings’
   
3. To change the way the values are being calculated, choose the type of calculation you want to use to summarize data from the selected field, then click ‘OK’

Use Case 2: AIRS Problems Needs (AIRSNeedCategory) by Gender – Create relationship between tables in PivotTables

1. Download your Contact Record Data Export files and open the ‘CallReports’ and ‘CallReportNeedsMetAndUnmet’ files
   
2. Delete the first two rows of both files – this data is related to the export request and doesn’t contain any relevant information for this report
   
3. Combine the data into one workbook, by completing the following steps:
   
1. Choose one file to be your “main” workbook – it doesn’t matter which one you choose
   
2. Add a new sheet to your main workbook
   
3. Copy the data from the other file and paste it into the new sheet in your main workbook – this process will make your data easier to work with
   
4. Format your data as a Table in both worksheets, by completing the following steps:
   
1. Select any cell that will be included in your Table
   
2. Click the Insert tab in the ribbon
   
3. Click the Table button
   
4. Leave the default options selected and click ‘OK’ and notice your data now appears as a table
   
5. Repeat these steps in the other worksheet
   
5. Create a simple PivotTable using data in one of the Tables (it doesn’t matter which Table you choose), by completing the following steps:
   
1. Select any cell in the table that will be included in your PivotTable
   
2. Click the Insert tab in the ribbon
   
3. Click the ‘PivotTable’ button
   
4. Leave the default selections and click ‘OK’ and notice a new tab opens where you add fields to your PivotTable
   
5. Your PivotTable Fields to choose from are listed on the right side of the page, along with the different areas of the PivotTable to place your fields
   
6. Add another Table and create a relationship in this PivotTable, by completing the following steps:
   
1. Scroll to the bottom of the PivotTable fields list and click the link to ‘More Tables…’
   
2. A pop-up message appears that says ‘Do you want to create a new PivotTable…’; click ‘Yes’
   
3. Click the Analyze tab in the ribbon
   
4. Click the ‘Relationships’ button
   
5. The ‘Manage Resources’ pop-up window will open; click the ‘New’ button
   
6. The ‘Create Relationship’ pop-up window will open; from the ‘Table’ drop-down list, select either Table
   
7. In the ‘Column (Foreign)’ drop-down list, select the field that is found in both files, and is unduplicated (unique) in at least one of the files; note, this is usually going to be the ‘CallReportNum’ field, as it will be for this example
   
8. In the ‘Related Table’ drop-down list, select the other Table
   
9. In the ‘Related Column (Primary)’, select the field that mirrors the field you selected for ‘Column (Foreign)’; note, in some files the ‘CallReportNum’ field is named ‘ContactRecordNum’ instead, that is OK and you can still create this relationship
   
10. Click ‘OK’ to finish creating the relationship; note, a pop-up message may appear that says Excel will auto-correct the relationship for you – click ‘OK’ if this appears
    
11. After you create the relationship, close the ‘Manage Relationships’ pop-up and continue creating the PivotTable
    
7. Create your PivotTable with linked Tables, by completing the following steps:
   
1. Use the search bar to type the field name(s), or find the field(s) in the list you wish to report on; once you find the field, drag it into the appropriate Area (i.e. Filters, Columns, Rows, Values); for this example, we’re looking for ‘AIRS Problems Needs’ and ‘Gender’
   
2. Try placing your Fields in the following Areas; it’s likely this data isn’t very meaningful because it’s counting how many times each Gender was entered for any AIRS Need Category; see Image 3 below to see this example:
1. Columns: Gender
   
2. Rows: AIRSNeedCategory
   
3. Values: AIRSNeedCategory
   
3. Now try placing your Fields in the following Areas; notice the differences and similarities in how the data is displayed; this data is likely more meaningful as it’s counting how many times each Gender was selected for each specific AIRS Need Category; see Image 4 below to see this example:
   
1. Columns: Gender
   
2. Rows: AIRSNeedCategory
   
3. Values: AIRSNeedCategory