PLC Reports User Manual

12. Variables

In section “Variables” all values to read from any PLC for reporting purposes are defined. Variables are defined – like all other definitions – in the Excel report template, also called the “report definition file”. The worksheet for variable definitions has the fix name “Variables”.

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

Variable 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.

12.1. Walk-Through: How to Edit Variable Definitions

  1. Open a report file for editing.
  2. Select the worksheet “Variables” in the Excel report template.
  3. Enter or change the desired variable definitions in Excel.
  4. Navigate to section “4. Variables” in PLC Reports.
  5. Click the button “Load and Check Settings”. Load and Check Settings
  6. PLC Reports now reads and validates all variables defined in the worksheet “Variables”.
  7. Valid variables will be shown in the table “Variables”.
  8. If errors occur while loading or validating, corresponding messages will be shown in the message list.
  9. Error-free variable definitions are marked with the icon Icon OK in column “Def”.
  10. Error-prone variable 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 by using the corresponding commands in the Excel file menu.

Variables

12.2. Columns of the Worksheet „Variables“

The worksheet “Variables” contains the following columns:

12.2.1. Column A: Index

This column is used for defining a unique identification number (index) for each variable. The index is used e.g. in worksheet “Archives” to archive a variable value.

WarningNote!
Records will only be interpreted if column “Index” contains a valid numeric value.  If the index cell is blank, all entries in the following columns of the same row will not be considered. This way, any comments can be added for documentation or description purposes. Empty rows will also not be considered.

12.2.2. Column B: Name

This column is used to define a clear text name for the variable. The name has no function except for the system variables and is used only for clarity. When defining the system variables, this column defines the name of the desired system variable. Further details about system variables can be found in the chapter “System Variables”.

12.2.3. Column C: Type

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

If a variable is an array of values of the same type, the following syntax is used:

Datatype[Length]

Example:
To define an array of 100 real values, the definition in column type is:

Real[100]

12.2.4. Column D: PLC Index

This column is used to assign a variable to a PLC. Valid values are existing indexes of PLCs defined in worksheet “PLCs”.

12.2.5. Column E: Area

This column is used to select the memory area to read the variable value from. Valid values are:
-          DB (Data Block)
-          M (Flag)

12.2.6. Column F: DB

This column defines the number of the data block to read the variable from. If data is read from other memory areas, the value in this column is ignored. In this case, this column should be left blank.

12.2.7. Column G: Byte

This column defines the byte address to read the variable from.

12.2.8. Column H: Bit

This column defines the bit address to read the variable from. It is only relevant to the values of type “Bool”. For any other data type the bit value needs to be set to 0.

12.2.9. Column I: Named Address

This column defines the symbolic name of the variable and will be used in the future for symbolic data access (e.g. from OPC servers, S7-1500, Rockwell). In Version 2013, entries in this column have no function.

12.2.10. Column J: Multiplier

Many PLC programmers do not use the data type real for performance reasons. They use integer values with a multiplier instead. For instance, the real value “47.11” is defined as an integer value “4,711” in the PLC. If the value is displayed on an HMI, a multiplier of 0.01 is being used. To read and scale such values correctly, PLC Reports provides the possibility to define a multiplier for each variable.

Valid values are positive and negative integer or floating point values. If the column is blank, the multiplier 1 is used.

12.2.11. Column K: Format

This column defines the format of the value shown in a report. 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.

12.2.11.1. Formatting Values by PLC Reports of 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.

12.2.11.2. Possible Formatting Values

Example formatting of the value 1,000,000:

12.2.11.2.1. 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
12.2.11.2.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
12.2.11.2.3. 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
12.2.11.2.4. Date and Time Values
Type Format Value Result
Short Date Format {0:d} 12/30/2007
Long Date Format {0:D} Saturday, December 22, 2007
Short Time Format {0:t} 2:20 PM
Long Time Format {0:T} 2:20:04 PM
Date & Time Complete (short) {0:f} Saturday, December 22, 2007 2:20 PM
Date & Time Complete (long) {0:F} Saturday, December 22, 2007 2:20:04 PM
Standard Date (short) {0:g} 12/30/2007 2:20 PM
Standard Date (long) {0:G} 12/22/2007 2:20:04 PM
Date {0:M} December 22
RFC1123 Date Format {0:r} Sat, 22 Dec 2007 14:20:04 GMT
Sortable Date Format {0:s} 2007-12-22T14:20:04
Universally Sortable Date Format {0:u} 2007-12-22 14:20:04Z
Universally Sortable GMT-Date Format {0:U} Saturday, December 22, 2007 1:20:04 PM
Year/Month Pattern {0:Y} December, 2007
12.2.11.2.5. Date Values Individually Formatted
Type Format Value Result
Day {0:dd} 22
Day Name (short) {0:ddd} Sat
Day Name (long) {0:dddd} Saturday
Era {0:gg} AD
Hour double-digit {0:hh} 02
Hour double-digit (24 hours) {0:HH} 14
Minute {0:mm} 20
Month {0:MM} 12
Month Name (short) {0:MMM} Dec
Month Name (long) {0:MMMM} December
Second {0:ss} 4
AM or PM (English only) {0:tt} AM
Year 2-digits {0:yy} 07
Year 4-digits {0:yyyy} 2007
Time Zone (short) {0:zz} 1
Time Zone (long) {0:zzz} +01:00

12.2.12. Column L: Min

This column defines the minimum variable value. If the online value falls below the minimum limit, the min value will be used as a substitute value.

12.2.13. Column M: Max

This column defines the maximum variable value. If the online value exceeds the maximum limit, the max value will be used as a substitute value.

12.2.14. Column N: Simulation

The simulation mode of PLC Reports allows defining and testing reports without having access to the destination PLC. The simulation mode can be activated in section “Global Settings”. If the simulation mode is active, all variables having a valid simulation definition will take their simulation values instead of reading the values from the PLC.

The simulation values can be sinus curves, random values or saw-tooth curves. The start values, end values and update cycles are adjustable. If a sinus curve is used, the change value per cycle is 1/60 of the full curve.

The simulation is set up by defining 5 values in column simulation. The values are separated by slashes:

  1. Start value
  2. End value
  3. Increment number per cycle or “Random”
  4. Cycle: x Seconds, x Minutes, x Hours or x Days
  5. Reset cycle: x Seconds, x Minutes, x Hours, x Days, x Weeks, x Month, x Years or “None”

Optionally the simulation can be set to “Default”. In this case, the following default values are used:

  1. Start value: Lowest possible value of the data type
  2. End value: Highest possible value of the data type
  3. Increment per cycle: 1
  4. Cycle: 1 Second
  5. Reset cycle: None

Example 1:
The simulation value is a sinus curve with values between 0 and 50. The actual value is changing every second.

In this case, the simulation setting is: 0 / 50 / Sinus / 1 Sec / None

Example 2:
The simulation value is increased once per minute by 0.5. The start value is 100. After 10 hours the value is reset and restarts at 100.

In this case, the simulation setting is: 100 / 10,000 / 0.5 / 1 Min / 10 Hours

12.2.15. Column O: Online Value

This column is reserved for future extensions and needs to be blank.

12.2.16. Column P: 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 °C), it will be added to the online value when writing values into the report. In this case, the value will be written as text.

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

12.2.17. Column Q: Report Sheet

This column defines the Excel sheet into which the value of the variable is to be written. This setting is only used if online values are to be directly written into the report, without writing them into archives first. If variable values are stored into archives first, the column must be left blank.

If a variable value is to be used in multiple sheets, it has to be defined multiple times. For each destination the variable has to be defined once.

WarningNote!
Even if the same variable is defined multiple times because it needs to be written into multiple sheets, it is still read from PLC only once.

12.2.18. Column R: Report Cell

This column defines the Excel cell into which the value of the variable is to be written. This setting is only used if online values are to be directly written into the report, without writing them into archives first. If variable values are stored into archives first, the column must be left blank.

If a variable value is to be used in multiple cells, it has to be defined multiple times. For each destination the variable has to be defined once.

WarningNote!
Even if the same variable is defined multiple times because it needs to be written into multiple cells it is still read from PLC only once.

1.2.19. Column S: Direction

This column defines in which direction values are written if more than one value is being transferred. This is the case if either the same value is written multiple times or the value is an array type.

Valid values are:
-          Down
-          Right

Example:
In column “Report Cell” the write destination of a variable value is set to cell A1. Column “Direction” has the value “Down”. When writing the variable for the first time, the value will be written into cell A1. When writing the variable value again, it will be written into cell A2, the next time into cell A3 and so on.

12.2.20. Column T: 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”.

12.2.21. Column U: Timestamp Mode

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

This setting is only used if online values are directly written into the report, without writing them into archives first. If variable values are stored into archives first, the column must be left blank.

PLC Reports provides timestamp modes, which allow displaying any variable value along with a special timestamp.

Valid values are:
- Report Start (Trigger time of the report)
- Timestamp (The moment the variable is read from the PLC)
- Today Begin (Actual day, 0:00:00 AM)
- Previous Day Begin (Previous day, 0:00:00 AM)
- Previous Day End (Previous day, 11:59:59 PM)
- This Hour Begin (Beginning of the actual hour (HH:00:00 AM/PM)
- Previous Hour Begin (Beginning of the previous hour (HH-1:00:00 AM/PM)
- Previous Hour End (End of the previous hour (HH-1:59:59 AM/PM)
- This Minute Begin (Beginning of the actual minute (HH:MM:00 AM/PM)
- Previous Minute Begin (Beginning of the previous minute (HH:MM-1:00 AM/PM)
- Previous Minute End (End of the previous minute (HH:MM-1:59 AM/PM)
- No Timestamp

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

Example:
Daily counter values are computed by a PLC and provided for reading. Writing these values into daily reports only makes sense after the day has passed. When a new day has started, the task of the reporting system is to create a report with the previous day values. In this case, the counter value, provided by the PLC is read and written into the report together with the timestamp “Previous Day End”.

WarningNote!
For most applications using archives to store and report historical values is recommended. In this case, the timestamp settings have no meaning.

WarningNote!
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”.

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

Format Cells

12.2.22. Column V: Read Counter

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

12.2.23. Column W: Remarks

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

12.3. System Variables

System variables are available for special purposes. They are defined like normal variables, but their data type is always “System”. The name defines the system variable used. When defining a system variable, the addressing columns have to be kept blank.

WarningNote!
To display a combined date and time it can be necessary to use two separate cells, one for the date and one for the time or to define a custom date-time format.

WarningNote!
Find details about date or time value formatting in chapter “Format”.

12.3.1. System Variable “Start Time”

This system variable contains the trigger time of the actual report generation. It contains both date and time information and can be used, for example, to display a timestamp in the header section of the report.

12.3.2. System Variable “Date Time”

This system variable contains the actual date and time the variable is being read.

12.3.3. System Variable “Today Begin”

This system variable contains the date and the time of the actual day at 0:00:00 AM.

12.3.4. System Variable “Previous Day Begin”

This system variable contains the date and the time of the previous day at 0:00:00 AM.

12.3.5. System Variable “Previous Day End”

This system variable contains the date and the time of the previous day at 11:59:59 PM.

12.3.6. System Variable “This Hour Begin”

This system variable contains the date and the time of the actual hour at HH:00:00 AM/PM.

12.3.7. System Variable “Previous Hour Begin”

This system variable contains the date and the time of the previous hour at HH-1:00:00 AM/PM.

12.3.8. System Variable “Previous Hour End”

This system variable contains the date and the time of the previous hour at HH-1:59:59 AM/PM.

12.3.9. System Variable “This Minute Begin”

This system variable contains the date and the time of the actual minute at HH:MM:00 AM/PM.

12.3.10. System Variable “Previous Minute Begin”

This system variable contains the date and the time of the previous minute at HH:MM-1:00 AM/PM.

12.3.11. System Variable “Previous Minute End”

This system variable contains the date and the time of the previous minute at HH:MM-1:59 AM/PM.

12.4. Testing Reading of a Variable from a PLC

After the variable definitions have been successfully read and validated, the list “Variables” shows all defined variables. Like in Excel, each row shows one variable.

Reading variables from the PLC can be tested by clicking “Test” in the corresponding row.

Variables

12.4.1. Walk-Through: How to Test Reading of a Variable from a PLC

  1. Make sure that at least one PLC is defined correctly and is shown in the table in section “1. PLCs”. Further details can be found in section “PLCs”.
  2. Check the communication to the PLCs. Find details in section “PLCs”.
  3.  Make sure that at least one variable is defined correctly and is shown in the table “Variables”.
  4. Connect the Ethernet interface of your computer with the network to which the PLC is connected.
  5. Check the network card settings of your computer and make sure that a valid IP address is assigned to it.
  6. Click “Test” of the desired variable definition.
  7. If reading the variable was successful, the first 100 read values will be displayed:
    Read Variable OK
  8. If the variable could not be read from the PLC, the following error message will appear:
    Read Variable Faulty
  9. In addition, each error will be listed in section “Messages”:
    PLC Communication Error Message
  10. After checking the communication the result will be indicated by an icon in column “Com”:

Icon QuestionMark YellowReading the variable has not been checked yet.

Icon OKReading the variable has been checked and is error free.

WarningError while reading the variable from the PLC.

WarningWarning!
Validate and test all variables carefully before using a report definition in a production environment.