Excel Trend Reports
Navigation: User Guide ➔ Reports ➔ Excel Reports ➔ Trend Reports
| Excel Automation | Export Reports | Import Data | Trend Data | Information Report | |||
|---|---|---|---|---|---|---|---|
| Excel Report Summary | Adding | Executing Report | Get Tag Report | Tag Select Report | Quick View Report | Set Tag Report | Trend Report | General | Species Info |
Latest SysCAD Version: 19 March 2024 - SysCAD 9.3 Build 139.35102
NOTE: We recommend using the Archive Reporter for dynamic project reports. The archive reporter is significantly faster, has more inbuilt reporting & calculation options and does not require tags to be recorded in the data historian.
While the Trend Windows are still highly recommended for Dynamic projects, the Trend Report should be built from the archive results, which involves importing the archiver output files into Excel, often through a macro, for data analysis and graphing. For more information, please see Archive Reporter.
The Trend report option is explained below for information only.
Introduction
This report is used in dynamic projects to obtain historical information. Although the Archive Reporter is recommended for trend reports, the Excel trend report is currently maintained for backward compatibility. Unlike the Archiver that generates the report while solving, the Trend Report retrieves data from the SysCAD Historian so all variables that have been recorded by the SysCAD data Historian can be reported via the Excel Trend Report. The most common ways to add tags to the historian is through trend pages or through a specified historian tag list file entered on the Dynamic page of the "Solver Settings".
The user must select Trend as the Type of report in the Excel Automation Report dialog window in SysCAD.
SysCAD loads the specified Excel file and searches all the Sheets for the keyword TrendReport(ReportName). This key word can be on any number of sheets, any quantity of times at any location. A SysCAD trend report will return a series of data on a time basis and default Excel XY scatter chart can be generated automatically by using the DoChart function.
Example
An example report is presented below. The report is identified with the keyword TrendReport(Report Name).
TrendReport(example) Allpoints() Duration(1:00:00) Location("Sheet1",30,2) Headings() Time(Seconds) TimeFull() TimeDate() GC_1.d1 GC_1.d2
Syntax
SysCAD expects to find a number of other function key words to the right of the TrendReport keyword. These extra functions are used to configure the trend report. They can be in any sequence and some are optional as illustrated by the different examples above. SysCAD keeps processing functions to the right of the key word until a blank cell is found.
A location must be specified. One of the three report type functions AllPoints, AvePoints or EndPoints must be specified. Tip: for the AvePoints and EndPoints options specify 1 more than the number of time intervals required. For example: if you want 1 minute intervals over a full hour you require 61 points or 25 points for 1 hour intervals over a full day.
The report time frame must be specified using a valid combination of the Start, End and Duration options. As illustrated in the examples this could be:
- Start and End;
- Start and Duration;
- End and Duration;
- Duration.
For option 4 where only the duration is specified, it is assumed that the end time is the current SysCAD simulation time.
SysCAD looks for the reporting variables (or tags) starting in the cell directly below the TrendReport key word. It then searches the cells horizontally until a blank cell is found.
The reporting variables (or tags) may be placed in column (vertical) form under the keyword SysCAD_TrendReport(ReportName). There should be at least one (up to 5) blank cells between the keywords and the tags. For example:
SysCAD_TrendReport(example) Allpoints() Duration(1:00:00) Location("Sheet1",30,2) Headings() Time(Seconds) TimeFull() TimeDate() --Leave a Blank Line-- GC_1.d1 GC_1.d2
In the above examples two tags are used. If valid full tag is not entered or the variable is not recorded in the historian, SysCAD will ignore the variable and returns an error message.
Trend Report Feedback
SysCAD provides feedback about the report on a separate sheet with the title Trend Errs. All errors and messages can be viewed on this page after a report has been generated.
- Location(Worksheet, row, column)
- Start(Full SysCAD time and date)
- End(Full SysCAD time and date)
- Duration(Duration time)
- AllPoints() - Reports all recorded data points.
- AvePoints(number of points) - Reports the defined number of data points using time-weighted average.
- EndPoints(number of points) - Reports the defined number of data points using value at the end of the time period.
- Headings() - Returns a row using variable tags as column headings in the report.
- TimeDate() - Includes a column with the time value in an Excel Date-Time format.
- TimeFull() - Includes a column with the time value as a full string.
- Time(units) - Includes a column of time values as numbers starting from zero. The valid time unit options are Seconds, Minutes, Hours, Days.
- NOTE SysCAD only looks at the first letter of these options, so by example Time(h) as well as Time(hours) are valid options.
- DoChart(name) - Creates a default excel XY scatter chart.
Examples
More Examples of trend reports are shown below:
TrendReport(example1) AvePoints(7) Duration(0:01:00) Start(08:00:00.00 27/09/2005) Location("Sheet1",1,2) Headings() Time(Seconds) TimeFull() TimeDate() GC_1.d1 GC_1.d2
TrendReport(example2) EndPoints(7) Duration(0:01:00) End(10:00:00.00 27/09/2005) Location("Sheet1",15,2) Headings() Time(Seconds) TimeFull() TimeDate() GC_1.d1 GC_1.d2
Example Results are as follows:
For Example 1 above:
Time Time Seconds GC_1.d1 GC_1.d2 08:00:00.00 27/09/2005 38622.33 0 3.601 45.3016 08:00:10.00 27/09/2005 38622.33 10 2 6.5 08:00:20.00 27/09/2005 38622.33 20 3.6 16.5 08:00:30.00 27/09/2005 38622.33 30 4.6 26.5 08:00:40.00 27/09/2005 38622.33 40 5 36.5 08:00:50.00 27/09/2005 38622.33 50 4.8 46.5 08:01:00.00 27/09/2005 38622.33 60 4.3 56.5
For Example 2 above:
Time Time Seconds GC_1.d1 GC_1.d2 09:59:00.00 27/09/2005 38622.42 0 2 2 09:59:10.00 27/09/2005 38622.42 10 2 12 09:59:20.00 27/09/2005 38622.42 20 4 22 09:59:30.00 27/09/2005 38622.42 30 5 32 09:59:40.00 27/09/2005 38622.42 40 5 42 09:59:50.00 27/09/2005 38622.42 50 3 52 10:00:00.00 27/09/2005 38622.42 60 8 61