Input Template and Report Functions
Customizing Input Template or Report Window
Function ribbon and quick access toolbar
To minimize the function ribbon, click the ribbon with the right mouse button and select Minimize the Ribbon from the pop-up menu.
To add functions to the quick access toolbar, click on the function with the right mouse button (button or drop-down list) in the ribbon and select Add to Quick Access Toolbar. To remove a function, select Remove from Quick Access Toolbar.
To hide or view the template options (the drop-down lists above the template table), click
(hide) or
(view).
Row sets
You can select a row set for the template from the Row set drop-down list (available if enabled in the task settings) on the View tab. Row sets are subsets of input or report template rows which can be used to view a predefined combination of rows in the template. The default option is Basic row set (all rows).
If Hide zero rows is set for the template, all zero rows are hidden even if they are included in the selected row set.
Column filtering
Select the columns to which you want to add filtering (selecting at least one cell in a column selects the column). Note that if a selected cell is merged over columns, filtering is applied only to the first column of the merged range.
On the View tab, click
Filter (alternative: click the selected columns with the right mouse button and select Filter from the pop-up menu). Filtering icons appear on the column headers:
.
Click on the filtering icon and select a filtering option from the list. Only one option can be selected at a time. The template data is filtered according to the selected option.
To remove filtering, click Filter again. Filtering is removed from all filtered columns regardless of which columns are selected.
External Workbooks
Input templates and reports may include external Excel workbooks used to provide additional information, for example, explanations for units or other instructions for using the template. These external workbooks are shown as separate workbook tabs (see the following figure).
Extra Field Selection Lists
The extra fields of the input template may contain selection lists from which you can select options for the fields. To open a selection list, double-click a cell in the extra field data column. You can also type directly in the extra field cells. If you type an invalid value, the selection list is opened. A list cannot be opened if the cell is locked.
Periodizing
The periodizing function is available on the Periodizing tab if defined for the input template. However, it is not necessary to use it even if the template contains it. Figures can also be entered directly to the period columns.
The periodizing function is used for distributing a sum over several periods in an input template. Periodizing can be done based on a periodizing pattern or selected values (see the following sections).
Periodizing can be used when a row set is selected.
For cumulative accounts, the total of the sums distributed over the periods equals the value entered in the Periodizable sum column.
For non-cumulative accounts, the average of the sums distributed over the periods equals the value entered in the Periodizable sum column.
Periodizing with Periodizing Pattern
The system may include common periodizing patterns which are available to all users, but users can also create their own periodizing patterns which are only available to themselves (see Managing Periodizing Patterns).
Type the sum to be distributed over the periods in the Periodizable sum column.
-
Click the row for which you want to apply periodizing, go to the Periodizing tab, click
Pattern, and select the periodizing pattern you want to use from the drop-down list. (Alternatively in the Desktop Client: Click on the row with the right mouse button, select Pattern from the pop-up menu and the periodizing pattern you want to use from the list.
The sum entered in the Periodizable sum column is automatically distributed over all periods in the input template.
To select all input rows in the template so that you can apply a periodizing pattern to all of them at the same time, go to the Periodizing tab and click
Select All. (Alternatively in the Desktop Client: Click the template with the right mouse button and select Select All from the pop-up menu.) You can also select specific rows by holding down the CTRL key and selecting the rows you want.
Save the template.
If you do not select a periodizing pattern after entering a sum to be distributed, the
icon is shown in the cell until you select a periodizing pattern.
When you use the periodizing function, the period column cells of the row are locked and cannot be modified. If you want to modify the sums manually, you must release the data by removing the periodizing pattern from the row (see Unlocking Periodized Columns).
Periodizing Based on Selected Values
You can select the values for retrieving data and periodize a sum based on the weights calculated from the retrieved data.
Type the sum to be distributed over the periods in the Periodizable sum column.
-
Click the rows to which you want to apply periodizing with the right mouse button and select Values > Select Values (alternative: Click on the row, go to the Periodizing tab and select
Values > Select Values).
-
Select the values you want to use in periodizing.
All the values listed below are available in Clausion Desktop Client, however, in Clausion Web Client, the only available options are Data type and Year.
Open the list for values:
Data type: Select the data type from which data will be retrieved. The drop-down list contains those data types for which you have at least read rights.
Account: Select the account from which data will be retrieved (all types of accounts can be selected). Accounts that are connected to the selected data type and have the same cumulative setting as the active input account on the sheet are available in the list. If a data type is not selected, all accounts and components with same cumulative setting are available. The currently selected account (when only one account is selected on the template) is shown in the field as the default account.
Period: Set the period range from which data will be retrieved. When you select a period to the Period from field, the Period to field is automatically updated based on the number of periodizable columns in the input template and the position of the columns. The available periods depend on the input template settings: only periods set as periodizable are included in the list.
Year: Set the year range from which data will be retrieved. When you select a year to the Year from field, the Year to field is automatically updated based on the periodizable periods and the start period so that it shows the year of the last period. All years included in the system are available.
Document options: Select the documents or a document series from which data will be retrieved.
Range: Select a range of documents from the drop-down lists (you can also type directly to the fields).
Document series: Enter document series. You can add several document series separated by a semicolon. If no documents are selected all documents are included.
Dimensions: Select values for dimensions. All dimensions available in the system are shown in the dialogue box. You can select the hierarchy for the dimension from the Hierarchy drop-down list. The default hierarchy is the hierarchy selected on the Basic Options tab or defined in the XML file. Units that are connected to the selected data type in the selected year are available in the hierarchy list.
If no option is selected for a dimension, all data for the dimension is retrieved. If the dimension is not connected to the selected data type, the option is disabled. Only units to which the user has at least read access are included in the list.
If you want to preview the data retrieved with your selections, click Preview. A preview window opens showing the following: currency, account, periods/years (as on the input template and in the retrieved data), values in the used currency per period, and percentage weight for each period. Data can be copied from the window.
-
When you have selected all the values you want to use, click OK in the Select Periodizing Values dialogue box.
The rows for which periodizing based on selected values has been used show the text “Data” in the periodizing pattern column.
Data is also retrieved to locked periods if they are set as periodizable. However, data is not saved for locked periods.
When you use the periodizing function, the period column cells of the row are locked and cannot be modified. If you want to modify the sums manually, you must release the data by removing the periodizing pattern from the row (see Unlocking Periodized Columns).
When you open or refresh a template that includes rows previously periodized with values, the rows are marked with the text “Data“ in the periodizing pattern column. If you change the periodizing sum on these rows, the
icon is shown in the periodizing pattern column indicating that periodizing is no longer calculated to the data columns.
Periodizing using previous values
If you want to use the latest periodizing calculated from the data, click the row to which you want to apply periodizing with the right mouse button and select Values > Use Previous Values (alternative: click on the row, go to the Periodizing tab and select
Values > Use Previous Values).
Using previous values is possible on the same template as long as the template has not been saved and refreshed or re-opened. Previous values can be used for cumulative and non-cumulative accounts regardless of the type of the account the last periodizing with values was done, and also when both types of accounts are selected at the same time.
Unlocking Periodized Columns
When you use the periodizing function, the period column cells of the row are locked and cannot be modified. If you want to modify the sums manually, you must release the data by removing the periodizing pattern from the row.
To select all input rows in the template so that you can remove the periodizing pattern from all of them at the same time, go to the Periodizing tab and click
Select All. (Alternatively in the Desktop Client: Click the template with the right mouse button and select Select All from the pop-up menu.)
Updating Periodizing Pattern List in Input Template
After adding new periodizing patterns to the system, you must update the periodizing pattern drop-down list in the input template before the new periodizing patterns become available.
Go to the Periodizing tab and click
Update. (Alternatively in the Desktop Client: Click the template with the right mouse button and select Update from the pop-up menu.)
This does not update weighting factors that have been modified when the input template was open. To update the weighting factors, open the input template again, or click
Refresh on the Home tab.
Entering Internal Transactions Using Account Drill-Down
If enabled in the input template settings, you can enter data to type 2 document series using account drill-down on a basic input sheet. The account generation function is only available for input accounts, including sum accounts used as input accounts.
Using default drill-down input template:
-
Double-click on a data cell (alternative: click on the cell and click
Drill Down). An empty data row is added below the row. If there are existing entries, the empty row is added below them. The account code and name are copied from the row from which the account generation was opened.
-
Double-click on the counter unit ID cell and select a unit from the selection list or type the unit ID directly in the cell. The unit list contains all units connected to the data type. Unit rights apply to the list, if counter unit restriction functionality is in use in the environment.
You can add a position number for the entry. If you leave the position number empty, 1 is automatically added when saving. The template allows you to change the position number later, but note that the existing row is then not updated, instead new identical row (except for the position) is created.
Enter the sums in the data cells.
If a comment column exists, you can enter a comment for the row.
If an input type column is included in the template, it displays the automatic and calculated entries updated after the last save. The data in this column cannot be modified. If an input type is not included in the template, only manual entries are displayed.
To select how the account drill-down rows are shown, select an option from the Drill-down Style drop-down list:
Spaces: Account numbers are indented with spaces before them.
Dashes: Account numbers are indented with dashes before them.
None: Account numbers are not indented.
Importing Data from Excel File to Input Template
Click
Import data.
-
Select an import option:
Clear template and import data from matching cells: The data of the Clausion input template data columns is cleared and the Excel file data is imported to the template when the definition column values match in the template and the Excel file.
Update only cells for which the file contains data: No data is cleared from the Clausion input template, but existing data is updated and new data is imported from the Excel file.
Click OK to start the import.
Notes and restrictions:
Clausion Desktop Client: Data can be imported to InputSheet10000, InputSheet20000, InputSheet40000, and InputSheet70000 type templates.
The structure of the Excel workbook from which data is imported must be identical with the input template structure as regards the template areas, definition columns, data columns, and the name of the tab in Excel. "predefined" type input templates may contain more than one area, but "retrievefromdata" type input templates only one (importing a file with more areas will result in an error).
If there are several sheets in the Excel file, data is imported from the sheet with the same name as the active sheet in the input window. If a sheet with the same name is not found, no import is done.
All rows in the template are processed regardless of the hide zero rows setting or the selected row set.
Extra fields are imported from the file if they are defined in the XML definitions. If the extra field is of type "numeric" and there are several rows with the same matching criteria, the values in the extra field are summed in the same way as for normal report fields. If the extra field is of type "string" and there are several rows with the same matching criteria, only the last value is imported to the input sheet. If the extra field is of type "comment", importing is done as with "string" type extra fields except that numeric input is not retrieved to the template.
If the input template (InputSheet10000, InputSheet40000 or InputSheet70000) is empty, all data in the Excel file is imported to the input template. For "retrievefromdata" type templates this means both the definition columns and the data columns, for "predefined" type templates data is imported only to the data columns. (Report columns and locked cells are not updated.)
When using the Clear template and import data from matching cells option, 0 values are added in the input template for those column combinations which do not match. When using the Update only cells for which the file contains data option, no 0 values are added.
New rows are added from the Excel file to "retrievefromdata" type templates, but not to "predefined" type templates.
If the Excel file contains the same combination repeatedly, the data is summed during the import.
In InputSheet10000 and InputSheet20000 templates, invalid IDs are replaced with a blank. In InputSheet40000 and InputSheet70000 type templates, invalid and empty unit IDs are replaced with the BWNonAllocatedItem unit and invalid account IDs with a blank.
In InputSheet20000 templates, data is imported for the unit selected in the template. All other rows are ignored.
No data is imported from the Document Number column.
If the input type column is empty or contains an invalid value in the Excel file, the value "Manual Entry" is added to the row. As a result, rows with an invalid input type are treated as manual entry rows and summed up. Rows with input type "autocalculation", "calculated", "NCI" or "reconciliation" are not imported.
Viewing Sub-Account Data
You can drill down to accounts that contain data and view the data of the sub-accounts.
Double-click on the account number of the account or select the account number cell and click Drill Down. The sub-accounts that contain data are shown below the parent account. Note that all sub-accounts are shown regardless of account and data type connections.
The hide/show ( /
) symbol appears on the left side of the list. You can hide or show the sub-account data by clicking this symbol. To hide the sub-account data of all accounts, click
Hide All.
To remove the sub-account rows, click Remove All or double-click the vertical line of the account from which you started drilling.
To select how the sub-account rows are shown, select an option from the Drill-down Style drop-down list:
Spaces: Sub-account numbers are indented with spaces before them.
Dashes: Sub-account numbers are indented with dashes before them.
None: Sub-account numbers are not indented.
Drilling Down to Figures
Drilling Down to Basic Data Level
By drilling down to the figures, you can view detailed information on what the figures consist.
The drill-down sheets are company- and report-specific.
Double-click a figure on the report or select the cell you want to drill down to and click
-
In the drill-down window, you may select the check boxes of the options you want to apply to viewing data.
The options listed in the following are according to the default drill-down template, but there can be different company- and report-specific dimension definitions in use.
Unit
Document
Position
Co-Unit
Input Type
Click
There may be more than one data column in the drill-down template. For example, when you drill down to a selected period in a quarterly report, you may also get the data of the previous two periods. The original value from which you drilled down in the report is displayed in the status bar. You can sort the rows in the window by clicking on the column headers.
If a report is opened from a consolidation level, extra field columns of the "text" type display the text "n different values". To view the values, drill down from the extra field. The values are displayed in the Amount column.
Drill-down template rows
Total permitted units: This row displays the total sum of the data for the units you have rights to.
Total protected units: If you have rights to the consolidation level but not all units under it, the data of the other units is summed up in this row. The row is only visible when you do not have rights to some units.
Difference: This row displays the difference between the report and the drill-down values. Usually the difference is 0 regardless of the value of the All entries allocated to unit option. The difference is calculated only for the period of the data column from which you drilled down. If there are data columns for other periods on the drill-down template, N/A is shown as the difference in them.
Total: The total(s) of the sums in the drill-down window.
Total on status bar: The sum in the report from which you drilled down.
Drilling down to internal transactions
When you have drilled down from an internal input account you can drill down further from the drill down window to see the related internal transactions. This applies when the document series type=1 (either as document or document series) is visible on the drill-down screen (document / document series check box selected in the drill down window) and the series is linked as target to a series of type=2.
Double-click a figure in the drill-down window or select the cell you want to drill down to and click Drill Down on the Home tab. The internal transactions drill-down window opens showing the related internal transaction entries. Note that amounts on this screen have the opposite sign to that of the figure from which the drill-down was performed. This is because what is shown here are the document series type 2 eliminations entries created from the input of internal transactions.
Drilling Down to Source Data Document Level (Transparent Reporting)
This feature is available only if the Transparent Reporting add-on feature is enabled in your system.
The drill-down sheets are company and report specific.
With Transparent Reporting, you can drill down deeper to the figures entered to the accounts and find out what the figures consist of on source application document level. When you drill down to source document level in internal transactions, the document series and counter unit are taken into account with regard to the elimination entry.
To drill down to the document level from the drill-down window:
Double-click a figure in the drill-down window or select the cell you want to drill down to and click
The Difference row shows the difference between the data from the database and the Total row. This row will be displayed when the difference is not zero.
You can sort the rows in the window by clicking on the column headers.
Drilling down to cumulative figures
When you drill down to a cumulative figure, all documents of the active year up to the selected period are shown. The setting of the <showalloids> element in the report definitions file defines whether the documents are shown by default either as a total for each period (one period per row) or if all documents of all periods are listed. For more information about the parameter settings, refer to Clausion Input and Report Template Parameters.
When documents are shown by periods, you can view all documents by clicking Show All Invoices.
When all documents are shown, you can view documents by periods by clicking Show Periodically.
When documents are shown by periods, you can drill down to the figure of a period to view all documents of that period.
Currency conversion
If the reporting currency is the group currency, the sums in the drill-down view are also shown in the group currency. This means that no currency conversion will be done. If the reporting currency is not the group currency, the values in the drill-down window are shown in the reporting currency and conversion into the reporting currency is done.
Note that converting with cumulative currency rates is not possible in Transparent Reporting on document level. In this case an error message is shown.
Viewing Invoices Linked to Documents (Transparent Reporting)
This feature is available if the Transparent Reporting add-on feature is enabled in your system.
You can open invoices linked to documents from invoice archiving systems such as Basware Invoice Processing system, Basware P2P, or Rondo if such system is in use in your environment and you have the required rights.
First drill down to source document level (see Drilling Down to Source Data Document Level (Transparent Reporting)).
To view all rows that contain invoice data, click Mark Invoice Row(s). The cells that contain invoice data are shown in blue color.
To open an invoice, double-click on the cell. The invoice opens in an application window or a web browser window depending on the parameter settings. Note that you cannot open an invoice from the Protected units sum row.
Saving Input Template or Report to File
You can save an input template or a report from the system to a file in xlsx and pdf formats:
In the template, click
Save to File.
Select the saving location, type a name for the file and select the saving format (xlsx or pdf).
Click Save.