How to use Excel as a report front end for FocalScope 'raw' report data
Friday, December 2, 2016 4:59 AM

There may be cases when it is desirable to load FocalScope raw report data into the Excel template file manually. One of the examples may be offline report demonstration, in which case one can save raw tabular data as a regular file in the local file system and display different Excel reports based on the saved offline copy of the raw data. This article details the process of using Excel as a report front end for raw report data exported from FocalScope.




Tutorial

Exporting 'raw' report data in FocalScope

  1. In the [Workspace], navigate to the [Shared Items > Shared reports] folder
  2. Double-click an existing report, or right-click in the right-hand pane and select [Add]
    1. If you are adding a new report, in the [Report form], set the template to one of the available options
  3. Click the [Download...] link right of the [Excel template] field (highlighted in orange), and save to a folder, e.g., C:\report
  4. Configure the report's filtering options and click the [Generate > Raw] button (highlighted in red) to generate the raw report data
  5. Save the generated raw data file into the same folder as the Excel template file in point 2 (C:\report) and name the file raw.xls



    Figure 1 - Generating a raw report



    Figure 2 - Excel template & raw report files

    Loading 'raw' report data into an Excel template file

    1. Open the Excel template file that was downloaded in the previous section (point 2) and select the [General] worksheet tab
    2. Replace the link in the cell next to [Data URL] with the full path to the raw.xls file. Please note: The full path to the file must be prefixed with file://. The path should then be: file://c:\report\raw.xls
    3. Enable external data sources by clicking the [Enable Content] button (indicated by arrow) in the warning bar. Please note: This button is labelled [Options] in Excel 2007
    4. All pivot tables and charts in the template should now automatically update with the data from the raw.xls file



      Figure 3 - Defining the data source for an Excel template file


      Please note: You can reuse the saved Excel template by replacing the raw.xls file with a freshly exported one and reopening the Excel template file.


      Additional Information