Excel Reports

From SysCAD Documentation
Jump to navigation Jump to search

Navigation: User Guide -> Reports -> Excel Reports

Excel Report Summary Adding & Executing Report Get Tag Report Tag Select Report Set Tag Report Trend Report Quick View Data Report

Related Link: Example SysCAD Excel Reports

Introduction

Microsoft Excel has many functions and features available for presenting data in different formats. Instead of duplicating these functions, SysCAD includes the capability to communicate with Microsoft Excel. This is done in SysCAD by using COM Automation (ie SysCAD communicates with Excel using COM Automation).

The number of ways SysCAD calls Excel are:

Export Data Import Data

Reports where information is transferred from SysCAD to Excel.
The following types of SysCAD Reports are available:

  1. Get Tag Reports
  2. Get Tag Select Reports
  3. Trend Reports (for dynamic projects).
  4. General Information Reports
  5. Species Information Reports

Inputs where information is transferred from Excel to SysCAD.

  1. Set Tag Report.
  2. Activate an Excel Macro from SysCAD.

Video Links

There are a number of videos in the Tutorial section of the Help documentation that show users how to create, link to and generate or process Excel reports. Please see Basic Tutorial - Excel Reports and Quickview.

Steps in Creating a Report

The steps required to successfully run a SysCAD report in any form are as follows:

  1. Create a SysCAD report in Excel. See Excel Get Tag Reports and Excel Set Tag Reports for detailed description of the SysCAD report formats.
  2. Once the Excel report is set up and saved, open the Reports dialog window in SysCAD via Tools - Reports, using the icon Reportbutton.png, or the shortcut keys CTRL + R. This action will open the Export Reports Dialog Box.
  3. There are 2 different types of reports:
    • To send data from SysCAD to Excel, add the Excel report to the 'Export Reports' tab;
    • To import data from Excel into SysCAD, add the Excel report to the 'Import Data' tab.
  4. Add the new report that has been created by clicking on the 'Add' button in the appropriate tab and browsing for the new report - see Add a report to the Dialog window.
  5. Once the report has been located, click the 'OK' button and you will see the report has been added to the Reports dialog window.
  6. The report can now be accessed by SysCAD:
    • If it is on the 'Export Report' tab, click on the 'Generate' button and SysCAD will send data to the Excel report;
    • If it is on the 'Import Data' tab, click on the 'Process' button and SysCAD will import data from Excel to SysCAD.
  7. SysCAD accesses Excel using COM Automation:
    • If Excel is not started, SysCAD will start it and make it the active application.
    • If more than one copy of Excel is opened, SysCAD uses the first copy it finds.
  8. SysCAD will then search for the report file:
    • If the file is already opened and saved, it will be used for the report.
    • If the file is opened and has been changed and NOT saved, SysCAD will return an error message prompting the user to save the Excel spreadsheet and try again.
  9. When SysCAD has finished generating or processing the reports, it saves the spreadsheet. Any errors encountered will be reported in the Excel spreadsheet.
  10. The report is now ready for reviewing.

NOTES:

  1. Always save the Excel spreadsheet before using SysCAD reporting.
  2. Any fields from a Deactivated flowsheet will be reported as a '*'.
  3. SysCAD reporting becomes EXCESSIVELY slow if Excel is in "page break view". Please make sure the spreadsheet is in normal view while a report is being generated.
  4. Do not edit a spreadsheet while SysCAD is busy writing to it, as the report generation will be cancelled. Use the Lock Excel while generating option to avoid accidentally stopping the reporting. See Excel Reports Dialog Box - Options Tab.
  5. Reports may be generated while SysCAD is running or solving. However, it is recommended that reports be generated when SysCAD is stopped as it is faster and the values obtained will be synchronised (i.e. if SysCAD is running values may be obtained from different iterations).

Location of Reports

  1. Reports may be stored at any location.
  2. If the report is stored within the Project folder, then SysCAD will always create a copy of all connected reports when the user saves the project to a different name, or as a version. (A connected report is a report that has been Added using the Excel dialogue box in SysCAD)
  3. Users may view all connected reports in a project and their locations by accessing the Project Window - Files List dialogue box.

Adding and Executing the Excel Report in SysCAD

Command Button : Reportbutton.png

DefaultReport.png

For full description of this topic, please see Adding and Executing Excel Reports.

Summary Table of Report Types and Keywords

SysCAD Report Type and Keywords
Type Example Keyword (See also Keyword Syntax) Brief Description or Link to More Information
List SysCAD_TagList("KPI", V, 20) SysCAD Report in a Single Column, Hint: Use Copy Full Tag from SysCAD. 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, Hint: Use Copy Full Tag from SysCAD. 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_SetTagList("Criteria", V, 20) SysCAD Set Tag Report in a Single Column. This sends values from Excel to SysCAD, best used when setting project criteria before project solve.
SysCAD_SetTagList("Criteria", H, 20) SysCAD Set Tag Report in a Single Row. This sends values from Excel to SysCAD, best used when setting project criteria before project solve.
Table
simple
SysCAD_TagTable("Inputs", H, 50, 50) SysCAD Report in a Table, Hint: Use Copy Tag from SysCAD. 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, Hint: Use Copy Tag from SysCAD. This reports SysCAD values to Excel, the unit tags will be row headings, and variables are column headings. Best used for stream data tables.
SysCAD_SetTagTable("Feed", H, 5, 50) SysCAD Set Tag Report in a Table, Hint: Use Copy Tag from SysCAD. This sets values from Excel to SysCAD, the unit tags will be Column headings, and variables are row headings.
SysCAD_SetTagTable("HX", V, 5, 20) SysCAD Set Tag Report in a Table, Hint: Use Copy Tag from SysCAD. This sets values from Excel to SysCAD, the unit tags will be row headings, and variables are column headings.
Table
Multi Column
SysCAD_TagTable("MultiCol", H, 2, 25, 3) SysCAD Report in Multi Column blocks, Hint: Use Copy Tag from SysCAD. This reports SysCAD values to Excel, the unit tags will be column headings, and variables are row headings. In this example, each stream has 3 columns of reported data.
Table
Multi Row
SysCAD_TagTable("MultiRow", V, 6, 3, 5) SysCAD Report in Multi Row Blocks, Hint: Use Copy Tag from SysCAD. This reports SysCAD values to Excel, the unit tags will be row headings, and variables are column headings. In this example, each stream has 5 rows of reported data.
Table with
Select Option
SysCAD_TagSelect("Streams", V, 30, 2000, Select [UnitGroup] == "Stream", Orderby [Graphic] [Tag], ClearTags: On) 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. Please follow link for more information and examples.
Table
with Offset
SysCAD_TagListOffSet(B12, "Case2") Multiple Reports from a Single Row or Column (OffSet). Please follow link for more information and example.
SysCAD_SetTagListOffSet(B22, "Case2") Multiple SysCAD Set Tag Report in a Single Row or Column . Please follow link for more information and examples.

Report Types

Get Tag Reports (Export)

The following report types allow the user to export SysCAD information to Excel. Please see Excel Get Tag Reports for more information.

Set Tag Reports (Import)

The following report types allow the user to import data from Excel to SysCAD. Please see Excel Set Tag Reports for more information.

Info Report

The "Info Report" are auto-generated reports. User can specify a workbook for these reports to be added, then press the Info Report button, and select the required report type. Currently two types of reports are available:

  1. General Information Report - Reports Equipment, Pipes and Species List for the project.
  2. Species Information Report - Reports Species Data defined in the species database.
Excel Info Reports.png

Trend Report

Please see Excel Trend Reports

DDE Reporting

SysCAD values can also be exported / linked using Dynamic Data Exchange. See DDE for details.

Examples and Other Uses

Example SysCAD Reports

  • As part of the SysCAD install, some Example SysCAD Excel Reports are included. Please find these stored in the \SysCAD9x\Examples\01 Example Reports folder.
  • Report Templates and Default Reports are available in SysCAD 9.3 Build 137 or later,
Default Reports
For every project, the first time the Excel automation command is access, two reports will be added automatically to the project\Reports folder:
  1. 02GeneralReport.xlsx, created using the SteadyStateGeneral.xlsx template.
  2. 04DetailedReport.xlsx, created using the SteadyStateDetailed.xlsx template.
DefaultReport.png
These reports are set up to run with most projects. (with minor input from user - define 4 elements to be included in the stream report.) Please see Default Reports for more information.

Obtaining Equipment & Pipe List for the Report Generation

When generating the reports, although it is possible to manually enter unit operation or pipe names into the reports, it is much easier to auto generate these lists from SysCAD.

There are a number of ways in which the user can get the lists generated:

  1. To obtain a complete equipment list for the project, from the Explore Window in SysCAD, right click on the word Graphics(xx), and choose the Copy Graphics, Tag List to Clipboard option, then paste them into MS Excel.
  2. To obtain an equipment list for a selected graphics page, from the Explore Window in SysCAD, open the Graphics(xx) tree, right click on the required graphics page and choose the Copy Tag List to Clipboard option, then paste them into MS Excel.
  3. To obtain an equipment list for a selected process unit type, from the Explore Window in SysCAD, open the Classes Tree, right click on the required equipment type and choose the Copy Tag List to Clipboard option, then paste them into MS Excel.
  4. To obtain an equipment list for a selected process unit type, while also reporting the graphics page information, for example all pipes, assuming all the pipes are named with prefix P_, type the identifier P_ into the Filter box on the Explorer window, then follow point (1) above.
  5. To obtain an equipment list for a selected graphics page, for example all pipes for Graphics page Leach, assuming all the pipes are named with prefix P_, type the identifier P_ into the Filter box on the Explorer window, then follow point (2) above.
  6. To get a list by manual selection from a graphics page, see Quick View
  7. Use the SysCAD_TagSelect keyword option and enter the required criteria. See SysCAD Tag Select Reports for more information.
  8. Running the General Information Report, this report will create equipment / pipe lists for easy report generation. (This is the old method, Method 1 is now the preferred option.)

Automatically Generate Report Template from Quick View

The user may use the Quick View window to automatically generate a template for either of the following types of reports:

  • A 'Tag Select' report with all of the required secondary tags (e.g. Qo.Qm, Qo.Qv, Qo.To, etc.) with the required tag select statement to generate a report for ALL units in a project.
  • A Table report for selected units or primary tags (e.g. P_001, P_002, etc.) and variables or secondary tags (e.g. Qo.Qm, Qo.Qv, Qo.To, etc.).

Please see Data for Excel Reports in Quick View for more details.

Starting and running SysCAD from MS Excel

A macro can be set up in MS Excel to start SysCAD. A command script file can be included in the macro to perform various commands in SysCAD. An example of the macro is given below.

Sub StartSysCAD()
Application.Save
RetVal = Shell("c:\SysCAD138\bin\syscad93.exe 'c:\SysCAD138\Examples\03UnitModels\Counter Current Washer Example.spf\CommandScripts\UsingExcelReport.ssc'", 1)
End Sub

NOTE: If the user has a programming background, they can set up visual basic code to drive SysCAD entirely from MS Excel if required. For example, this may be useful if the user is in the feasibility study phase where the SysCAD flowsheet can remain untouched and many sets of test cases need to be performed.

Example Excel report with the above macro can be found in distributed projects:

  1. SysCADxxx\Examples\01 Reports\Project Report.xlsm (This example was created for the \Examples\40 Nickel\Demo Nickel Copper Project.spf)
  2. General Projects: Counter Current Washer Project - CCD.xlsm
  3. Alumina Project: Digestion Example - Digestion.xlsm.

The macro contained in these workbooks allows the user to modify and create a simple command script file, then SysCAD can be started and the generated command script file is executed.

Alternatively, rather than using the Shell command and Command Scripts, SysCAD can be manipulated grammatically using the full SysCAD COM API, an example excel file is distributed with the standard install.

Location of the file is:

..\SysCADxxx\Examples\01Reports\Sensitivity Analysis(SysCAD9.3).xlsm

For further information, please see COM Automation.