Some of the EQuIS Geotech reports require use of Microsoft Excel template files. Out-of-the-box Excel templates are provided with EQuIS Geotech, but users can format the layout and graphic data tables as desired.
The EQuIS Geotech reports contain multiple queries that are classified under a defined “field name”. This field name can be referenced in an Excel template through the built-in Excel Name Manager functionality. The queries gather data within the selected task_code and report the data in the Name Manager defined cell(s).
This documentation is specific to the following Geotechnical reports:
•CPT - Pore Pressure Dissipation
•Gradation (Hydrometer) - ASTM D7928
•Gradation (Sieve) - ASTM D6913
Creating a Report Template in Excel
The EQuIS Geotech report templates use the Name Manager functionality in Excel. The Name Manager allows users to specify where in the template they would like their defined name values to be populated. Each geotechnical report has a set of defined Name Manager fields specific to them. A list of the available names and their descriptions are outlined in each respective report documentation. The shared field names are available to use on the EQuIS Geotech reports unless otherwise stated in the specific report documentation.
Name Manager
To manage names in the Excel workbook, the user must navigate to Formulas Name Manager in the ribbon. In the Name Manager dialog window, the user can add, edit, or delete the workbook defined names. More information on defining Named Ranges in Excel can be found here: https://support.microsoft.com/en-us/office/use-the-name-manager-in-excel-4d8c4c2b-9f7d-44e3-a3b4-9f61bd5c64e4.
Name: The defined field name.
Value: The value in the cell the defined “name” is populated.
Refers To: The cell(s) where the defined “name” is populated.
Scope: The worksheet name.
Template Requirements:
•Template worksheet must be called “Report”.
•Name Manager fields must be assigned to Scope “Report”. Make sure no ‘REF!’ errors are present in the Scope.
•User can format cell(s) to display number in a certain manner (i.e., 2 decimal places)
Things to pay attention to:
•Text present in a cell will be merged with the name manager field.
•Make sure the cells are unmerged prior to applying a name range to a range of cells.
•Excel conditions can be set for a cell. (IF ___ is empty, then print___)
Syncfusion Report Known Issues and Workarounds:
•Must apply the axis bounds in plots; not have it set to auto.
•No embedded shapes in graphs.
•Do not merge cells that are assigned a ‘symbol’ (data marker).
•Workaround if axis is not respected: Add invisible lines to the graph template.
Modifying a Standard EQuIS Geotech Report Template
Note: A hidden sheet is used to construct the constant lines on the plots in the Geotechnical Atterberg Limits report and the Geotechnical Grain Size Analysis report. |
Repeating Values
To repeat a named range to a different set of cells, use a "question mark" to keep the named ranges unique. See the image below as an example.
