Excel Tag Select Reports

From SysCAD Documentation
Jump to navigation Jump to search

Navigation: User Guide ➔ Reports ➔ Excel Reports ➔ Get Tag Reports ➔ Tag Select 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: 21 September 2021 - SysCAD 9.3 Build 139.29552

Related Links: Example SysCAD Excel Reports, Sensitivity Analysis


Introduction

This Excel report returns all the model tags based on an SQL type statement.

  • For the first parse, SysCAD returns all the model tags that meet the select statement criteria, then
  • For the second parse it returns all the data in the same way as a SysCAD TagTable Report.

Tag Select Report Function Syntax

Syntax for SysCAD 9.3 Build 137 or later

Function Syntax: SysCAD_TagSelect(ReportName, Tag Location, Horizontal search length, Vertical search length, Select [FieldName] Operator "Value/String", Orderby [FieldName], GroupBy [FieldName], {Option1,...})
Example Report: SysCAD_TagSelect("Pipes", V, 30, 2000, Select [UnitGroup] == "Stream" AND [Qo.Sf (%)]>20 , Orderby [Graphic] [Tag], GroupBy [Graphic] BoldHeading:1, Clear: On)
Please see the Default Reports in the project folder: 04DetailedReport.xlsx

Syntax for SysCAD 9.3 Build 136

Function Syntax: SysCAD_TagSelect(ReportName, Tag Location, Horizontal search length, Vertical search length, Select [FieldName] Operator "Value/String", {Option1,...})
Example Report: SysCAD_TagSelect("Pipes", V, 30, 2000, Select [UnitGroup] == "Stream" AND [Qo.Sf (%)]>20 , Orderby [Graphic] [Tag], Clear: On)

Summary of the Excel Report keywords used in SysCAD:

Function Parameters Syntax Descriptions Note/Example
ReportName "Name of Report" If the report name includes spaces, then it must be surrounded by " " "StreamReport"
Tag Location H Or V H - primary tags are column headings, and
V - primary tags are row headings.
See Report Syntax
Horizontal search length Integer Number of columns for the report Table See Report Syntax
Vertical search length Integer Number of rows for the report Table See Report Syntax
SELECT Expression SELECT Defines a boolean expression selecting the subset of primary tags required for the report. This is constructed from FieldName and Operators. Using SQL query terminology, the SysCAD "SELECT" is equivalent to an SQL WHERE statement. NOTE: There must be a space after the keyword Select or the command will not work.
[FieldName (cnv)] The FieldName must be enclosed in square brackets [ ]. The conversion (eg t/h) is optional, if not specified, SI units will be used. Note, the FieldName can be copied directly from SysCAD by using the right mouse click Copy Tag command. Select [Qo.Sf (%)]>20
[SubType]

(Valid in Build 137 or later)

A special field used to gather information on sub models; Valid [SubType] are:

  • [SubType]=="RB" (returns all unit operation with reactions on)
  • [SubType]=="RCT" (returns all reactions)
  • [SubType]=="PID" (returns all PIDs)
  • [SubType]=="SetTagCon" (returns all SetTag Controllers)
  • [SubType]=="EHX" (returns all EHX Sub models)
  • [SubType]=="MU" (returns all Makeups )
  • [SubType]=="Evap" (returns all Evaporation Sub models)
  • [SubType]=="Noise" (returns all noise blocks, dynamic only)
  • [SubType]=="Signal" (returns all Signal blocks, dynamic only)
  • [SubType]=="Downtime" (returns all downtime blocks, dynamic only)
  • [SubType]=="FlowStats" (returns all FlowStats blocks, dynamic only)
  • [SubType]=="GenStats" (returns all GenStats blocks, dynamic only)

Please see Sub - model information for SubType examples.

[Parent.Tag]

(Valid in Build 137 or later)
This is a special tag to be used with [SubType] reports.

Select [SubType]=="RCT", Orderby [Graphic] [Parent.Tag] [Index]

This is used with the [SubType] reports. For example, [SubType]=="RCT" will return all the reactions, and the "primary tag" for the report might be "TANK1.RB.R1", if we want to add in a criteria that is on the TANK1 Tab page, for example TANK1.Tag then we will need to use the special tag [Parent.Tag] to fetch a tag at the First level.

(, ) Round brackets, used to group statements together. Select ([UnitGroup]=="Unit" AND NOT ([UnitType]=="FeederSink"))
<, <=, ==, !=, <>, >=, > Comparison Operator Select [UnitType]!="FeederSink"
AND Combines two criteria The following example will select all pipes on Graphics page 05_Flowsheet

Select [UnitType]=="Pipe-1" and [Graphic]=="05_Flowsheet"

OR Either Criteria The following example will select all Washer and Thickeners on all Graphics pages

Select [UnitType] == "Washer" or [UnitType] == "Thickener1"

NOT Excludes the selection This Excludes any FeederSink being reported.
Select ([UnitGroup]=="Unit" AND NOT ([UnitType]=="FeederSink"))
LIKE

The LIKE operator keyword can be used to match parts of strings, it uses the standard Regular Expression Syntax
The wildcard used to match any number of characters has the following syntax: .* (dot follow by asterisk)

This will select all UnitType that include the character "P" in the tag
Select [Tag] like ".*P.*"

This will select all pipes or direct links that have a tag starting with "P_"
Select [UnitGroup] == "Stream" and [Tag] like "P_.*"

This will select all pipes or direct links except those start with "P_9"
Select [UnitGroup] == "Stream" AND NOT ([TAG] like "P_9.*")

ORDERBY

[FieldName] ASC [FieldName] DES

Defines the sort sequence of the results. Where Asc is short for ascending and Des is short for descending. If Asc or Dec is not specified, Asc is used. The full word can be used "OrderBy [Tag] Ascending" rather than "OrderBy [Tag] Asc".

Select [UnitType]=="Pipe-1", OrderBy [Graphic] [Tag]
Select [UnitType]=="Pipe-1", OrderBy [Graphic] Dec [Tag] Asc
Select [UnitType]=="Pipe-1", OrderBy [Graphic] Ascending [Tag] Descending

GROUPBY
(Only Available in Build 137 or later)

[FieldName] Gap/Heading:count

  • [FieldName] is optional, if not specified then first Tag in OrderBy is used for grouping
  • Gap is optional. The number following this is the number of lines to be skipped between groups. If Gap and heading are not specified then a gap of 1 is assumed.
  • Heading is optional: the value of the group tag is displayed as a heading in the last line of multiple gap lines. Displayed in cell one column/row offset from the tag column/row.
  • Use "BoldHeading" for Bold Heading Text, "Heading" for normal heading text.

GroupBy
grouped by the first tag in the OrderBy, gap = 1 line with no heading
GroupBy Gap:3
grouped by the first tag in the OrderBy, gap = 3 lines with no heading
GroupBy Heading:2
grouped by the first tag in the OrderBy, gap = 2 lines with heading
GroupBy [Graphic]
grouped graphics page, gap = 1 line with no heading
GroupBy [UnitType] BoldHeading:3
grouped unit type, gap = 3 lines with bold heading

CLEARTAGS ON / OFF If this is ON, the Primary Tags will cleared beyond the range where valid tags exist and within the MaxRow/MaxCol count. The default setting is OFF which is used if this option is not specified. CLEARTAGS : On
CLEARTAGS : Off
CLEARRANGE ON / OFF If this is ON, then the area of the report (as specified by MaxRow and MaxCol count) is cleared before generating the report. Formatting of all cells is retained. NOTE: All cells, including cells with formulas, are cleared. The default setting is OFF which is used if this option is not specified. CLEARRANGE: On
CLEARRANGE: Off
CLEAR ON / OFF This is a combination of the two options CLEARTAGS and CLEARRANGE CLEAR: On
CLEAR: Off

NOTES

  1. The Options may be provided in any order and are not case sensitive.
  2. Make sure when writing the Tag Select expression, there is a space between the expression and the condition. For example, when typing Select [UnitType]=="Pipe-1", be sure to type in the space between Select and [UnitType], if not, the expression will return an error and the statement is ignored.
  3. Use the special graphics tag "Graphic" to return the name of the graphics page that the model appears on and/or to sort the report by flowsheet pages.
  4. If there are multiple tags in the expression, then any tags that are invalid (not found) for a specific unit model then the part of the expression with the invalid tag is ignored. So if you have an expression such as [Tag1]==v1 AND [Tag2]==v2 AND [Tag3]==v3 and Tag2 is not found but Tag1 and Tag3 evaluate to true then the overall expression is still considered to be true.
  5. As above, if the select condition refers to a property that does not exist in the model the condition will be ignored. For example using Select [RB.R20.Extent.Desc]=="H2O(g)" will look for reaction number 20 in a model to see if the extent species is H2O(g). If the model has a reaction number 20 and the extent species for it is H2O(g), then it is true, if the extent species for reaction number 20 is not H2O(g) then it is false. However, in models where there is no reaction number 20 this criteria will be ignored. If this was the only select criteria, the statement will be evaluated as true and data will be returned. In other words if the search select criteria refers to a property that is not in a model, it will be ignored and if it was the only select criteria, the condition will be evaluated as true - this may give unexpected results. For example using Select [RB.R30.Extent.Desc] =="H2O(g)" in a project which has no reactions will return the entire list of models in the project.
  6. If there is more than one select criteria and one of the criteria refers to a property that does not exist, it will be ignored. It is recommended that select criteria be chosen carefully to avoid selecting unintentional data.
  7. New data imported to a spreadsheet will overwrite any existing data in the selected range. However, if you do not use any of the Clear commands above, old data that is not overwritten will not be cleared - this may lead to confusion and users should be careful when not clearing data ranges before exporting data.
  8. If the Tag Select expression references a drop down list tag in SysCAD, then the user MUST use the integer value and NOT the string value. For example, if the user wishes to generate a list of all true Feeders to a project using the State dropdown list field, then the correct form of the select statement is: Select [UnitType]=="FeederSink" AND [State]==1 (can not use [State]=="Feeder").
  9. If using "Excel 97-2003 Workbook (*.xls)" the maximum column that can be referenced is column 256 because of this limitation in this Excel file format.

Tag Select Report Template

Report Template: These are distributed with Build 137 (or later) installation: Report Template

SysCAD Tag Select Report can also be created by:

  1. Manually by the user using the above syntax (or examples listed in the next headings)
  2. From Quick View background Click Pop Up menu, see QuickView
  3. From Quick View Editor, see Quick View Editor
  4. From Find, Find Results Report Options.

Stream Reports Examples

The Tag Select report is very useful for generating reports to Auto create a pipe list - to update changes in the project, pipe name change, addition, deletion and so on.

1) To create a stream report that auto generates the list for Pipe and Makeup stream for the entire project, use:
  • SysCAD_TagSelect("Streams", V, 30, 2000, Select [UnitGroup] == "Stream", Orderby [Graphic] [Tag], Clear: On)
2) To create a stream report for a specific graphic page, use:
  • SysCAD_TagSelect("Pipes", H, 200, 30, Select [Graphic]=="05_Flowsheet" AND [UnitGroup] == "Stream", OrderBy [Tag] Asc, Clear: On)
user can use "Page1" instead of the actual flowsheet name to specify a specific page. Using this method allows the user to change the page name without having to adjust the report. The graphics page list can be found in PlantModel - Flowsheets Tab.
  • SysCAD_TagSelect("Pipes", H, 200, 30, Select [Graphic]=="Page1" AND [UnitGroup] == "Stream", OrderBy [Tag] Asc, Clear: On)
3) To create a stream report that looks for specific conditions, for example, solids flow > 20% OR check velocity calculations use:
  • SysCAD_TagSelect("Pipes", V, 30, 2000, Select [UnitGroup] == "Stream" AND [Qo.Sf (%)]>20 , Orderby [Graphic] [Tag], Clear: On)
  • SysCAD_TagSelect("Report1", V, 10, 42, Select [Eqn.Diam (mm)] > 10, OrderBy [Graphic] Des [Eqn.Diam] Asc [Vel] Asc, NAN:nan)
4) To get a list of all the Makeup flows, use:
  • SysCAD_TagSelect(Streams, V, 30, 2000, Select ([UnitType]=="DirectLink" AND [SubClass]=="Makeup"), Orderby [Graphic] [Tag], Clear: On)
5) To get a list of all the pipes which have reactions turned on , use:
  • SysCAD_TagSelect("PipeRxns", V, 1, 100, Select ([UnitType] == "Pipe-1" AND [EB.Reactions] <>0 ) )
6) To get a list of all the pipes in evaluation sequence. Note that the model must be solved first. Use:
  • SysCAD_TagSelect("Pipes", V, 30, 2000, Select [UnitType] == "Pipe-1", OrderBy [Graphic] [EvalSequence])

Sub-Model Information, e.g. Reaction Blocks

Build 137 or later Syntax:

NOTE: These reports are available in the default report 04DetailedReport.xlsx.
  1. To create a list for all the unit operations, including pipes, that contain RB sub models.
    • SysCAD_TagSelect("Reactions", V, 20, 500, Select [SubType]=="RB" , Orderby [Graphic] [Tag], Clear: On)
  2. To create a list for all the reactions:.
    • SysCAD_TagSelect("Reactions", V, 100, 5000, Select [SubType]=="RCT", Orderby [Graphic] [Parent.Tag] [Index], Groupby [Parent.Tag] [Tag] BoldHeading:1, Clear: On)
  3. To create a list for all the PIDs:.
    • SysCAD_TagSelect(PID, V,50,1000, Select [SubType] == "PID", OrderBy [Graphic] [Parent.Tag] [Index], CLEAR: On)
  4. To create a list for all the SetTag Controllers:.
    • SysCAD_TagSelect("SetTagCtrl", V, 100, 500, Select [SubType]=="SetTagCon", Orderby [Graphic] [UnitType] [Parent.Tag] [Index], Clear: On)
  5. To create a list for all the makeups:.
    • SysCAD_TagSelect("Makeup", V, 100, 500, Select [SubType]=="MU", Orderby [Graphic] [UnitType] [Tag], Clear: On)
  6. To create a list for all the EHX sub model:.
    • SysCAD_TagSelect("EHX", V,9, 1000, Select [SubType]=="EHX", OrderBy [UnitType] [Graphic] [Tag], Clear: ON)
  7. To create a list for all the Evaporation sub model:.
    • SysCAD_TagSelect("Evap", V, 20, 1000, Select [SubType]=="Evap", OrderBy [UnitType] [Graphic] [Tag], Clear: ON)

Build 136 Syntax:

  1. To create a list for all the unit operations, including pipes, that contain RB, EHX, MU or VLE sub models.
    • SysCAD_TagSelect("SubModel", V, 20, 200, Select [EB.Reactions]<>0 or [Reactions]<>0 or [FEB.Reactions]<>0 or [EB.EnvironHX]<>0 or [EnvironHX]<>0 or [FEB.EnvironHX]<>0 or [EB.Makeups]<>0 or [FEB.Makeups]<>0 or [EB.VLEquilibrium]<>0, OrderBy [UnitType] [Graphic] [Tag])
  2. To create a list for all the unit operations, including pipes, that use the reaction block sub model. The resulting information can then be used set up a reactions report.
    • SysCAD_TagSelect("Reactions", H, 90, 1, Select [EB.Reactions]<>0 or [Reactions]<>0 or [FEB.Reactions]<>0, OrderBy [Graphic] Asc [Tag] Asc)

Overall Mass Balance

Useful tags for mass balance reporting are on the Links tab page of all models. Reports that are useful in creating an Overall Mass Balance are:

1) To get a list of all the feeders in a project (excluding those that are connected):
  • SysCAD_TagSelect(MassBal, V, 1, 100, Select [UnitType]=="FeederSink" AND [State]==1, OrderBy [Graphic] Asc, Clear: On)
2) To get a list of all the sinks in a project (excluding those that are connected):
  • SysCAD_TagSelect(MassBal, V, 1, 100, Select [UnitType]=="FeederSink" AND [State]==2, OrderBy [Graphic] Asc, Clear: On)
3) To get a list of all the makeup sources in a project:
  • SysCAD_TagSelect(MassBal, V, 1, 100, Select [UnitType]=="MakeupSource", OrderBy [Graphic] Asc, Clear: On)
4) To get a list of all the feeders, sinks and makeup sources in a project (HINT: report tags "Links.QmIn (t/h)" and "Links.QmOut (t/h)" to get all the massflows in and out):
  • SysCAD_TagSelect(MassBal, V, 10, 5000, Select ([UnitType]=="FeederSink" AND ([State]==1 OR [State]==2)) OR ([UnitType]=="MakeupSource"), OrderBy [Graphic] [State] [Tag], Clear: On)
5) Excluding deactivated pages:
  • SysCAD_TagSelect(MassBal, V, 10, 5000, Select ([UnitType]=="FeederSink" AND ([State]==1 OR [State]==2)) OR ([UnitType]=="MakeupSource") AND [Active]==1, OrderBy [Graphic] [State] [Tag], Clear: On)

Mass Balance on Individual Graphics Page

1) To get a list of all the feeders on a single flowsheet (including those that are connected):
For specified flowsheet
  • SysCAD_TagSelect(MassBal, V, 10, 1000, Select [Graphic]=="05_Flowsheet" AND [UnitType]=="FeederSink" AND ([State]==1 OR [State]==3), OrderBy [Graphic] [State] [Tag])
For all flowsheets (HINT:Report field Graphic so that results can be grouped by page)
  • SysCAD_TagSelect(MassBal, V, 10, 5000, Select [UnitType]=="FeederSink" AND ([State]==1 OR [State]==3) AND [Active]==1, OrderBy [Graphic] [State] [Tag])
2) To get a list of all the sinks on a single flowsheet (including those that are connected):
For specified flowsheet
  • SysCAD_TagSelect(MassBal, V, 10, 1000, Select [Graphic]=="05_Flowsheet" AND [UnitType]=="FeederSink" AND ([State]==2 OR [State]==4), OrderBy [Graphic] [State] [Tag])
For all flowsheets (HINT:Report field Graphic so that results can be grouped by page)
  • SysCAD_TagSelect(MassBal, V, 10, 5000, Select [UnitType]=="FeederSink" AND ([State]==2 OR [State]==4) AND [Active]==1, OrderBy [Graphic] [State] [Tag])
3) To generate a balance using stream tags: First need to get a list of all input pipes (connected to feeders), outlet pipes (connected to sinks) and input makeup streams as follows:
All input streams (HINT:Report field "LinksOut.[0].ConnTag" to get the pipe tag; Report field Graphic so that results can be grouped by page)
  • SysCAD_TagSelect(MB, V, 4, 5000, Select [UnitType]=="FeederSink" AND ([State]==1 OR [State]==3) AND [Active]==1, OrderBy [Graphic] [State] [Tag])
All outlet streams (HINT:Report field "LinksIn.[0].ConnTag" to get the pipe tag; Report field Graphic so that results can be grouped by page)
  • SysCAD_TagSelect(MB, V, 4, 5000, Select [UnitType]=="FeederSink" AND ([State]==2 OR [State]==4) AND [Active]==1, OrderBy [Graphic] [State] [Tag])
All inlet directlink makeups (HINT:Report field Graphic so that results can be grouped by page)
  • SysCAD_TagSelect(MB, V, 4, 5000, Select [UnitType]=="DirectLink" AND [SubClass]=="Makeup", OrderBy [Graphic] [Tag])
The stream tags reported from the above can then be used in other stream reports for mass balances by flowsheet.

Water / Steam Balance

Reports that are useful in creating a plant water / steam balance:

1) The Water / Steam Mass change values are reported on the Unit operation's Access Window - Links Tab. The following report can be used to report the Nett.Qm.H2O(l) (t/h) and Nett.Qm.H2O(g) (t/h) values for the project.
In this example any units or pipes where there are no changes in H2O are excluded.
  • SysCAD_TagSelect("H2OBal", V, 10, 2000, Select ([Nett.Qm.H2O(l)]!=0.0 OR [Nett.Qm.H2O(g)]!=0.0), Orderby [Graphic] [UnitType] [Tag])
2) As above but excluding deactivated pages.
  • SysCAD_TagSelect("H2OBal", V, 10, 2000, Select ([Nett.Qm.H2O(l)]!=0.0 OR [Nett.Qm.H2O(g)]!=0.0) AND [Active]==1, Orderby [Graphic] [UnitType] [Tag])
In this example, reporting all active models and links that may potentially have a change in H2O.
  • SysCAD_TagSelect("H2OBal", V, 10, 5000, Select [UnitGroup]!="Control" AND [Active]==1, Orderby [Graphic] [UnitType] [Tag])

Species Balance

Reports that are useful in creating a species balance, note the species of interest must be first added to the PlantModel Settings Tab. See How to perform a Species Mass Balance

The Mass change values for the species of interest are reported on the Unit operation's Access Window - Links Tab. The following report can be used to report the Nett.Qm.xxxxx (t/h) for the project.

NaCl(aq) is used for the following examples.

1) In this example any units or pipes where there are no changes in NaCl(aq) are excluded.
  • SysCAD_TagSelect("NaClBal", V, 10, 2000, Select [Nett.Qm.NaCl(aq)]!=0.0, Orderby [Graphic] [UnitType] [Tag])
2) As above but excluding deactivated pages.
  • SysCAD_TagSelect("NaClBal", V, 10, 2000, Select ([Nett.Qm.H2O(l)]!=0.0) AND [Active]==1, Orderby [Graphic] [UnitType] [Tag])
3) In this example, reporting all active models and links that may potentially have a change in NaCl.
  • SysCAD_TagSelect("NaClBal", V, 10, 5000, Select [UnitGroup]!="Control" AND [Active]==1, Orderby [Graphic] [UnitType] [Tag])
4) The NaCl(aq) entering the process from Feeder / Makeup Source
  • SysCAD_TagSelect(NaClBal, H, 1000, 1, Select ([UnitType]=="FeederSink" AND [State]==1) OR ([UnitType] == "MakeupSource") AND [Links.Nett.Qm.NaCl(aq)]<>0, OrderBy [Graphic] [Tag], Clear: On)
5) The NaCl(aq) Leaving the process
  • SysCAD_TagSelect(NaClBal, H, 1000, 1, Select ([UnitType]=="FeederSink" AND [State]==2) AND [Links.Nett.Qm.NaCl(aq)]<>0, OrderBy [Graphic] [Tag], Clear: On)
6) The NaCl(aq) used by or created by reactions
  • SysCAD_TagSelect("NaClBal", H, 1000, 1, Select ([EB.Reactions]<>0 or [Reactions]<>0 or [FEB.Reactions]<>0 ) AND [Links.Nett.Qm.NaCl(aq)]<>0 , OrderBy [Graphic] Asc [Tag] Asc)

List Graphics Symbols

The graphics page and symbol are NOT shown in the model access window but are available using the special graphics tag "Graphic" and "Symbol". Reports that are useful to get a list of unit models including flowsheet and graphics symbol used:

1) Report all unit models flowsheet, UnitType and Symbol. (HINT:Report field Graphic, UnitType and Symbol)
  • SysCAD_TagSelect("Models", V, 10, 2000, Select ([UnitGroup]=="Unit" OR [UnitGroup]=="Control"), Orderby [Graphic] [UnitType] [Tag])

The example below shows a report that has been generated to report the Graphic, UnitType and Symbol for all units in a project:

Excel Select Report 1.png