Excel Get Tag Reports

From SysCAD Documentation
Jump to navigation Jump to search

Navigation: User Guide ➔ Reports ➔ Excel Reports ➔ Get Tag 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: 23 November 2021 - SysCAD 9.3 Build 139.29907

Related Links: Example SysCAD Excel Reports, Sensitivity Analysis


Introduction

This report is used to obtain the current value of tags. SysCAD loads the specified Excel file and searches all the sheets in the workbook for any of the SysCAD Get Tag Report key words such as "SysCAD_TagTable", "SysCAD_TagList" or "SysCAD_TagSelect". The key words can be on any number of sheets, any quantity of times at any location.

SysCAD Report Type and Keywords
Type Example Keyword (See also Keyword Syntax) Tag / Variable Required Brief Description
List SysCAD_TagList("KPI", V, 20) Tag = Full Variable Tag
(Use Copy Full Tag)
Eg: P101.Qo.Qm (t/h)
SysCAD Report in a Single Column - This reports SysCAD values to Excel. Use this form to report a group of tags from different unit operation models, for example, the Key Process Indicators for the project.
SysCAD_TagList("KPI", H, 20) SysCAD Report in a Single Row - This reports SysCAD values to Excel. Use this form to report a group of tags from different unit operation models, for example, the Key Process Indicators for the project.
Lists using
Offset
SysCAD_TagListOffSet(B12, "Case2") Referencing an existing List,
No new tags required
Multiple Reports from a Single Row or Column (OffSet). This is used in combination with the SysCAD_TagList Report. Using the keyword SysCAD_TagListOffSet, the user can obtain multiple lists of values from SysCAD using a single set of SysCAD tags.
Table
Simple
SysCAD_TagTable("Inputs", H, 50, 50) Primary Tag: must include Unit Name
Eg: P101

Secondary Tag: variable name
(Use Copy Tag)
Eg: Qo.Qm (t/h)
SysCAD Report in a Table - This reports SysCAD values to Excel, the unit tags will be column headings, and variables are row headings. Best used for overall mass balance tables.
SysCAD_TagTable("Streams", V, 50, 2000) SysCAD Report in a Table - This reports SysCAD values to Excel, the unit tags will be row headings, and variables are column headings. Best used for stream data tables.
Table
Multi Column
SysCAD_TagTable("MultiCol", H, 2, 25, 3) SysCAD Report in Multi Column blocks, This is an extension of the SysCAD TagTable Report. Using an additional parameter, the user can obtain multiple columns of values from SysCAD using a single primary SysCAD tag. This reports SysCAD values to Excel, the unit tags will be column headings, and variables are row headings.
Table
Multi Row
SysCAD_TagTable("MultiRow", V, 6, 3, 5) SysCAD Report in Multi Row Blocks, This is an extension of the SysCAD TagTable Report. Using an additional parameter, the user can obtain multiple columns of values from SysCAD using a single primary SysCAD tag. This reports SysCAD values to Excel, the unit tags will be row headings, and variables are column headings.
Table with
Select Option
SysCAD_TagSelect("Streams", V, 30, 2000, Select [UnitGroup] == "Stream", Orderby [Graphic] [Tag], ClearTags: On) Primary Tag: not required, created by tagselect

Secondary Tag: variable name
(Use Copy Tag)
Eg: Qo.Qm (t/h)
SysCAD Report in a Table based on Select criteria. Using the keyword SysCAD_TagSelect, the user can obtain a table of values from SysCAD as described for SysCAD_TagTable Report, but with the additional feature of using a SQL type select statement to get SysCAD to return a list of models that meet the select statement rather than entering the unit model tags.

Notes:

  1. The keyword "SysCAD_TagTable" has replaced the keyword "SysCAD_Tags" that was used in previous versions of SysCAD. SysCAD will still recognise this keyword and generate a report table.
  2. The example keywords in the above table can be copied and pasted directly into an Excel spreadsheet for an Excel report.
  3. The SysCAD key words (such as SysCAD_TagTable and SysCAD_TagList) are not case sensitive;
  4. The column/row at which the SysCAD key word resides, as well as the tags, can be hidden to improve the presentation of the report.

Report Keyword Syntax

Keyword Function Parameter Used by Report type Description

Report Name

(Required)

all Reports types
  • The first parameter within the function is the name of the report.
    • For example, for keyword function "SysCAD_TagTable(Streams, H, 4, 4)", the name of the report is "Streams".
  • If the name contains spaces, then it should be enclosed in quotes.
  • The report name is only used by SysCAD to find a match if a specific report has been requested. If user intend to generate all the reports in the workbook at the same time, then the name can be left blank with "" as the first parameter.
  • The same report name may be used more than once - this is useful if you want to generate a number of reports at the same time.
    • For example, when running scenarios, user can set up multiple reports for the same case run, while naming them all Case1. At the end of the first case, all the reports can be generated with one report call.
  • This is not to be confused with the MS Excel File name or MS Excel Worksheet Name.
Primary Tag Location
(Required)
all Reports types
except Tag_ListOffset

The second parameter defines the location of the SysCAD Tags; H (or h) for Horizontal OR V (or v) for Vertical. If this parameter is invalid then 'V' (vertical) is assumed.

If H (horizontal) is entered for:

a) A SysCAD_TagTable report, then SysCAD expects to find the primary tags in cells to the right of the key word;
b) A SysCAD_TagList report, then SysCAD expects to find the full tags in cells to the right of the key word.

If V (vertical) is entered for:

a) A SysCAD_TagTable report, then SysCAD expects to find the primary tags in cells directly below the key word;
b) A SysCAD_TagList report, then SysCAD expects to find the full tags in cells directly below the key word.
Horizontal search length
(Required/Optional)
all SysCAD_TagTable,
Tag Select and
SysCAD_TagList - Horizontal
  • For simple Tag List and Tag Table, this parameter determines how many cells to the right of the key word SysCAD must process.
  • For multi Column or Multi Row Tag Table, this parameter determines how many Primary Tag Blocks to the right of the key word SysCAD must process.
  • The exact number of columns with tags does not have to be specified, e.g. if a value of 5 is specified SysCAD will read five cells looking for tags.
  • If there are more than one report on the same worksheet, then care should be taken when specifying the search length, it must not be too big so that it overlaps the start of another report on the same row.
  • When using "Excel 97-2003 Workbook (*.xls)" the search area must not exceed column 256 because of this limitation in this Excel file format.
Vertical search length
(Required/Optional)
all SysCAD_TagTable,
Tag Select and
SysCAD_TagList - Vertical
  • For simple Tag List and Tag Table, this parameter determines how many cells below the key word SysCAD must process.
  • For multi Column or Multi Row Tag Table, this parameter determines how many Primary Tag Blocks below the key word SysCAD must process.
  • The exact number of rows that contain tags does not have to be specified.
  • If there are more than one report on the same worksheet, then care should be taken when specifying the search length, it must not be too big so that it overlaps the start of another report on the same column.
Primary Tag Block Size
(Optional)
Multi Column or Multi Row Tag Tables
Inactive:XXX
(Optional)

Available in Build 138 or later
All SysCAD_TagTable,
SysCAD_TagList and
Tag Select
  • It is used to report "inactive" model values, usually as a results of inactivated flowsheets. These are normally displayed within SysCAD using the "inactive status colour".
  • The default for inactive is: *Inactive*, this parameter does not have to be added if user is happy with the default value.
  • For customised display, please enter the required text after Inactive:
  • For example:
    1. The left image is generated using default inactive settings: SysCAD_TagTable("Stream", V, 12, 100)
    2. The right image is generated using user defined inactive settings: SysCAD_TagTable("Stream", V, 12, 100, Inactive:N/A)
    Report138 inactive.png
NAN:XXX
(Optional)

Available in Build 138 or later
All SysCAD_TagTable,
SysCAD_TagList and
Tag Select
  • It is used when the reported value is not a number (NAN), the variable would normally be displayed within SysCAD as "*". an example of this would be the volume fraction of an aqueous species using density correction function.
  • The default for inactive is: *, this parameter does not have to be added if user is happy with the default value.
  • For customised display, please enter the required text after NAN:
  • For example:
    1. The left image is the value displayed in the SysCAD access window
    2. The middle image is generated using default NAN settings: SysCAD_TagTable("Stream", V, 12, 100)
    3. The right image is generated using user defined NAN settings: SysCAD_TagTable("Stream", V, 12, 100, NAN:Not Applicable)
    Report138 nan.png

Report Layout Requirements

Basic Format of a simple Get Tag Report

Two examples of simple reports are shown below, all tags in blue are required for the report:

Tagtable.png    Taglist1.png
  1. The first example (left image) is in the form of a function SysCAD_TagTable(Report Name, (Primary) Tag Location - Horizontal or Vertical, Horizontal search length, Vertical search length). The keyword parameters are explained in Report Keyword Syntax.
    • This example will create a table of values from the primary tags in cells D1 to F1 combined with the secondary tags in cells C2 to C5;
    • Cell C1 contains the key for the report;
    • Columns A & B are optional.
    • When generated, the values will be populated in cells D2 to F5.
  2. The second example (right image) is in the form of a function SysCAD_TagList(Report Name, Tag Location - Horizontal or Vertical, Horizontal or Vertical search length). The keyword parameters are explained in Report Keyword Syntax.
    • This example will create a list of values from the full tags in cells C2 to C5.
    • Cell C1 contains the key for the report.
    • Columns A & B are optional.
    • When generated, the values will be populated in cells D2 to D5.

Adding SysCAD Tags and variables to the reports

  1. The exact SysCAD tag is required when referenced in the report. The SysCAD tag can be copied and pasted into the report. See section on Copying Tags.
  2. Full Variable Tag: some report type (eg: Tag List report) requires the Full Variable Tag, please use Copy Full Tag to copy the tag.
  3. Primary tag: When a full tag is copied from SysCAD, the first part of the tag (separated by dots) is the unit tag name. This is usually referred to as the primary tag.
    • For example, if P_001.Qo.Qm (t/h) is the full tag, then P_001 is usually the Primary Tag.
    • The Primary tag can also the be portion of the tag that is common for the list of variables, so, if all variables are from the Qo tab, then the Primary tag can be P_001.Qo
  4. Secondary tag: This is the remaining of the full tag when Primary tag is removed. This is usually what is copied from Copy Tag.
    • Using the same example as above, if Primary Tag is P_001, the secondary tag is Qo.Qm (t/h)
    • If the primary tag is P_001.Qo, then the secondary tag is Qm (t/h)
  5. Tags are not case sensitive, e.g. the secondary tag Qm or QM would both work; as would primary tag P_001 or p_001.
  6. The required engineering unit is specified after the secondary tags.
    • The secondary tag and the engineering units are separated by a space.
    • The Engineering units are usually placed in round brackets, but this is optional.
    • Engineering units are case sensitive. Check the spelling in SysCAD if different units are needed.
    • If the engineering units requested are invalid, unknown or not specified then the result is returned in the SysCAD default SI units.
  7. There are two special graphics Names that can be used as variable names in a SysCAD get tag reports that are not actual 'variable tags' on the access window of the unit. These special graphics tags are:
    • Graphic - this will return the name of the graphics page that the model appears on.
    • Symbol - this will return the name of the graphics symbol used by a model.
    • Please see the TagTable example in the List Graphics Symbols example on the TagSelect page.

Reporting Rules

SysCAD follows the following rules when placing results in the cells:

  1. If the primary or secondary or full tag for a cell is blank, the resulting cell is left unchanged.
  2. If the relevant cell contains a formula, the cell is left unchanged. The cell is not altered even if the corresponding tag is valid.
  3. For SysCAD_TagTable reports, SysCAD generates a full tag by combining the corresponding primary and secondary tags.
    • If this tag does not exist in SysCAD, the cell will be left blank.
    • If the full tag does exist the resulting value in the required units is placed in the relevant cell.
  4. All styles such as fonts, borders, colours, bold text etc are ignored when SysCAD is transferring data. The format styles of the results are not changed when SysCAD writes the values to these cells.
  5. SysCAD provides feedback about the report on a separate sheet with the title Tag Errs. All errors and messages can be viewed on this page after a report has been generated.

Report Type Examples

SysCAD Report in a Table

This report is used to obtain the current value of tags in a table format. Using the keyword SysCAD_TagTable, the user can obtain a table of values from SysCAD built up from primary and secondary tags in the row and column headings.

The syntax for this type of report is SysCAD_TagTable(Report Name, (Primary) Tag Location (either H or V), Horizontal search length, Vertical search length). These parameters are explained in Report Keyword Syntax.

An example of a simple report is shown below, all tags in blue are required for the report:

SysCAD_TagTable("Flow",H,3,4)

Tagtable.png

The exact SysCAD Tag is required when referenced in the report.

  • The primary tags (cells D1 to F1 in the example) are the unit model names (i.e. the name of the pipes, tanks, etc.) See Obtaining Equipment & Pipe List for the Report Generation for hints on how to copy the required Tags to Excel.
  • The secondary tags (cells C2 to C5 in the example) are the quantities to be measured such as mass flow or temperature. Please use Copy Tag for secondary tags.
  • If there are more than one report on the same MS Excel worksheet, please make sure the search length is not too large so that it overlaps with a different report.
  • An example of a Tagtable report is included with the SysCAD install, see Example SysCAD Excel Reports

SysCAD Tag Select Reports

An extension of the Table report is the Select report which is a SysCAD Report in a Table based on Select criteria. Same as SysCAD_TagTable with extra options to specify selection criteria to return the list of model tags that match the select statement as well as the table data.

  • There are many examples of the tag select reports, these are documented on a separate page, please see Excel Tag Select Reports.
  • The Default Reports uses the Tag Select Reports extensively. User may open these default reports to view some working examples.

SysCAD Report in a Single Row or Column

This variation of SysCAD Get Tag report is for a user wanting to generate a small report, either in a straight row or column. Using the keyword SysCAD_TagList, the user can obtain a list of values from SysCAD using full tags in the adjacent row or column.

The syntax for these types of reports is SysCAD_TagList(ReportName, Tag Location (either H or V), Search Length). These parameters are explained in Report Keyword Syntax.

Some examples are as follows:

A Report in a single Row

SysCAD_TagList("Results",H,4)
TaglistH.png
  • The full SysCAD tags in blue are required as part of the report set up. Please use Copy Full Tags when copying the variable from SysCAD.
  • The results are always placed in the row directly underneath the SysCAD Tags.
  • If there are more than one report on the same MS Excel worksheet, please make sure the search length is not too large so that it overlaps with a different report.

A Report in a single Column

SysCAD_TagList("Results",V,4)
Taglist1.png
  • The full SysCAD tags in blue are required as part of the report set up. Please use Copy Full Tags when copying the variable from SysCAD.
  • The results are always placed in the column directly adjacent to the SysCAD tags.
  • If there are more than one report on the same MS Excel worksheet, please make sure the search length is not too large so that it overlaps with a different report.
  • An example of a full TagList report is included with the SysCAD install, see Example SysCAD Excel Reports

Multiple Reports from a Single Row or Column (OffSet)

This report format is most useful for users wanting to compare key results in a summary table for multiple scenarios or runs under various conditions. To do this:
  1. First set up a report using SysCAD_TagList in a single row or column. (See column C.)
  2. Then use the key word SysCAD_TagListOffSet(cell reference, Report Name) to point to the reference SysCAD_TagList Report created in the previous step.
    • columns E, and F are examples of offset reports. The reference report Case1 we have set up is located in Cell C4 (keyword location), so we will enter SysCAD_TagListOffSet(C4, Case2) for case2.
  3. As before, writing in blue forms the SysCAD report,
    • Both column C and row 4 can be hidden when presenting the report for neatness.
  4. Different Report Name must be used for each new offset report. In our example, we have used Case2 and Case 3 as report names (highlighted in the picture below).
    Report offset.png
    (This is an extract from the Digestion.xlsm report, located in the \Examples\Alumina\Digestion Example.spf\Reports folder.)
  5. To populate the reports, after each case/scenario solve, specify the Report Name to be generated, and only generate that report. That is, DO NOT leave '*' as the report name, as this will populate all the reports, and not the desired case report for the scenario.
    Report offset2.png
  6. Alternatively, once the reports are set up, the user may use a command script (see section on Command Scripts) or the automation macro interface (see section on COM Automation) to automate the process of running and reporting on multiple cases.

SysCAD report in Multi Column Blocks

This is an extension to the SysCAD TagTable Report.

The syntax used is SysCAD_TagTable(Report Name, (Primary) Tag Location (either H or V), Horizontal search length, Vertical search length, Primary Tag block Size).

These parameters are explained in Report Keyword Syntax.

  • In the example below, the Horizontal search length is 5, the Primary Tag block size is 3, this means that there are five Primary Tags, and they will be located 3 columns apart (cells F1, G1 and then I1 and J1 (and so on) are ignored).

SysCAD_TagTable("MultiCol",H,5,30,3)

Excel Report 3.png

NOTES:

  1. In the above example, the writing in blue and Shaded Cells are user-defined information.
  2. Columns B,C and D can be hidden when presenting the report for neatness.
  3. Blank cells are allowed in this report type.

SysCAD report in Multi Row Blocks

This is an extension to the SysCAD TagTable Report.

The syntax used is SysCAD_TagTable(Report Name, (Primary) Tag Location (either H or V), Horizontal search length, Vertical search length, Primary Tag block Size).

These parameters are explained in Report Keyword Syntax.

  • In the example below, the Vertical search length is 3, the Primary Tag block size is 5, this means that there are three Primary Tags, and they will be located 5 rows apart.

SysCAD_TagTable("MultiRow",V,6,3,5)

Reportmultirow.png

NOTES:

  1. In the above example, the writing in blue and Shaded Cells are user-defined information.
  2. Rows 1 to 5 can be hidden when presenting the report for neatness.
  3. Blank cells are allowed in this report type.