- Help center
- Dashboards and reporting
- Advanced controls
Creating custom pivot tables for FocalScope report
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
- When you are done exporting your FocalScope report to Excel, open the file and click on the [Raw Data] worksheet (highlighted in red)
- 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
- Raw report data can be either static (from an Excel file) or dynamic / live (directly from the FocalScope server)
- 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
- Continued in next section...
Figure 1 - Raw report data
Creating and populating a custom pivot table
- In the ribbon click [Insert] and then click [PivotTable > PivotTable]
- In the [Create PivotTable] popup, enter 'Raw Data'!RawData into the [Table/Range] field (highlighted in red)
- Click [OK] to create the pivot table
- Continued in next section...
Figure 2 - Creating a new pivot table
- 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
- 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
- Continued in next section...
Figure 3 - Populating the pivot table with data fields
Correctly calculating email totals
Please note, data 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:
- Right-click [Sum of ID] in the [Values] list (highlighted in red) and select [Value Field Settings]
- In the [Value Field Settings] popup, click the [Summarize Values By] tab
- From the list select [Count]
- 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:
- Right-click a pivot table / chart and select [PivotTable Options]
- In the [PivotTable Options] window, click the [Data] tab
- Tick the [Refresh data when opening the file] flag
- Click [OK] to save changes
Additional Information
- Things to know about pivot tables
- How to create new custom data fields in FocalScope reports
- When I open an Excel live report, I get an ‘Access denied’ error