Report output formats

XLSX format

XLSX Excel format can be used for all reports and lists available in the Create Report function.

Generating an XLSX format report requires that a report layout file (.rpt) and a matching Excel template are present in the report subfolder in the active norms folder %ncgnorms%. For example, to generate a part list, a part-list.rpt report layout file and a part-list.xlsx Excel template file are required. See Excel template and Report layout for the XLSX format.

XLSX report generating process

The default norms contain some report layout files and matching template files. These can be used as the basis for customized layouts and templates.

Excel template

The Excel template can contain up to five sections, each containing different information. Most sections are optional. The only mandatory section is the data row. Formulas are supported in all sections.

Sections in the Excel template

An excel template can have the following sections:

  • Header – All the rows between row 1 and the first cell with a note are considered as header rows (the first cell with a note belongs to the sub-header section). The header is an optional section.

    The number of columns is not limited.

    A header cell can contain an image.

    The header is visible in the report only at the top of the first page.

  • Sub-header – The sub-header starts with a cell containing a headerstart note, and ends with a cell containing a headerend note. The sub-header is an optional section.

    Images cannot be inserted in sub-header cells.

    The sub-header is visible in the report at the top of the first page just below the header, and it is duplicated at the top of all the other pages.

  • Data row – The data row contains expressions consisting of keywords, and optionally formatting codes, as notes. The data row is a single row that is duplicated for every record of data (construction part). A template file must have this section with at least one data cell in it.

    The expressions in the data row can contain the following: logistical database field names, all derived data fields except the weld-related ones, a number of keywords called global fields, and free text between double quotes. See Expressions and keywords below.

  • Sub-footer – The sub-footer starts with a cell containing a footerstart note, and ends with a cell containing a footerend note. The sub-footer is an optional section.

    The sub-footer must be placed exactly below the data keywords row. It is not allowed to have an empty row between the data keyword row and a sub-footer row.

    Images cannot be inserted in sub-footer cells.

    The sub-footer is visible in the report at the bottom of the first page, and it is duplicated at the bottom of all the other pages.

  • Footer – All the rows after the sub-footer (or data keywords if there is no sub-footer) are considered as footer rows. The footer is an optional section.

    The number of columns is not limited.

    A footer cell can contain an image.

    The footer is visible in the report only at the bottom of the last page, below the sub-footer.

 

Note: All the sections except the header and the footer require at least one note. In the example above a small red triangle indicates that the cell has a note. The system handles notes as case-insensitive.

Expressions and keywords

The expressions placed in the notes in the cells consist of elements. It is possible to use a combination of several elements, even different types of elements, in one note. When using multiple elements in one note, the keywords must be separated by a space character.

The elements that can be used in the expressions are as follows:

  • Logistical data fields – These are keywords that return the corresponding value from the logistical database for the construction part in this row. All logistical data fields can be used.

    Note: Logistical database keyword description can be used in the data row. The contents of description for the different construction parts is defined in the settings under System Management > Logistics > Description. In the description settings for Profiles, Shell Frames, Face Plates and Alternatives, it is possible to use the PROFNAME variable in the description. In Excel reports the system replaces the value of PROFNAME with the value of the ProfileName variable. ProfileName for the different profile types can be defined in System Management > Presentation > Profiles > Dimension Text, in the Name definition column.

  • Derived data fields – These are keywords that return a value derived from various data records in the system (the construction database for example) regarding the construction part in this row. All derived data fields except the weld-related ones are supported in Excel (XSLX) reports.

    • AttRecNumber – The attribute record number of the part, or 0 if the part has no attribute record.

    • BlockGroup – The block group that the construction part on the report row belongs to

    • GrossWeight – The gross weight of the construction part. For plates and brackets, the gross weight is the multiplication of the part's length, width, thickness and material density. For other parts, the gross weight is equal to the net weight.

    • LogRecNr – The logistical record number of the part

    • MaterialType – The part prefix set for the construction type. These prefixes are set in System Management > Logistics > Prefixes And Part Codes in the Report & sketch setting column.

      • If the part is a face plate, bent profile, profile as property or shell frame, the prefix for the profile type is used instead.

      • If the part is a shell plate, and no prefix is set for shell plates, the prefix for plates is used instead.

      • If the part is a lug, and no prefix is set for this type of lug, ST is used instead.

    • NumberColumn – The number of rows that were united (combined) into this row

    • Partcode – The part code of the construction type of the part. Part codes are set in System Management > Logistics > Prefixes And Part Codes in the Part code setting column.

      • If the part is a face plate, bent profile, profile as property or shell frame, the part code for the profile type is used instead.

      • If the part is a shell plate, and no part code is set for shell plates, the part code for plates is used instead.

    • PlateBrAttType – The type of the part. The part types are indicated as follows:

      • Plates: P520

      • Shell frames: P2

      • Shell plates: P3

      • Brackets: P<bracket type number>

      • Attributes: A<attribute type>

    • PlateRecNr – The plate record number of the part, or that of its parent plate-like part if the part has no plate record itself.

    • ProfileName – The name definition of the profile type as defined in System Management > Presentation > Profiles > Dimension Text

    • ProfileType – The profile type (ST, HP, HG etc.) and the profile's length and width dimensions, for example ST120x10 or HP180x8

    • TargetBlock – The block to which the profile part of a pillar-profile belongs. This field only applies to pillar-profiles. For all other parts, it is empty.

      Note: TargetBlock has a special meaning when included in a report's part selection criteria in a Logistical Selection Menu. With it, it is possible to define which pillar-profiles are presented in the report if they are connected to one of the blocks to be presented in the report.

    Note: Derived data fields can only be used in the data row section of the template.

  • Global fields – These are keywords that return a specific value as described below.

    • activeblockgroup – The block group of the block that is open when the report is created

    • activeblockname – The block name of the block that is open when the report is created

    • activeblocknumber – The block number of the block that is open when the report is created

    • blockgroups – A comma-separated list of the block groups included in the report

    • blocknames – A comma-separated list of the block names included in the report

    • blocknumbers – A comma-separated list of the block numbers included in the report

    • currentdate – Current date. The default format is D-M-Y. This can be changed in System Management > Production > General > Current Date Format.

    • currentdatetime – Current date and time. The default format is D-M-Y h:m:s. This can be changed in System Management > Logistics > Field Formats > Date/Time Format.

    • currenttime – Current time. The default format is h:m:s. This can be changed in System Management > Production > General> Current Time Format.

    • object – The object number of the project (used as a unique identification of the project)

    • projectname – The project name

    • user – The user name of the user generating the report

  • Free text between double quotes – Segments of free text can be added to the notes by placing each segment between double quotes.

Formatting of the keywords

Field format codes can be used to format those keywords that have a numerical value. See Defining field format codes for more information on the field format codes.

Logistical database field part, followed by free text space (, followed by logistical database field weight, followed by free text ):

part " (" weight ")"

Logistical database fields part and weight separated with a space character and formatted with a field format code:

part.32.-5.0.0 " " weight.32.0.2.0

Data cell with a field format code

Weight is the name of the logistical database field, and 32.0.03.0 is the field format code. In this example a dot is used as a separator character between the logistical database field name and the field format. A space can also be used as the separator character.

Formulas in the Excel template

Formulas are supported in all sections of the Excel template file. Formulas typically contain references to data in a cell or a range of cells.

Three types of formulas can be used in the Excel template:

  • Formulas that are duplicated in each row. Formulas of this type must have a byrow note in the cell. In the image above SQRT(E2^2+F2^2+G2^2) is a formula of this type.

  • Formulas that are duplicated on each page. These formulas can be used in the sub-footer and sub-header sections. In the image above MAX(D$2:D2) is a formula of this type.

    Note: The SUBTOTAL() function should be used in formulas in the sub-header and sub-footer sections to avoid duplication in calculations. In Excel, formulas with the SUBTOTAL() function are automatically excluded from other recursive formula calculations. For example, for calculating the sum of D2 per page you should use SUBTOTAL(9; D$2:D2).

  • Static formulas. Formulas of this type are not duplicated. They are used outside the sub-footer and sub-header sections. These formulas do not have a byrow note in the cell. In image above SUM(D$2:D2) is a formula of this type.

Important: Make sure that the range of a formula is well-defined and the dollar sign (indicating absolute reference) is correctly used. For example, references A1, A$1 and $A$1 have different interpretations. In the example above, the dollar sign in SUM(D$2:D2) is used correctly. SUM($D$2:D2) would also be correct. Other variations would not result in a correct outcome.

Refer to Microsoft Excel help for information on how to use cell references.

Report layout for the XLSX format

A report layout file <layoutname>.rpt in the report subfolder in the active norms folder %ncgnorms is required for each report type.

The system uses the following settings in the report layout file when generating an XLSX format report. The settings are defined in System Management > Production > Reports > Report Layout.

  • The group setting in the [SETTINGS] section of the report layout file defines the number of data rows after which an empty row is added to the report. No empty row is added when this setting is set to zero, or when this setting is not present in the report layout file.

    In System Management this is the Group lines setting in the General settings of the report layout.

  • The lines setting in the [SETTINGS] section of the report layout file defines the total number of data rows that are placed on one page when the report is printed.

    In System Management this is the Lines per Page setting in the General settings of the report layout. This setting has no effect if Page height is defined.

  • The pageheight setting in the [SETTINGS] section of the report layout file defines the page height of the printed report.

    In System Management this is the Page height setting in the General settings of the report layout.

  • The sorting rules in the [SORTING] section of the report layout file. The system allows recursion in sorting rules.

    In System Management the sorting rules are defined in the Sorting settings of the report layout.

  • The unite rules in the [UNITE] section of the report layout file. When multiple data rows fulfill the unite rule, the report will have only one combined row for this data. Other rows which contain the same data are skipped.

    In System Management the unite rules are defined in the Unite settings of the report layout. The system applies the selected summarize action to the united data.

See Report Layout for more information on these settings.

Notes on printing the XLSX report

For the best print results, make sure the that the printing properties like paper orientation, margins and the initial page break are properly set.

The system inserts page breaks according to the Page height setting, at points where the set height of the page is reached. When Page height is set to zero, or is not set, page breaks are inserted according to the Lines per Page setting, at points where the set number of lines (rows) is reached.