Reports

Configuration

Reports are Excel Workbooks in “.xlsx” file format containing “Input” style placeholders that are replaced with live values when report generation is triggered.

Select the “Configuration” node in the navigation panel and click on the “Reports” tab on the right to view “Reports” configuration options.

../_images/excel-config-reports.png

Check the “Remove Input style…” box to remove Fill and Border styles of the Input placeholders in generated reports.

Report files are stored into the default “Reports Folder”. On the local computer, you can browse to the folder by clicking the icon button on the right. To configure a different folder, ask your administrator to edit the Excel Connector service “appsettings.json” file. The file is usually located at:

C:\Program Files\Rensen\UA Office Link\Services\modules\Excel Connector\service\appsettings.json

For example, to save Excel reports to a network folder within subfolder “ExcelReportsFolder”, add a “ReportsFolder” entry to the file (note that the backslash character has to be entered twice to be recognized):

{
    {
    ...
    },
    "ReportsFolder": "\\\\HOSTNAME\\ExcelReportsFolder"
}

Note

Re-start the “Rensen UA Office Link Excel Connector Service” for changes to take effect. You will need to re-apply the settings if you re-install or update the Excel Connector.

Important

Ensure that the Excel Connector service account (“LOCAL SERVICE” by default) has write access to the configured reports folder. You may want to configure the service to log on as a specific user who has write access to the target folder and also to the Excel Connector data folder at “C:\ProgramData\Rensen\UA Office Link\modules\Excel Connector”. You will need to re-apply service account settings if you re-install or update the Excel Connector.

Prepare Excel Workbook

Before a report can be generated, an Excel Workbook needs to be prepared with placeholders indicating where live values are to be substituted. A placeholder is a cell that is formatted as “Input” containing a string type value that will become a live value tag name reference.

The workbook may contain placeholder cells in different locations, within the same sheet or in addtional sheets. For example, the following spreadsheet contains placeholders in cells B1, B2 and B3. Placeholder cells have the “Input” style applied and contain tag names “TankLevel1”, “TankLevel2” and “TankLevel3”.

../_images/excel-spreadsheet-inputs.png

The Excel Connector also supports live value substitution for one-dimensional arrays. A placeholder for an array value has the placeholder name in a single cell; this cell will receive the live value of the first element in the array. Cells in the following rows that are marked as “Input” but are otherwise emtpy, will receive subsequent array element values. The following example prepares a spreadsheet to receive 5 array values in cells B1 to B5; the live value is identified as “Tag1”.

../_images/excel-report-arrayvalue.png

Save the prepared workbook for import into the Excel connector.

Tip

To design a sheet that performs calculations on substituted live values or renders charts based on substituted live values, design the spreadsheet with some representative number values in placeholder positions first, then, as a last step, replace the number values with placeholder names again.

Add Report

To add reports, click the “Add” button with the Excel connector’s “Reports” node selected in the main application’s nagivation pane, then select the prepared workbook file in the dialog.

../_images/excel-add-report.png

This will open the Excel Workbook, retrieve placeholder names and make placeholders available as “tags” for processing in the UA Office Link task pipeline. For example, the tank level workbook prepared earlier will have the following tags.

../_images/excel-report-tags.png

Reassign Report

Use “Reassign” if the original workbook has been updated, for example if the design has changed or if placeholders are added or removed. If placeholders were added or removed, then tasks that use the report will be updated accordingly.

To reassign a report, select the “Reports” node in the main application’s navigation pane, select the report in the content panel, then click on the “Reassign” toolbar button and open the updated workbook file in the dialog. You can also right-click on the report and select “Reassign” from the popup menu.

../_images/excel-reassign-report.png

Remove Report

To remove a report, select the “Reports” node in the main application’s navigation pane, select the report in the content panel, then click on the “Remove” toolbar button. You can also right-click on the report and select “Remove” from the popup menu.

../_images/excel-remove-report.png

Warning

Note that reports are also removed from any task using the reports.

Example

The following task is configured to store tank levels into spreadsheets every hour. Please see the Tasks section to learn more about task configuration.

../_images/excel-report-task.png

The generated spreadsheet reports (found in the Reports Folder) contain the live values in placeholder positions:

../_images/excel-report-output.png

Tip

You can remove the input styling of placeholder cells in the Reports Configuration.