Creating custom pivot tables for FocalScope report
Friday, December 2, 2016 4:24 AM

FocalScope's raw report data is the foundation on which you can build highly customized reports that represent the sections of data most relevant to your organization, in the way that is most meaningful to you. This article details the process of creating custom pivot tables and charts and how to populate them with raw data from FocalScope reports.

Prerequisites

Tutorial

Identifying the raw data in FocalScope reports

  1. When you are done exporting your FocalScope report to Excel, open the file and click on the [Raw Data] worksheet (highlighted in red)
  2. The raw report data contained in each FocalScope report varies according to the type of report generated and the filtering that was set for the report
  3. Raw report data can be either static (from an Excel file) or dynamic / live (directly from the FocalScope server)
  4. Click the [Insert Worksheet] tab (highlighted in blue) to create a new worksheet and calll it Table. On this worksheet we will create a new pivot table
  5. Continued in next section...



Figure 1 - Raw report data


Creating and populating a custom pivot table

  1. In the ribbon click [Insert] and then click [PivotTable > PivotTable]
  2. In the [Create PivotTable] popup, enter 'Raw Data'!RawData into the [Table/Range] field (highlighted in red)
  3. Click [OK] to create the pivot table
  4. Continued in next section...



    Figure 2 - Creating a new pivot table



    1. With the blank pivot table created, you can now drag and drop data fields from the [Choose fields to add to report] list to the [Report Filter], [Column Labels], [Row Labels], and [Values] list of the report
    2. Depending on which section you drag the data fields to, you will get a different arrangement of the data in the pivot table, so feel free to experiment and use the various Excel options for pivot tables to fine-tune the report to match your needs
    3. Continued in next section...


      Figure 3 - Populating the pivot table with data fields


      Correctly calculating email totals

      Please notedata fields such as ID (the internal ID of emails) are in numeric format, so this needs to be considered when email totals are calculated from this data field. By default, if the ID data field is dragged to the [Values] list, the values will be the sum of the IDs. This will generate incorrect totals that are far higher than the actual amount of emails in the system. The ID data field's value must be set as the count of the IDs to correct this issue. To correct this issue, please do the following:

      1. Right-click [Sum of ID] in the [Values] list (highlighted in red) and select [Value Field Settings]
      2. In the [Value Field Settings] popup, click the [Summarize Values By] tab
      3. From the list select [Count]
      4. Click [OK] to save changes


      Figure 4 - Tweaking value field settings



      Figure 5 shows a perfectly functioning report with the corrected email totals.



      Figure 5 - A basic but functional  custom pivot table

      Useful Tip

      If you want pivot tables / charts to automatically refresh when the report is opened, do the following:

      1. Right-click a pivot table / chart and select [PivotTable Options]
      2. In the [PivotTable Options] window, click the [Data] tab
      3. Tick the [Refresh data when opening the file] flag
      4. Click [OK] to save changes




      Figure 6 - Setting a pivot table / chart to autorefresh

      Additional Information