Wiping data from custom reports before uploading them to FocalScope
Friday, December 2, 2016 5:11 AM

After you've configured a custom report complete with custom data fields and custom pivot tables / charts, it is time to save the report and upload it to FocalScope. It is important to note that reports must wiped of residual data to ensure proper functionality once they are uploaded to the FocalScope Report Center. This article details the methods used to wipe custom reports--as well as how to retain any custom formatting--before uploading them to FocalScope.

Be sure to download and extract sample.zip file attached to this article. The sample.html file inside is required by method 1 of wiping custom reports.

Prerequisites

Tutorial

Configuring pivot tables / charts to not retain data

Because FocalScope reports rely on a live data source, which is constantly updated. Saving outdated data in the Excel file will prevent updated data from properly propagating through the report. Pivot tables / charts need to be configured to wipe data contained in them, else the method 1 wiping procedure can have unpredictable results.


  1. Right-click a pivot table (for pivot chart, select its source pivot table) and select [PivotTable Options]
  2. In the [PivotTable Options] pop up, click the [Data] tab
  3. Untick the [Save source data with file] flag (highlighted in red)
  4. Set the [Number of items to retain per field] value to [None] (highlighted in blue)
  5. Click [OK] to save changes



    Figure 1 - Configuring pivot tables to not retain data

    Method 1: Wiping custom reports and retaining the state of pivot tables / charts

    This is the recommended method of wiping custom reports as it will retain any custom filters and formatting options used in pivot tables / charts.

    1. Extract the attached sample.zip file to the following path c:\reports\
    2. Under the [General] worksheet of the custom report, totally replace the URL in the [Data URL] field with file://c:\reports\sample.html (highlighted in red)
    3. Save the report



    Figure 2 - Loading artificial source data


    The report will have a small amount of artificial data to allow the retention of all custom filters and formatting the report may have.



    Figure 3 - Artificial data in a report (to retain custom filters and formatting)



    Method 2: Completely wiping out a report

    Warning, this method is only recommended if your custom report uses pivot tables / charts that don't have any custom filters and formatting (only use this option if you are an advanced Excel user and sure of what you are doing).


    1. Under the [General] worksheet of the custom report, add &blank=1 to the end of the URL in the [Data URL] field (highlighted in red)
    2. Save the report



      Figure 4 - Loading blank source data


      The report will be wiped clean, and all custom pivot table / chart filters and formatting will be wiped out, as well.



      Figure 5 - A wiped report (all custom filters and formatting is lost)

      Additional Information



      Attachments
      sample.zip