Creating templates with WinCC OA Excel Report

The templates are used to specify the data points for inclusion in the report, and the layout of the report. Balance sheets, in the sense of calculations using Excel formulas, can also be integrated in a template, as can trend diagrams, graphs and charts for representing the values. A simple example template is shown under "Template structure".

The templates are created using the New template button and edited using Open template. Alternatively you can use the corresponding options in the Template menu.

To create a new template, the Master template for the selected report type (standard, operational, ATV, costs center or SQL report) must be present.

Only templates located in the "Template" directory can be updated!

The following functions are available for creating a template:

  • Define a report time range

 Selecting Report type and time range

  • Link to a cell in another file

A cell of a file can be displayed in another file. Use the = sign in the file where the another cell should be shown. Open the file via "Switch windows"

Figure: Excel report and Option "Switch Windows"

Select the cell and press the enter key. The cell content is shown in the selected cell. You can also use the reference: [fileName]Table1!$A$3

  • Configure a template

Configuring a report type or editing a report type

  • Add sheet

The different templates of WinCC OA report can be added as an Excel worksheet to an opened template (3.2 upwards).

  • Delete sheet

When a sheet is deleted using the menu option Template/Delete sheet, the active worksheet is removed from the workbook.

This process cannot be undone!

  • Add data point

One data point can be inserted in each column (or row). The individual values of the data point are entered in the rows (one cell per value).

  • Edit data point

A data point can be edited in the same way as adding a data point. First of all select the column (or row) of the data point concerned. Then open the selection window using the menu option Template/Edit DP. The data point in question is then displayed with the selected settings, where changes can easily be made.

  • Enter formulas

The formulas available in Excel can be entered in those cells not required for displaying archive values. Such formulas include complex calculations and detailed statistical analyses. See Statistical functions and also Code words in the header and footer demarcation rows.

  • Save a template

Templates are saved in the directory <proj_path>/data/xls_report/Template as Excel document templates (.xltm). The format .xltm is used by default but you can also use the .xlt format.

In Excel 2007 the templates are saved by default with the extension *.xlsx. Before saving a template you have to change the file type to *.xlt.

You can choose any file name as long as it does not end with a number.

  • Layout control in headers and footers

Functions that are executed at runtime during database access (dynamic functions) are summarized in:

Code words for headers and footers