1.General

To ensure the report data is presented attractively on the web and in Excel, there are various independent formatting settings. These can be entered per column, per row, and even per individual cell.

Since the desired result – especially with Excel export – is not always achieved on the first attempt, a check in Excel is essential.

2. Formatting on the web

2.1 General – Syntax

The details for displaying the report can be defined in the property fields described below.

Syntax rules:

1. Multiple properties can be specified separated by a semicolon (;).

2. The properties are case sensitive.

3. No spaces may be used.

4. Assigning a value to a property is done using a colon (:)

Example: borderLineColor : blue ; BorderLineWidth : 5

2.2 Frequently used properties

The most commonly used properties are the following:

color: assigns a new color to the text.
background-color: assigns a new background color to the cell.
font-family: assigns the font.
font-style: assigns the attributes bold, italic or underline to the font.
font-size: assigns the font size in pixels.
border: specifies the line width, line type and line color for the cell border.

The border property can also be further refined.
First, you have the option to specify which of the four lines (top, left, right, bottom) should appear. Furthermore, you can also set the width, style, and color properties individually.

The page https://www.w3schools.com/css/css_border.asp provides the necessary information.

2.3 Colors

Either the HTML color code (e.g., blue) or the number code with # can be used. (e.g., #F0F8FF)

Examples of color codes: http://www.w3schools.com/colors/colors_names.asp
Color picker for creating any color code: http://htmlcolorcodes.com/

2.4 Lines

Unless otherwise specified, the default line style is "solid." Other possible values are:

dotted:                dotted
dashed:                dashed
solid:                     continuous line
double:                double line

2.5 Setting the formatting in the column scheme

In the report's column scheme, you'll find the settings for formatting on the web in the "Columns" tab, almost on the far right. There are two different columns in the overview: one for formatting the heading, and the other for formatting the values:

Formatting can then be entered into these columns according to the HTML style definition. Leaving the dataset saves the changes, and you can view the changes directly by accessing the report in the web client or, if the report is open, by refreshing it.
+

2.6 Setting conditional formatting in the column schema

It's also possible to specify formatting based on the content (value) of a cell. However, this isn't visible in the column overview of the column scheme. The setting is "hidden" in the column details. Alternatively, you can display the columns by right-clicking the column names and selecting "Select Columns." Then add the columns "Display Conditions" and "Conditional Formatting for Values."

To access it, place the cursor on the row in the column scheme that you want to edit and then click on the “Details” button:

In the window that opens, switch to the "WebPortal" tab. There, enter the settings in the "Display Conditions" and "Conditional Formatting for Values" fields:

The procedure is as follows:

One or more conditions are entered in the "Condition for display" field. Individual conditions are separated by the pipe symbol (|).

In the "Conditional Formatting for Values" field, multiple formatting options for the values (always one more than the conditions) are entered. These are also separated by the pipe symbol.

The functionality is then as follows:

If the first condition is met, the first formatting is applied; if the second condition is met, the second formatting is applied, and so on.

The final formatting is then applied to the values for which no previous condition was applied. Therefore, you should always enter one more formatting than conditions.

2.7 Setting the formatting in the row scheme

Formatting in the row scheme is analogous to formatting in the column scheme. The columns in the "Rows" tab are again located almost to the right of the column list:

The difference here is the naming of the columns. The functionality is the same.
The “Formatting Description” column specifies the formatting of the row label, the “Formatting Values” column specifies the formatting of the values in the cells of this row.

IMPORTANT! Conditional formatting is not possible in the row scheme.

2.8 Rules for applying formatting

If formatting is applied only to the column schema or only to the row schema, there will be no conflicts. However, if formatting is applied to both the column and row schema, the following rules apply to determine which property is applied:

If different properties are stored in the row and column schema, there is no problem because the properties cannot overwrite each other.

However, if the properties (e.g. color) are the same on the row and column schema, the following sequence is used to resolve a conflict between different values of the same property:

1. Properties from conditional formatting

2. Properties from the row schema

3. Properties from the column schema

2.9 Overriding cell-level formatting

Despite knowing the formatting rules, it is sometimes necessary to override the formatting of an individual cell. This is possible by switching from the report scheme to the cell contents:

In this overview of the cell contents, you must first switch to the correct view. When opened, this is always set to "Formula":

The "Conditional Display" and "Conditional Value Formatting" views are responsible for setting the formatting in the Web Client. These are the same settings fields that are also used for conditional formatting in the column schema.
With the “trick” of leaving the “Condition for display” field empty, the formatting of the values is applied to all values in this cell.

It also makes formatting easier to name the columns appropriately in the column scheme. If you don't do this, you'll end up with a page where only the rows are labeled, and no other cells are clearly visible because the column headings are missing.

The existing formatting created by the row or column scheme can be easily overwritten. The text color of an overwritten property changes from gray to black.
If an overridden property is removed, the value of the property generated by the row or column schema is automatically re-entered. This value is then entered in gray. Therefore, the condition for the display cannot be deleted from the cell. However, this problem can be easily circumvented by entering a condition that always returns "true" in the cell. The simplest condition for this is "1=1" or "true."

This allows the formatting to be assigned to each individual cell.

3. Formatting in Excel

3.1 One-time basic setup of format templates

Under Controlling Module / Setup / Basic Setup, the settings for the template report and the paths to the Excel templates as well as the path for automatic output must first be set in the “Reports and Preview” tab:

Additionally, a template report must be created:
This essentially consists of a column and row scheme in which the respective formatting is entered. However, this template report is not assigned to a report group because it does not contain any meaningful data. Here is how to set up such a template report.

Reporting scheme:

As already described, the report group remains empty.
The descriptions can be used to set the report header that is printed on the Excel register.

The path to the Excel template is entered in the "Excel" tab. The path can be abbreviated with the placeholder [TEMPLATEDIR] if the UNC path for the Excel template was previously set in the basic settings:

Column scheme:

The formatting of the individual values is then defined in the column schema:

In this example the 4 possibilities are listed:
The first row defines the formatting of all values if no further settings are made. It's important that the column number, heading, amount type, and suffix are empty.

The second line defines the formatting of the values when the percent sign is set as the suffix.

The third line shows the option to define formatting based on the amount type. However, this formatting is only valid for this one amount type.

The same is also possible depending on the column number (4th row in the example) or the heading. To do this, a column number or heading must be specified.

Line scheme:

The row scheme takes on special significance in this case. The description defines how the row labels are displayed in Excel.
A line label generally consists of a code and a corresponding description. This can be, for example, an article number and the article name.

The description now specifies which Excel column the code and description should be placed in during export, and how they should be formatted. The Outline Level field is empty. This is visible in the first two rows of the graphic below.

In the next row you then specify in which Excel column the code and description should be written if the row is part of an outline and the outline level is not the top level.
In the graphic below, this is visible in the third and fourth rows. The Outline Level field has been filled with 1, meaning this applies to the first outline level. Since no further outline levels are defined, this also applies to all subsequent – deeper – outline levels.

3.2 Requirements for Excel export

For the data to reach Excel, Excel export must be enabled for the respective report. This is done in the report's column scheme on the "Excel" tab. There, the "Excel download" checkbox must be checked:

If no further settings are made, the formatting settings from the template are adopted.

Otherwise, an Excel template can be specified in the individual report, which will then be used instead of the default template. This is especially advisable if the report needs to use formatting that differs from the default template.

3.3 Creating a style template

The easiest way to create a formatting template is to export the report to Excel and then manually format the report as desired. This created file then serves as the template for the report.

To do this, the tab that contains the formatting must be renamed to “LAYOUT”.

Additionally, an empty tab must be inserted into the template file. Name it "TEMPLATE."

The file must then be stored in the template directory.

3.4 Setting the formatting in the column scheme

Once the basic settings have been made and a format template has been created, you can now start formatting the individual columns by assigning Excel cells from the template.

To do this, go to the report's column scheme. If you scroll to the right in the "Columns" tab, you'll find the "Excel Format Description" and "Excel Format Value" columns:

The “Excel Format Description” column is for formatting the headings, the “Excel Format Values” column is for formatting the values in this column.

For formatting, enter the cell numbers (e.g. B12) or cell ranges (e.g. B10:B12) into the individual fields.
If only a single field is entered, all cells in the column are formatted the same.
However, if a range is entered, in the case of an outline, the cells of the first level are formatted like the last cell in the range, the cells of the second level like the second-to-last cell in the range, etc. When a level is reached for which there is no longer any formatting, the first cell in the range is used as the format template.

The following example illustrates this. For clarity, the section of the style sheet used for formatting has been copied into the example.

A "Values" column was assigned the value "G4:G6" in the "Excel Format Values" field. For this purpose, the row scheme contains a structure that spans four levels. When exported to Excel, the rows are then formatted as follows:

The values of the first outline level (B4) are formatted like cell G6 in the style template.
The values of the second outline level (B5) are formatted like cell G5 in the style template.
The values of the third outline level (B6) are formatted like cell G4 in the style sheet.
Since there is no longer any formatting for the fourth outline level or no formatting has been specified, the formatting of the third level (cell G4) is used for this and all other levels below it.

3.5 Setting conditional formatting in the column schema

It's also possible to specify formatting based on the content (value) of a cell. However, this isn't visible in the column overview of the column scheme. The setting is hidden in the column details.

To access it, place the cursor on the row in the column scheme that you want to edit and then click on the “Details” button:

In the window that opens, switch to the "Excel Formatting" tab. There, enter the settings in the "Display Conditions" and "Excel Conditional Formula for Values" fields.

A special feature here is that the formatting condition is the same both on the web and in Excel. Therefore, it's not possible to define a different display condition for Excel than for the web.

One or more conditions are entered in the "Condition for display" field. Individual conditions are separated by the pipe symbol (|).

In the "Conditional Formatting for Values" field, multiple formatting options for the values (always one more than the conditions) are entered. These are also separated by the pipe symbol.

The functionality is then as follows:

If the first condition is met, the first formatting is applied; if the second condition is met, the second formatting is applied, and so on.

The final formatting is then applied to the values for which no previous condition was applied. Therefore, you should always enter one more formatting than conditions.

3.6 Setting a formatting in the row scheme

Formatting in the row scheme is analogous to formatting in the column scheme. The columns in the "Rows" tab are located in the middle of the column list, right after the display conditions:

The “Excel Format Description” column sets the formatting of the row label, the “Excel Format Value” column sets the formatting of the values in the cells of this row.

As before in the column scheme, the Excel cell or range from the format template is entered here again.

IMPORTANT! Conditional formatting is not possible in the row scheme.

3.7 Rules for applying formatting

When formatting values in Excel, the same rules apply as when formatting on the web.
The only difference, however, is that the column formatting takes precedence over the row formatting.

3.8 Overriding cell-level formatting

Despite knowing the formatting rules, it is sometimes necessary to override the formatting of an individual cell. This is possible by switching from the report scheme to the cell contents:

In this overview of the cell contents, you must first switch to the correct view. When opened, this is always set to "Formula":

The "Format Detail Values" view is responsible for setting the formatting in Excel. This is the same settings field that is also used for formatting in the column scheme.

It also makes formatting easier to name the columns appropriately in the column scheme. If you don't do this, you'll end up with a page where only the rows are labeled, and no other cells are clearly visible because the column headings are missing.

The existing formatting created by the row or column scheme can be easily overwritten. The text color of an overwritten property changes from gray to black.
If an overridden property is removed, the value of the property generated by the row or column scheme is automatically re-entered. This value is then entered in gray. Therefore, the display formatting cannot be deleted from the cell. The only option is to specify a different, differently formatted range in the template to overwrite the unwanted formatting.