Structure of a template

A template is structured into four different areas demarcated by orange rows or columns. The screenshot below illustrates a standard template.

The four areas are called:

  1. Default settings, containing the report type, for example, standard report

  2. Header

  3. Data(Data area)

  4. Page Endor Footer

The code words (located in column 'A' in the standard report, and in row '1' in the ATV report and operational report) and the orange demarcation rows must not be deleted or modified.

Figure 1. Example of a standard template

The template areas

A brief description of the structure that applies to all report types is given below. The following applies to all areas:

Default settings

T he information on each of the data points is saved in the first area (Settings). This section is normally not visible in the finished report.

You must not add or delete rows in this area! Only experienced users should edit these rows if necessary when creating a template.

You can find more details in the Data analysis chapter.

Header and Footer areas

The Header and Footer areas can be customized using the MS Excel functions (but you cannot change the headers and footers of Excel itself).

If these rows are meant to appear on every sheet of a multi-sheet report, then this must be set under the menu option Page setup/Tablesheet/Rows to repeat.

See the following examples:

Data point columns

Simple Excel formulas are used to transfer suitable titles for the data point columns from the settings area (between Report type name and Header).

For example, in the Example of the standard template shown above , the formula "=C6" appears in the cell C21 so that the comment text " Dry weather pump 1" from row no.6, column C will appear in the heading row no. 21, column C.

Code words

Code words can be entered in the header area and the footer area in order to display statistical functions (e.g. in column 'A' of the Standard report ). The relevant values are then entered here for each data point. The following code words can be used (see _dp_fct):

Min, Max, MinTime, MaxTime, Number, Sum, Integral0, Integral1, SumNumber,
                    Average, Average1, Time0, Time1, Changes, Changes01, Changes10, StartValue,
                    EndValue and Difference

WinCC OA functions in Excel

Several code words are available to the user for labeling the finished report in the header and footer areas or in other areas outside the actual data area. You can choose to access the report information in all templates by German or English names. (erProtokollTyp = erReportType).

WinCC OA function in Excel english name Description of function
erProtokollTyp erReportType Name of the report type
erVon erFrom Start time of query
erBis erTo End time of query
erIntervall erInterval Time interval between the individual values
erIntervallEinheit erIntervalUnit Units for the time interval between the individual values
erPeriode erPeriod Period of time covered by the report query
erPeriodeEinheit erPeriodUnit Units for the period of time covered by the report query
erRequestType erRequestType

Name of the query type. There are three different types how a report can be created:

Man: manually created report,

Time: time controlled,

WinCC OA: triggered through

WinCC OA

erUserName erUserName The user name. Specifies who created a report.

These code words are used like functions in Excel (must be called from a cell using, for example, =erFrom). One thing to ensure here is that the corresponding cells also have the correct display format, as shown in the following example:

Example

For this formula: Cell formatting required: Format _ Cells _ Numbers
Formula erFrom Date _ '4.7.97 13:30'

Data area (Data)

The Data area can be defined using one or more rows which will contain the individual values for the data points over time. These rows are used cyclically in the report layout. For example, if the parameter setter creates a template in which one white row, one green row and one yellow row are displayed in its three-row data area, then in the finished report this color sequence will be repeated cyclically as many times as is necessary to display all the rows of values.

In the template for a daily report containing hourly values, 24 rows do not need to be allocated in the data area. WinCC OA Excel Report automatically fits in the necessary number of rows when the report is created.

You customize the formatting. Formulas can be inserted in columns where no data point has been defined. Mixed queries across several systems can be compiled in one joint report.

Reports are protected with Sheet protection by default. This means that subsequent changes to individual cells cannot be made. If you want to exclude individual cells from this protection, then disable the Locked option via Home/Format/Format cells/Protection. This might be useful for instance in comment columns.