Office reports (Word, Excel, PDF)

Vertec provides an Office-based, cloud-ready report generator.

Vertec Office reports consist of a combination of a Word or Excel template for the layout structure and a report definition in the form of Python code for generating the content.

Overview

• Registration of Office reports
• Structure of Word templates
• Structure of Excel templates

To create a Vertec Office report, it must be registered. The Word or Excel template is saved and the relevant Python code is inserted as the report definition.

The procedure is described in detail in the article Registering reports .

The data content of the report is calculated using Python code, which is stored as the report definition. How this code is structured is described in detail in the article Python code for Office reports.

If the data is to be output in Word or in a PDF, a Word document (.docx) is saved as a template.

Everything that is printed is located in so-called bands.

A band is represented in the report template by a bookmark. The name of the bookmark is also the band name. At the location where you want to create a band, create a sample text in the desired layout (e.g. a table, rows with tab, etc). Then select the text and click on  Insert - Bookmark....

A dialog opens, where you can specify a bookmark name. It is advisable to choose a name that identifies the content (not Band1, Band2). This will make it easier for you to customize the bands and expressions later.

The bands can be nested freely. Each report template has at least one main band that runs over everything. Therefore, after creating the template, you can select everything and enclose it with a bookmark. When a report is run, a band is printed once or several times depending on the whether or not it is a single object or a list. In this case, the band is printed as often as there are elements in the list.

The individual bands are controlled by band expressions, which are located in a text area highlighted by a comment. The syntax is as follows:

• The highlighted text begins with the name of the band to which the expression refers, followed by Exp, Cond, or Local, depending on the type of expression it is (see below).
• The code reference is :name. This is the field name of the relevant TableField.

For example, bndServicesExp:services is written as a band expression, the text is highlighted and a blank comment is added:

In the code, the keyword expense contains the desired objects that are to be displayed in the band. The following band expressions are possible (bndXXX means band name):

1. bndXXXExp: Band for main expression. If specified, the band value is calculated based on this expression. The expression refers to the root object of the report. If the result of this expression matches a list, the band is duplicated for each entry in the list.
2. bndXXXCond: Band for conditional expression. If specified, this expression is evaluated each time the band occurs (i.e. if the band is duplicated within a list, for each object). For the band to be printed, the result of the expression must be:
   • For Boolean values (True/False), the result must be True.
   • For integer, currency and minute values, the result must be <> 0.
   • For string values, the result must be <>'' be, i.e. not an empty string.
   • For date and image values, the result must be <> null.
   • In general: not null/not zero = True, null/zero = False.
   • For table-fields (Tablefields ), the name of the field can be entered directly as the conditional expression. If the table is empty, the band is not printed, e.g. bndHasServicesCond:services, where services is the name of the table-field.
     If you want to print a band if it has no entries (e.g. to indicate that no entries were found), you can reverse the expression with not and the field name: bndNoServicesCond:not services.
   • Context variables can also be indicated as the conditional expression (from version 6.2.0.5). The syntax is as follows: bndXXXCond:context:variable.

     

     A band cannot have Exp and Cond expressions at the same time. If both are desired, two bands must be made, with the Cond band enclosing the Exp band.

3. bndXXXLocale: Numeric values are formatted according to the locale of the local Windows. Locale expressions allow all numeric values within the band to be formatted according to a different locale. There are two ways to control this:
   1. A reference to the code is given, which results in a country code. In the code, a OclTextField with the given name (in this example locale) is inserted in the corresponding table:

      

      The result of the given OCL expression must be a country code. This can either be specified directly, as in the example above (“de-DE”), or can be read from a field: OclTextField("locale", "projekt.zusatzfeldasstring('locale')").
   2. The country code is specified directly, e.g. bndServicesLocale:de-DE

A table with the country codes can be found at: https://msdn.microsoft.com/de-de/library/ee825488(v=cs.20).aspx

Bookmarks that are not deleted

Bookmarks whose name starts with trg or src are not deleted by the Vertec report mechanism when executing the report. So if you need bookmarks in your document that remain after execution, call them trgXXX or srcXXX.

Band definitions

Bands hierarchy levels

Start and end of a band must always be on the same (hierarchy) level.

For example, if a band starts at the end of a paragraph that contains text but only includes the paragraph mark, this is not actually valid. For the report to work anyway, the band start is interpretatively moved forward to the beginning of the next item (paragraph or table):

This automatic adjustment results in a paragraph change less displayed. This is visible in the output.

If possible, such bands should not be defined, but preferably whole sections or start and end within the text should be defined as bands.

If the start of the band is in the middle of the paragraph text, as shown in the figure below, an error message will appear regarding invalid band definition.

In general:

The start and end of bookmarks can be viewed from the menu Insert > Bookmark, select the bookmark and click Go To.

Indistinguishable variants

There are cases of band definitions that are indistinguishable from the code, although they are visually distinguishable in Word.

This is the case, for example, if a bookmark ends at the end of a blank paragraph that follows a table:

In this case, the end of the band is inside the empty paragraph. If the band ends at the end of the table, i.e. does not contain the empty paragraph, then the end of the bookmark is also in the following section.

Therefore, bands that end in an otherwise empty paragraph are always interpreted as the end of the preceding table.

If you want a blank paragraph after a table to explicitly belong to the band, this can be done by inserting some text (e.g. a space) in the paragraph. Then the paragraph is interpreted as belonging to the band.

Bands that start in a blank table row

If a band starts at the beginning of a blank table row, at least one space must be inserted in that table row:

Otherwise, an error message of the type: Invalid overlapping bands bndXY and bndYZ will appear.

A field is text marked by a comment within a band that is replaced by data from Vertec when the report runs. The marked text can contain the reference to a field defined in the Python code, as well as the OCL expression for a member directly. OCL member expressions are case-sensitive, and a business class must be specified in the table represented by the band, see table declaration .

In Word, write the field name where you want to insert the data from Vertec later, mark it and click Insert - Comment.

The comment itself:

• is empty, if the text represents a field name in the code or an OCL member
• contains the keyword translation, if the text is a term to translate

The prefix sum: can be used to sum up field values. The following example sums up the values of the field fee in the table services:

sum:services.fee

Summation is also possible across multiple table hierarchies:

As an example, there is a table on a service report, which makes a grouping by service type (name of the table: servicetypes) and a sub-table with the list of services for this type (name of the sub-table: services).

If you now want to output the total of all services outside the service type band, you can do this with the following expression:

sum:servicetypes.services.fee

This field must be outside the first table referenced (servicetypes), in a location where its expression would be known and would return the table (which means in the parent band of servicetypes):

By using so-called context expressions, context variables can be output directly on the report without a field declaration.

A context expression is identified by the prefix context: and references a context variable of the report. Example:

context:currentdate

The data type of the field depends on the value of the context variable. If the context variable is an object reference or a list, the context expression returns a string (string representation) as the value.

Context expressions in headers and footers

Since comments are not available in headers and footers, the variables here are written in double curly brackets, for example:

{{context:currentdate}}

To translate a field, enter the keyword translation as a comment. The translation mechanism then looks in the translation file for the term and, if available, uses the corresponding translation.

The selected term to translate must be known by the Vertec translation system . Terms that are used exclusively by you and are not known by default must be added as translation items in Vertec.

Translations in headers and footers

In headers and footers of Office reports, translations can be written in double braces. The term then follows after the colon:

{{Translation:Page}} X {{Translation:of}} XX

Images from the database are declared in the Python code as ImageField or OclImageField. They can be inserted in the report document as normal data fields (commented text). It also supports the use of PDF documents as images, showing the preview of the first page.

It is not possible to specify an image path, the image must come from the database, e.g. the company logo  in the system settings.

To control the size of the inserted images in Word, it is recommended to display them in a table, see examples below.

The inserted images will not be scaled up, either horizontally or vertically. However, the images will be scaled down if necessary:

• If an image is wider than the table cell, it is scaled down when the table option Automatically resize to fit contents is turned off:

  

• If an image is higher than the table row, the row is enlarged, unless the row height is set to an exact value in the table properties. If the row height is set to an exact value and Automatically resize to fit contents is turned off, the image is scaled down to fit the cell.

  

As HTML-formatted text, e.g. from text blocks, Python Code declared as TextField or OclTextField. It is inserted in the report document with the prefix html:.

Example field declaration:

 OclTextField('textblock', "zusatzfeldobj('textblock').oclAsType(Textbaustein).text"),

In the template:

The value is interpreted as formatted HTML text and the result is written to the report.

It supports (with some noted exceptions) the elements described in the Text blocks article, including OCL expressions calculated within HTML.

From Vertec 6.3 onwards, Office reports can also be output in Excel format. The underlying Python code is the same as for Word reports; the only difference between the two formats is the template.

Bands

To set a band, select the appropriate cells and paste the band name in the box at the top left.

There is always a main band that includes everything and refers to the main object, i.e. the main table.

The other bands resp. sub-bands are set equal:

Important: When setting the bands, make sure not to select the entire rows (infinit to the right), but only the cells that are actually needed. Otherwise, the report will take much longer to show up because Vertec has to iterate through all the cells to find out if a value is set anywhere.

If you want to delete or rename a band, open the Formulas > Name manager:

Band expression

There are two types of band expressions: Cond and Exp bands.

• The text begins with the name of the band to which the expression refers, followed by Exp or Cond, depending on the type of expression (see below).
• The expression (reference to the data) is given as :name

For example, write bndProjectExp:projects as a band expression, mark this text and add a blank comment.

Excel automatically inserts the author when you create a comment. It is important that you leave it and do not delete it. An “empty” comment does not mean a completely empty comment, but one with only the author in it:

Note or comment?

In Excel versions from Office 365, there are “notes,” which replace the “comments”. Comments are still available, but are now a kind of thread where several users can talk about them.

If both are available, use “notes” for the Vertec templates.

Fields

A field is text within a band that is highlighted by a comment and replaced by data from Vertec when executing the report. The highlighted text contains the reference to the field defined in the Python code. The comment itself is empty (see above) or contains the keyword translation to activate translation for that field.

Only one term can be set per cell at a time, so you cannot set a field and a band expression in one cell at the same time. In the example report above, this was solved by inserting the band expression in the cell at the end of the row and widening the band by one column.

Context variables

Context variables are also supported. Instead of a Python field, the cell contains the text context:, followed by the corresponding variable:

Data types in Excel

In contrast to Word, there are different data types in Excel. Automatic recognition in Excel is not always useful. Therefore, in the Excel template, a cell can be provided with the desired format. When the data is pasted or the cell is copied down, the format is also copied.

Minute fields

In Vertec versions prior to 6.4.0.22, minute values are exported in the format defined in the system settings Display minutes .

Starting with Vertec 6.4.0.22, the format Hours:Minutes is no longer taken into account, but exported as Hours.Decimal with 2 decimal places. Minuten is still exported as minutes.

Summation

Summation in Excel works with the usual Excel formula =SUM(band name).

Row totals usually are made with the cell references:

The following guidelines apply to the totals in the columns:

• All Excel operators are accepted, but the format must be =Operator(band name) or =Operator(cell reference)
• The formula must be outside the band referenced by the sum, but within the band that includes it, i.e. one level lower.
• The column in which the formula is located is summed.
• Subtotals are possible, but only within the same record.
• Summing all subtotals in the same column is not supported.

Because of the formatting of the data types (see the Data types in Excel section above), cell references in the template may display “A value used in the formula is of the wrong data type.”

This is because the referenced cells contain texts like leistwertint, leistwertext, which cannot be summed. After the report is generated, the summation works because the cells now contain numerical values.

Summation over multiple levels

Summing cells in Excel reports via band totals sums all cells of a column within a band.

In many cases, you want to sum individual cells of the bands, away from the sum by several hierarchical levels.

Starting with Vertec 6.4, when summing only one cell, a sum is made over all copies of those cells.

Example

The summation refers to the total cell

On execution, a sum is made over all copies of this cell: