Defining Simple Report Template

This topic describes the items that must be defined to create a valid template. The Simple Report example template (EX_RT101), which is a simple report template with only one sheet and one area, is used to illustrate the process. More complex templates are described in the following chapters.

Basic concepts of an input or report template:

• sheet: the sheets of the Excel workbook

• area: portion of a sheet where a specific set of data is shown

• definition column: columns telling what data is shown on the row, for example account code, unit name

• data column: columns showing the facts data, for example euros, pieces or percentages

• dimension: data classification factors used to limit the data retrieved (to a row, column or the entire template)

The first thing to do when creating a new template is to plan the data content and layout. The example template shows actual figures for two months and the change between months. The following figure depicts how it looks when opened in Clausion.

Workbook

The workbook looks like the following:

It has the following named cell definitions:

Colouring in the workbook is for clarity only and does not affect the functionality of the template.

In the Simple Report example template (EX_RT101) the report header (in cell A1) is entered in the workbook by using input and report template variables that will get their values from the user's selections when opening the report. Cells A1:E1 are merged to center the text across the width of the report.Column headers are defined in the XML file and that is why we do not enter column headers in definition columns A and B. In the data columns C to E we enter column headers '1', '2' and '3' for clarity purposes. They will be overwritten by the headers defined in the XML file when opening the report in Clausion.

Definition Columns, Data Columns, Area and ACCCODE Definitions

The workbook must contain the sheets and areas defined in the XML file and they must have the same names as are used in the XML file. Additional sheets and areas in the workbook will be ignored.

For the workbook and XML file to work together, column A of the data area must be named with the same name as the area name in the XML file.

The cell on the first row of Area1 following the last data column must be named ACCCODE1 (note the three C letters in ACCCODE1).

The defined area must include columns corresponding to the definition and data columns specified in the XML file. Columns A and B are definition columns showing account code and name respectively. Columns C–E are data columns, showing figures for December and November and their difference.

the Simple Report example template (EX_RT101) the data area, called Area1, consists of cells A3:E6.

Though the area stretches over several columns in the workbook, in the Name Manager it is defined as A3:A6.

The dimensions specified:

• account for definitions columns A–B

• year, month, data type, and document series for data columns C–D

Year, month, data type, document series, and unit are selected by the user when opening the report. Column E is not directly limited by dimension selections but is calculated from the other data columns.

Row Sets

Row sets are subsets of input or report template rows. They can be used to view a predefined combinations of rows in the input or report template. Row sets are sheet-specific. If the input or report template contains several sheets, you need to define row sets separately to all sheets on which you want to use them.

In the Simple Report example template (EX_RT101) there are three row sets. Columns immediately following the ACCCODE column are for row sets (columns G to I).

1. Add as many columns as you need. Row sets must be named on first row of the area ('Items', 'Sum' and 'All'). This name is shown as the row set name in the Row set drop-down list in the input or report template. The same name is used in all languages.

2. The next empty column marks the end of row set columns.

3. Type a character (for example, X) on the rows that you want to include in the row set. Any characters except special characters are allowed. The same row can be included in multiple row sets.

You can also define row sets with formulas, including IF formulas which make it possible to create row sets dynamically on generated templates. On generated templates, row sets are copied to all generated blocks. In 'retrievefromdata' type templates, the formula must be copied to as many rows as you expect to be the maximum data rows retrieved on the template.

When you define a row set for a sheet, the application adds the Basic row set option to the drop-down list. It includes all rows and is the default row set. You can change the default row set in the user interface task settings. It is also possible to define whether users can change the row set.

• Row sets cannot be used in 20 000 and 70 000 document series input templates, drill-down and TR templates, custom templates, or external workbooks.

• The 'Hide zero rows' function works in combination with row sets. If 'Hide zero rows' is set for the sheet, all zero rows are hidden even if they are included in the selected row set.

• Header rows of the areas are always shown.

• Rows that are not included in any area (including empty rows) are not affected by row set settings.

• Row sets with formulas based on a generated data column do not work.

• The ACCCODE naming in the template must follow the order of the sheets; ACCCCODE1 in first sheet, ACCCODE2 in second sheet and so on.

• If there are several areas on the sheet, they must include the same number of columns (total of definition columns and data columns). Otherwise row sets do not work for the areas.

Language Translations

You can add language translations for all definition columns, as well as title rows that are included in areas defined in input and report templates.

Language translations can be used only in 'predefined', not 'retrievefromdata' (account generation) type templates. Note, however, that language translations cannot be used in account generation templates, because even though the template itself is of 'predefined' type, the account rows generated for entering account generation data (the "sub-areas") are of 'retrievefromdata' type. For more information, refer to the description of the presentationtype parameter in Clausion Input and Report Template Parameters. The language used in the template depends on the user's active language.

1. After the last row set column, leave at least one empty column before the translation columns. If row sets are not used, leave an empty column after the ACCCODE column. You can add as many translation columns as you need.

2. In the first translation column, add the name TRANSLATIONS1 to the cell on the same row as the ACCCODE1 definition:

In the Formulas menu in Excel, use the Define Name function and add the name TRANSLATIONS1 to the cell. For more information on using the function, refer to Microsoft Excel documentation.

1. For all translation columns, add the following definition on the header row: <language code used in the system>_(underscore)<letter of the destination column for the translation> (the definition column to which the translation is applied). For example: EN_B.

In this case, the texts added in a column defined as EN_B are shown in column B (the second column in the sheet) when the active language of the application is English.

Note the following:

• If a translation is included in a sheet, it overrides any other existing text in the corresponding definition column of the sheet.

• If no translation has been added, but the corresponding definition column contains text, the definition column text is shown in the input or report template.

• If no translation has been added and the corresponding definition column does not contain text, the account names are retrieved from the account chart based on the account code. For dimensions, the dimension unit name is retrieved from the system based on the unit ID.

• If headers are defined in the XML definitions file, they override translations defined in the Excel file.

• Only the rows included in the areas defined on the sheet are translated.

the Simple Report example template (EX_RT101) columns (K:L) are defined for language versions ('EN_B' and 'FI_B') although no translations have been entered. The use of language translations is illustrated in the Language Translations example template (EX_RT102) that differs from Simple Report only by having language translations entered in the workbook.

In Language Translations (EX_RT102) language versions for the account names have been added to the template workbook in columns K and L.

Language versions entered in the workbook will show in column B of the opened template.

Formulas in Workbook

Formulas may be defined in the workbook or in the XML definitions file. If you define a formula in the workbook, you also need to add some settings in the XML definitions file indicating that the column is a calculated one. See the following examples.

If you define formulas both in XML and in the workbook for the same column, the formula in the workbook overrides the formula in the XML definitions file.

Example XML definitions

Example: Input column with a formula in the workbook

If you want data to be saved with the formula, you must set the value of the <datacolumn><settings><save> element as 'yes'.

Example: Reporting column with a formula in the workbook

Example: Input column with a formula in XML

If you want data to be saved with the formula, you must set the value of the <datacolumn><settings><save> element as 'yes'.

Example: Reporting column with a formula in XML

For more information on the XML structure, elements and attributes, refer to Clausion Input and Report Template Parameters.

In the Simple Report example template (EX_RT101) formulas to column E could be entered to calculate the change from one month to another, but by defining the formula in the XML file, it only needs to be entered once.

Locked and Unlocked Cells

To prevent data entry in other cells than those intended in an input template, unlock the input cells and lock the other cells of the area. Protecting the sheet is not necessary as Clausion protects it when opening the template.

XML File

The contents of the XML definitions file can be characterized like boxes within boxes. The outer box is defined first, and within it are smaller boxes, which in turn may contain smaller boxes of their own. In XML language these 'boxes' are called elements.

The following figure shows the XML file 'boxes' defining an area on a template.

• Area: the area is named, an id is given, and its type is specified. Area name must be the same as in the Excel workbook.

  • Settings: definitions concerning the whole area are made

    • Basicareasettings: definitions mainly affecting the appearance of the area

    • Customareasettings: in custom templates only

    • Subtotals: may be used with row generation

  • Definitioncolumns: definitions of what data is shown on rows, for example accounts and/or units

    • Definitioncolumn: which attribute of the content is shown in this column, for example, account code, unit name

    • Generates: specifying how row content is created in row generating templates

  • Datacolumns: columns where the facts data is shown, for example, euros, pieces or percentages

    • Generates: specifying how column content is created in column generating templates

    • Headers: defining data column headers

    • Settings: details about data retrieving in some special cases

    • Datacolumn: data content of one column

In Clausion a portion of an XML definitions file defining an area may look, for example, like in the following figure.

In addition to the definitions of areas and their sub-elements, there are definitions to be made for the sheet(s), workbook, and for the template in general.

The following figure shows the XML file 'boxes' surrounding the area box(es).

• FPM: standard content for the technical functionality of the template, content not shown in the XML editor

  • Document: standard content for the technical functionality of the template, content not shown in the XML editor

    • Settings: definitions in some special cases of templates

      • Data: in custom templates only

      • Transparentreportsettings: in TR templates only

    • Workbooks: standard content for the technical functionality of the template, content not shown in the XML editor

      • Workbook: standard content

        • Settings: specifications affecting the whole workbook (that is, all sheets and areas of the template)

        • Sheets: 'box' surrounding definitions of single sheets, in case of a multiple sheet template there may be some definitions here

          • Sheet: an id is given to the sheet

            • Settings: the sheet is named, its type is specified, and settings mainly affecting the appearance of the sheet are made. Sheet name must be the same as in the Excel workbook.

            • Areas: 'box' surrounding definitions of single areas, the order in which areas appear on the sheet is specified here

In the input and report templates task, a portion of an XML definitions file enclosing the area element(s) may look, for example, like in the following figure.

When opened in an editor outside Clausion, a portion of the XML definitions file would look, for example, like in the following figure.

Workbook, Sheet and Area Settings

This topic describes basic XML definitions needed in most templates. The XML excerpts are from the Simple Report example template (EX_RT101).

The first few elements of the XML file are always the same. The first element to pay attention to is drilldowndefinition. This definition specifies how the drill-down window looks like when drilling down into figures in report columns. The specifications are stored in a separate template and the name of this template is what is told with the drilldowndefinition setting. We use a standard drill-down definition file BW_DT101. The type attribute is always file.

Then the sheet id and the settings are defined. The id is used for referencing to sheets of a workbook in cases of multiple sheet templates. In the the Simple Report example template (EX_RT101) there is just one sheet but for technical reasons the id must be given anyhow.

The sheet name must be the same as the sheet name in the workbook. The sheet type is report because this template is a report (not, for example, an input or custom template).

The pageorientation and fittopages attributes are optional and they have been added here as examples of common settings.

As for the sheet, also the area must have an id, type, and name. The name must be the same as the area name in the workbook. Areaorder tells in which order areas are calculated on multiple area sheets. In the Simple Report example template (EX_RT101) there is just one area but for technical reasons the area order must be specified. This is done by listing the areas in order of calculation using the listitem parameter.

Presentationtype tells whether the content of definition columns is defined in the workbook (predefined) or constructed when opening the template (retrievefromdata).

Basicareasettings direct how currency conversion is performed and how positive and negative numbers are shown for various account types.

Signs with Debit and Credit Figures

Every account is either a debit account or a credit account. The appearance of debit and credit figures on input and report templates depends on the use of show_dk parameters in the template definitions. Different show_dk parameters are used for income accounts, balance accounts, and other accounts.

Below is an example of the default case when the show_dk parameter is not used in the input or report template definition file. For more information and examples of the parameter usage, refer to Clausion Input and Report Template Parameters.

Default case

By default (if the show_dk parameter is not used in the input or report template definition file), figures are shown on input and report templates as they appear in the database multiplied by the account's D/C code (debit +1 or credit -1).

Table 4: Document series types and accounts

Default signs on templates

*Intragroup transactions are multiplied by -1 when saved to the database. Intragroup transactions are also multiplied by -1 when retrieved from the database to an input column (to a column with the definition type=input ), but not when retrieved from the database to a report column (to a column with the definition type=report).

Definition Columns

Under definitioncolumns (note the plural 's' at the end) contents of the single definition columns are specified (the definitioncolumn parameter (no 's' at the end)) in the order of appearance of these columns on the workbook sheet.

In the Simple Report example template (EX_RT101) there are two columns, the first for account code, and the other for account name. The column id may be any alphanumeric value. For clarity purposes, explanatory ids have been given in Simple Report, because unlike sheets and areas, the columns have no name parameter. The column header to show on the template and the dimension in question are specified for each column. The dimension is given with the dimensiontype parameter. To get the account code in the first column we specify presentationproperty as udid and for account name in the second column presentationproperty is set to name. udid is the code telling what item (for example, account and unit) of the dimension (for example, accounts, dim00 - dim09, and document series) to deal with.

Data Columns

Under datacolumns (note the plural 's' at the end) contents of the single data columns are specified (the datacolumn parameter (no 's' at the end)) in the order of appearance of these columns on the workbook sheet. The column id may be any alphanumeric value.

For each data column the following is defined:

• headers to show on the opened template

• dimensions by which to limit the data in the columns

• settings and datafield parameters to specify how data is handled

If there are parentheses in the datacolumn id value (data column identification), the type value of the dimension element will not be shown in the template management even if it has been correctly defined and visible on the next level in the XML tree.

When the parentheses are removed from the datacolumn id value, the dimension type value is shown normally.

The dimensions definitions tell that data retrieved to the first data columns is limited by four dimensions as follows:

• Data will be from the month (finper) that the user selects when opening the template (%PERIOD CODE%). For information about all available input and report template variables, refer to Clausion Input and Report Template Parameters.

• Data will be from the data type that the user selects when opening the template (%DATA TYPE CODE%).

• Data will be from the year (finyr) that the user selects when opening the template (%YEAR CODE%).

• Data will be from the document series that the user selects when opening the template (%DOCUMENT ADVANCED CODES%).

Other definitions:

• headers: column header will be the name followed by the number of the month in parentheses

• settings

  • save = no: data in this column will not be saved to the database. In a report template this parameter is not necessary but in an input template it is relevant to tell which columns are to be saved and which are shown for reference only.

  • scaling: data from the database will be divided by the factor that the user chooses when opening the template (%USERSCALINGFACTOR%).

  • datafield: this column is numeric (not a comment column for example) and for reporting (not entering data).

  • numeric:

    • amount: defines whether the amounts in the column are in unit or group currency.

    • period: defines whether the data in the column is periodical or cumulative. In this case this is decided by the user when opening the report (%PERIOD REPRESENTATION%).

The second data column is defined similarly to the first one, only the finper definition differs. The %PERIOD CODE-1% variable is used here to indicate that data will be from the month prior to the one that the user selects when opening the template.

Variables with a minus (or plus) sign and a number are only limitedly valid in definitions. For more information, refer to Clausion Input and Report Template Parameters.

The last data column is calculated as the difference between the two other columns:

• datafield

  • type: formulas tells that this is a calculated column

  • formulas: contains the definitions of formulas in the data column

    • formula: defines one calculation rule. Other columns are referred to with their ids.

      • type: this parameter must have value all

  • the numeric parameter is not needed in a formula column