×
Menu
Index
Search
 
   
   
   
 
  • Excel Reporting Configuration Steps
  (c) Automation Consultancy Limited, 2021

Excel Reporting Configuration Steps

 
Excel Reporting Configuration File
Once installed the Excel reporting configuration file can be found in the installation directory normally by default;
"C:\Program Files (x86) \ACL\Apps\PlcData.LoggingService\Files"
 
 
 
Overview
 This guide is best consumed in conjunction with the Excel Reporting Configuration file. This section is just a bird’s eye view of the configuration. Details are provided in subsequent sections.
 
Although there are similarities PlcData is two apps in one; A logging and reporting app. they are totally independent applications linked only by data parameters i.e., the controllers and tag configurations must be the same.
 
The generated reports are of CSV type that can be opened by Excel and several other third-party software. 
 
Note: Controller and Plc are used interchangeably    to refer to the programmable logic controller
 
The controller configuration structure: Controller -> Reports -> Sections -> Tags
Configurations are contained in Excel tables spread over worksheets.
 
Note 2: The excel spreadsheet contains macros and needs to have the extension ‘.xlsm’.
 
 
1.     Controllers: This is where the PLCs or controllers’ parameters are configured.
2.     Controller Reports: Each Controllers can trigger reporting on demand for instance at the end of a Mixer (or a process) cycle. A controller can have one or more reports. Report parameters are configured in this worksheet. Reports can also be triggered on a day of the week and/or a time of day.
3.     Report Sections: A report is organised into sections for ease of navigation.
4.     Section Tags: Each section has a block of tags associated with it. These are the tags that will reported upon when the report is triggered by the PLC and/or day/Time trigger. The tags associated with a section are configured in this worksheet. A tag can be associated with one or more sections.
5.     Controller Type: This worksheet is protected and should not be changed. It contains information on the supported PLCs and their supported datatypes.
 
Controllers
PlcData Reporting is capable of handling one or more PLCs of different types. The supported types are detailed in the controller Type Worksheet.
 
 
1.     Ip Address: This is the Ip Address of the PLC on the network. The PC where PlcData is installed needs to be part of the network.
2.     Controller Type: This is the controller type i.e., Siemens S7, Allen Bradley SLC or a Rockwell’s Logix series. Only supported PLC types can be selected (See controller type worksheet for detail).
3.     Rack: Rack where the PLC resides.
4.     Slot: A rack usually has many slots this the slot where the PLC is housed (plugged in).
5.     Port: The controller port number. This is normally fixed by the manufacturer and should not need to be changed from the figures shown in the controller type worksheet.
6.     Timeout: This the connection time out in milliseconds. If PlcData fails to connect with the PLC within the timeout time it will log an error in the event log and the log file.
7.     Expand Table: This doubles the size of the configuration table should more table rows be required for configuration.
8.     Shrink Table: This deletes all fully blank rows.
 
Controller Reports
Each controller has one or more reports. Each report has several sections associated with it. When the PLC requests a report, the associated section tags are retrieved from the database.
There are two possible modes for generating the CSV reports: Day of the week and time trigger and/or Plc trigger.
The modes work hand in hand together and they are mutually exclusive and independent of each other.
 
NOTE: The generated CSV file headers are derived from the tags description, so they are mandatory 
 
From now on we will be dealing with Tags or PLC tags to be more precise. The tags’ structure is the same for all supported PLC types as follows.
 
·     Friendly name: This is usually a symbol or a name for the tag i.e., ‘Mixer-Start’, ‘Flour-Weight’ etc... (this is optional)
·     Description: The is a short description to clarify the information the tag holds or what the tag does in some instances. (this is mandatory for reporting)
·     Plc Address: This is the Tag address in the PLC i.e., for Siemens PLC ‘DB2.4’ (this is usually mandatory)
·     Plc Data Type: This is usually the supported data type for a PLC type (See controller type worksheet for details).
·     Units: This refers to Engineering units i.e.  Degree Celsius (Optional)
 
 
 
Settings
1.      Controller Ip: A controller can have one or more reports. This is the Ip Address of the PLC for which reports are configured. Only configured controllers can be selected.
2.     Report Name: This identifies the report i.e., ‘Mixing Report’. Several PLCs can have same report name. The report name is scoped to a PLC.
3.     Enable Creation: This enables or disables report creation. If a report is disabled, NO report generation occurs even if the PLC or Day/Time trigger request it.
4.     Directory Path: This is the path where the reports files are stored. Different reports can have different storage path.
 
Trigger
5.     Day of The Week: This is a day of the week trigger for report generation this works in conjunction of the Time configuration. This I usually used for weekly reports.
6.     Time: This the time of day when the report is generated. If the Day of the Week is blank this will generate reports daily.
7.     Number of Previous Days: This is the number of days’ worth of data to include in the report i.e., for a weekly report it could be 7 days
8.     Check Interval: This is how frequent PlcData will listen for report generation requests form the PLC (milliseconds).
 
Report Create
The Create Report Tag Plc Address is what PlcData listens for. When it goes from OFF-To-ON, PlcData generates the requested report by querying the SQL server database. 
The Create Report is optional if the Plc Address is left blank i.e., Plc Address=””, then reports will be generated according to the day and time trigger settings 
If Create Report tag has a plc address filled, then the reporting is deemed to be also PLC controlled.
When PlcData has generated a requested report, it turns OFF the ‘Create Report’ flag and sends back a signal to the PLC to indicate the requested report has been generated, by turning the ‘Report Created Ack’ ON.
 
9.     Friendly name: This is usually a symbol or a name for the tag.
10.     Description: The is a short description to clarify the information the tag holds or what the tag does in some instances.
11.     Plc Address: This is the Tag address in the PLC i.e., for Siemens PLC ‘DB2.0.0’ (this is optional, if left blank only the Day/Time trigger can generate the report
12.     Plc Data Type: This is a Boolean type
13.     Units: This is usually not used
 
Report Created Acknowledge
Once report generation is complete an acknowledge signal is send back to the PLC. PlcData always turns the acknowledge tag to OFF before generating a report.
14.     Friendly name: This is usually a symbol or a name for the tag.
15.     Description: The is a short description to clarify the information the tag holds or what the tag does in some instances.
16.     Plc Address: This is the Tag address in the PLC i.e., for Siemens PLC ‘DB2.0.1’ (this is optional but highly recommended)
17.     Plc Data Type: This is a Boolean type
18.     Units: This is not usually used
 
Configuration Tables
 
1.     Expand Table: This doubles the size of the configuration table should more table rows be required for configuration.
2.     Shrink Table: This deletes all fully blank rows.
 
Report Sections
Each Report has several sections to help organise the report. Each section has a block of tags associated with it.
 
 
1.     Selected Controller Report: Once controllers and their associated reports have been configured, report sections need to be configured. Only configured controller-reports can be selected.
2.     Section Name: This identifies and describes the report section.
3.     Expand Table: This doubles the size of the configuration table should more table rows be required for configuration.
4.     Shrink Table: This deletes all fully blank rows.
 
Section Tags
Each section can have one or more tags. Sections can have same tags configured.
 
 
1.     Selected Controller Report Section: Once controllers and their associated reports and sections have been configured, the block of tags needs to be configured. Only configured controller-reports-sections can be selected.
2.     Friendly name: This is usually a symbol or a name for the tag i.e., ‘Mixer-Start’, ‘Flour-Weight’ etc... (this is optional).
3.     Description: The is a short description to clarify the information the tag holds or what the tag does in some instances. (this is mandatory for reporting)
4.     Plc Address: This is the Tag address in the PLC i.e., for Siemens PLC ‘DB2.4’ (this is usually mandatory)
5.     Plc Data Type: This is usually the supported data type for a PLC type (See controller type worksheet for details).
6.     Units: This refers to Engineering units i.e.  Degree Celsius (Optional)
7.     Expand Table: This doubles the size of the configuration table should more table rows be required for configuration.
8.     Shrink Table: This deletes all fully blank rows.
 
PLC - PlcData Handshake
 
1.     Plc sends signal to create a report by turning the Create Report flag to ON.
2.     PlcData turns the Report Created Ack to OFF.
3.     PlcData generates the report, turns the Create Report flag to OFF and the Report Created Ack to ON
4.     PlcData checks the Create Report flag every Check Interval time. And the cycle repeats from 1.
 
Controller Type
This is where supported controllers and their supported data types are documented.
 
 
1.     Controller Selection: This is the list of supported controllers. When configuring controllers’ users must choose from this list.
2.     Plc Model: These are the models that are supported, more models could be supported.
3.     Port Numbers: These are believed to be the PLC vendors port numbers and users should not change them unless they know what they are doing.
4.     Logix Data Type: Logix series supported data type.
5.     Slc Data Type: SLC505 supported data type.
6.     Siemens S7 Data Type: S1500 supported data type.
Report Example
1.     The name of the reporting file structure: Report Name_Ip Address_Date Time.CSV
2.     The report title and plc ip address are in row 1.
3.     The section name is on row 2
4.     The report headers are in row 4
5.     Tags are organised under their configured section.
6.     Section 1 and section 2 tags data have been logged under different events hence they have been registered in the data base at different times. Explaining the reason for the no-data indications. So users can configure their reports sections to be under one event. Events are asynchronous.
 
 NOTE: Format the excel date column to show the seconds part of the time.
 
 
DISCLAIMER
Plc Data has been thoroughly tested with a sample of PLCs types. Users are urged to try the free version before buying to make sure PlcData meets the required needs.