PLC Reports User Manual

13. Archives

For many report types, besides variable values read at the time the report is being created, historical values are required. For instance, a monthly consumption report shows the consumption values of each day of the month. In this example, the daily consumption values are first written into an archive and at the end of the month read from the archive and written into the monthly report.

Archived values are stored into database files on the hard drive. Each value is stored with a corresponding quality code and timestamp. In addition, the database files are protected against falsification by checksums, the so-called “Hash-Codes”.

By means of various calculation functions, it is also possible to compress archived values on-the-fly while writing them into the archive. For example, an hourly average value is calculated by 600 values read every 10 seconds from the PLC. In this case, the archive would contain only one value per hour – namely the calculated average value.

In section “Archives” for each archived value, one archive is defined. Like all definitions, archives are defined in the Excel report definition files. The worksheet for archive definitions is named “Archives”.

To edit and validate archive definitions, a report template must be opened first by clicking “Open Report File” in section “2. Report File”.

Archive definitions can be entered starting from row 6. Row 5 is reserved for the column headers. The header area (row 1-4) is not being interpreted.

13.1. Walk-Through: How to Edit Archive Definitions

  1. Open a report file for editing.
  2. Select the worksheet “Archives” in the Excel report template.
  3. Enter or change the desired archive definitions in Excel.
  4. Navigate to section “5. Archives” in PLC Reports.
  5. Click the button “Load and Check Archives”.
  6. PLC Reports now reads and validates all archives defined in worksheet “Archives”.
  7. Valid archives will be shown in table “Archives”.
  8. If errors occur while loading or validating, corresponding messages will be shown in the message list.
  9. Error-free archive definitions are marked with the icon Icon OK in column “Def”.
  10. Error-prone archive definitions are marked with the icon Warning in column “Def”.
  11. By double clicking an error message, you can directly jump to the error-prone cell in the Excel sheet.
  12. After editing the report template, save and close the workbook using the corresponding commands in the Excel file menu.


13.2. Columns of the Worksheet „Archives“

The worksheet “Archives” contains the following columns:

13.2.1. Column A: Name

This column is used to define a system-wide unique name for the archive. In contrast to variables, archives are identified by a name, not by an index. The reason for this is that values from archives can be read from any report, whereas variables can only be read from archives containing their definition.  However, writing values into archives is only possible within the report containing the archive definition.

For monitoring energy consumption, a weekly, monthly and annual report is created. All three reports show daily values, but the different reports show the values over different periods of time. The weekly report shows 7, the monthly report shows 28-31, the annual report shows 365/366 daily values originated from the same archive.

To read values from archives defined in other report definitions further details can be found in column G “Archive Type”.

In section “Global Settings” the “Archives Path” has been defined. For each defined archive one file will be created in this path. Therefore, archive names must be unique. The same rules for file naming in Windows apply for naming archives.

Use as short as possible but as precise as necessary archive names. This is important especially when working with archives in section “Instructions”.

1.2.2.              Column B: Data Type

This column defines the archives data type. Valid types are:
- Bool
- Byte
- Word
- Int16
- UInt16
- Int32
- UInt32
- Real

13.2.3. Column C: Format

This column defines the format of the value shown in a report and works in the same way as the variable formatting. For example, the number of decimal places of a real-type value can be defined. If the column is blank, PLC Reports does not format the value.

Archived variables are always converted into a decimal numbers. Formatting has no influence on the archive value. The desired format needs to be defined in column “Format” of the archive definition. If the online value of the variable is used parallel to the archiving it, formatting of the variable can be used if the result of the formatting can be converted into a decimal number.

The variable columns “Min”, “Max” and “Multiplier” can be used without limitations when archiving values. Formatting Values by PLC Reports or by Excel

The basic decision whether PLC Reports or Excel should format the values must be made when configuring a report. In general the recommendation is to let Excel format values wherever possible. If PLC Reports is used to format values, all Excel destination cells should be formatted as text. Note that in this case no Excel-based calculations or graphical representations in the form of curves or diagrams can be made in Excel. Possible Formatting Values

Example formatting of the value 1,000,000: Data Type Bool
Type Format Value Result
Yes / No 1=Yes, 0=No Yes / No
True / False 1=True, 0=False True / False
Only Yes 1=Yes Yes
Only No 0=No No
Free 1=My Text 1, 0=My Text 2 My Text 1 / My Text 2 Numbers
Type Format Value Result
Currency {0:c} 1,000,000.00€
Decimal Number {0:d} 1,000,000
Scientific {0:e} 10e+6
Fixed-Point Number {0:f} 1,000,000.0
Generic {0:g} 1,000,000
Thousand Separator {0:n} 1,000,000%
Hexadecimal {0:x4} f4240 Formatting Values Individually
Type Format Value Result
0 Placeholder {0:00.0000} 1,000,000.0000
Number Placeholder {0:(#).##} (1,000,000)
Decimal Point {0:0.0} 1,000,000.0
Thousand Separator {0:0,0} 1,000,000
Integer Multiplier of 1,000 {0:0,.} 1,000
Percent Value {0:0%} 1,000,000%
Exponent Placeholder {0:00e+0} 10e+5

13.2.4. Column D: Unit

In addition to column “Format”, the column “Unit” formats the value and defines its unit. Mind the notes regarding formatting of values in column “Format”.

If a unit is set (for instance to °F), it will be added to the online value when writing values into the report. In this case, the value will be written as text.

If values are to be shown in charts, or Excel based calculations are to be used, the formatting of values must be done in Excel. In this case, the column “Unit” must be left blank.

13.2.5. Column E: Cycle

This column defines the archives storage cycle. According to the value in this column, the archive database provides one memory cell for each storage cycle.

It is possible to write to the same memory cell multiple times. This especially makes sense if min, max, average values or summations are calculated on the fly.

A report shows one average temperature value per hour. For this purpose, an archive with cycle “1 hour” is defined. Thus, the archive database provides one memory cell per hour for archiving the average temperature values.

However, to calculate the average temperature the actual measured temperature is read from the PLC every 10 seconds and the average value is recalculated and stored into the same hourly memory cell of the archive.

In this example, every 10 seconds, or 360 times per hour, the variable is read from the PLC, the average value is updated and subsequently the result is stored into the archive. After one hour, exactly one value, namely the average value, calculated by 360 single measurement values, is stored in the archive.

The advantage of this method is the low memory consumption of the archives database files. This way the archives are compressed to the desired size on the fly.

This is equally important in view of the final report file. In most cases, it makes no sense to transfer multiple thousands of values into Excel. If the archive settings are made cleverly, meaningful reports can be created with a minimum source data. This increases the performance and limits the file size of the archive databases and the final reports.

Valid values are:
- x Second(s)
- x Minute(s)
- x Hour(s)

Only full seconds, minutes or hours are allowed.

1.2.6. Column F: Archive Size

This column defines the storage period of the archive. The size is defined in full minutes, hours, days or years. A database file with the corresponding size is created on the hard drive depending on the setting “Cycle” and “Archive Size”.

After loading and validating the archive definitions, table “Archives” shows the size of the database file in column “Memory Size”. In addition, the number of storable values is shown in column “Memory Cells”.

The archive size and the cycle are basic settings which need to be adjusted carefully. Changing an archive which already exists is only possible by re-creating the archive database file.

1.2.7. Column G: Archive Type

This column defines if the archive is of a circular or reference type.

Valid values are:
- Circular
- Reference Circular Archives

Default archives are always circular. With this archive type reading from and writing to the archive is possible within the report the archive is defined. The archive is filled with data over the desired period of time. If values are stored over a longer period of time than defined in the archive settings, the oldest values are overwritten. By this, the archive always provides data over the selected period. Reference Archives

Reference archives are meant to provide read-access to data from archives defined in other report templates. It is not possible to write into this type of archives. The name and the data type of the reference archive definition must match the definition in the main report. The format and the unit can be different. Settings for cycle and archive size, which will be taken from the main archive definition, must be left blank.

13.2.8. Column H: Report Sheet

This column defines the Excel sheet into which the archive value is to be written. A valid value is e.g. “Report”, provided that a worksheet named “Report” exists.

13.2.9. Column I: Report Cell

This column defines the Excel cell into which the value of the archive is to be written. A valid value is e.g. the cell name “A1”.

13.2.10. Column J: Direction

In most cases multiple values are provided when reading values from an archive. The column “Direction” defines where the following values are written to.

Valid values are:
- Down
- Right

In the column “Report Cell” the write-destination of an archive value is set to cell B1. The column “Direction” has the value “Down”. When reading values from the archive, the first value will be written into cell A1, whereas the following values will be written into cells A2, A3, A4 and so on.

13.2.11. Column K: Timestamp Cell

This column defines the destination cell into which the timestamp of the variable value is written.

Valid values are cell names, e.g. “A1”.

13.2.12. Column L: Timestamp Mode

This column defines the timestamp type to be used when archive values are written into the report.

Valid values are:
- Timestamp (The moment the value was originally read from the PLC)
- No Timestamp

If the column is left blank, no timestamp will be written into the report.

To display timestamps in the desired format, the destination Excel cells must be formatted correctly. By default, Excel does not provide a format for displaying timestamps showing full details about both date and time in the same cell. To get a fully-detailed display, a custom format needs to be used. Right click the cell to format and select “Format Cells” – “Custom”. Enter the desired format into the field “Type”.

mm/dd/yyyy h:mm:ss AM/PM will be displayed as 12/22/2007 2:20:04 PM

Format Cells

13.2.13. Column M: Substitute Value

This column defines which substitute value should be used:
- If no valid online value is available when writing into the archive, for example, if the connection to the PLC is interrupted.
- If the archived data has gaps. Gaps can occur if the archive cycle does not match the writing of data to the archive. For example, if the computer was restarted for maintenance, no data will be written while restarting.
- If data is requested from the archive which is not present in the archives database.

Valid values are:
- Blank
- A numeric value*
- Last Value**

* Depending on the archive data type
** Last valid archive value

If the column “Substitute Value” is blank, the value 0 will be used.
Example value for an archive with data type real: -0.1

For archives with data type “Bool”, the substitute value must be either “0” or “1”. However, the output of the substitute values will be formatted according to the format settings.

13.2.14. Column N: Read Counter

This column is internally used by PLC Reports and always has to have the value 0 in the archive definition. PLC Reports uses this cell to store how many times the same value has been written to the report, and then calculates the actual destination cell depending on the setting “Direction”.

13.2.15. Column O: Remarks

This column is used for documentation purposes and can contain any comment.

13.3. Archive Reorganization Notes

If PLC Reports was stopped for a long period of time, the archive databases must be reorganized when restarting the system. This can take a few seconds or even minutes. If fast logging cycles are used, some values will not be logged and will be replaced by the corresponding substitute value while the reorganization is in process.