Report form structure

<< Prev   Next >>

When you develop report forms, determine the following:

• Report data (rows, columns, indicators, necessary dimension drill-downs).
• Rules for calculating and checking indicator values.
• Rules for displaying the filled values: output form templates to display aggregated values and their dimension drill-downs.

All the settings mentioned above are used by the Report types catalog item.

Report types

The Report types catalog is a classifier of report and budget forms. It is used for development of customizable report models.

Use a short unique report type name as a code of the Report types catalog item.

The Report types catalog item form contains the following tabs:

• Main.
• Report dimensions.
• Default settings.
• Report parameters.
• Set limits.
• Used balance and turnovers. This tab is only available if the report's purpose is set to Balance and turnovers by accounts.

Let's review each of these tabs.

On the Main tab, specify the main report type attributes, such as Description, Code, or Full name. In the Object used for value approval attribute, specify a document that's used to approve values of report type indicators: Report instance or View. Select the latter if you expect different approval routes for different report type row groups. View-based approval uses the Pivot table data processor. In the Purpose attribute, specify the extra functionality of report type indicators. You can select one of the following values:

• Arbitrary data. No extra functionality is available.
• Balance and turnovers by accounts. The report type is automatically generated from the system chart of accounts. The indicators are related to accounts. Any changes to the related chart of accounts are automatically reflected on the current report type.
• Cash flow budget. The report type can be automatically generated from the Cash flow items catalog. In this case, each row in the report type is related to an item in the Cash flow items catalog, and the report row dimensions are inherited from the corresponding cash flow item.
• P&L budget. The report type can be automatically generated from the Income and expense items catalog. In this case, each row in the report type is related to an item in the Income and expense items catalog, and the report row dimensions are inherited from the corresponding income and expense item.
• Purchase budget. The report type can be automatically generated from the Purchase budget items catalog. In this case, each row in the report type is related to an item in the Purchase budget items catalog, and the report row dimensions are inherited from the corresponding purchase budget item.

The Save change history check box determines version management for the current report type. If this check box is selected, each time you change and save an indicator value, a new version is created. This is useful if you want to track and control all changes to the report type indicators. Please keep in mind that creating a large number of versions holding a significant amount of data may result in slower reading of the report type data. In this case, it's recommended that you clear the Save change history check box.

The Investment project separation check box enables automated calculation of investment parameters for a project. It's useful if you want to use the report type as budget for an investment project. If this check box is selected, the mandatory key dimension Project is added to each indicator in the current report type.

On the Report dimensions tab, specify the report type-level dimensions (up to 6). The report type dimensions are added to all indicators in the current report type. Use the report type dimensions if you plan to have report instance separation (for example, for the purpose of assigning responsibility in the approval subsystem).

On the Default settings tab, specify settings for the report instance template, pivot table template, report instance import template, processing rule, and verification rule to be used with the current report type unless the report preparation regulation specifies otherwise.

On the Report parameters tab, specify additional parameters for arbitrary type. These parameters can be used as extra variables in the Report instance document (for example, for the Processing rules calculation procedures).

On the Setting of limits tab, select or clear the check box that allows using the current report type to set limits in the operational planning and control subsystem. You can only select this check box if the current report type has one or more report rows corresponding to cash flow items, income and expense items, or purchase budget items. This tab displays rows of the current report type that contain the resource used to calculate the indicator containing the limit amount.

The Used balance and turnovers tab is only available if the purpose of the report type is set to Account turnovers and balance. On this tab, specify the attributes that govern the automated report structure generation rules:

• In the Chart of accounts attribute, specify the Infobase charts of accounts catalog item whose accounts are used. Below, the extra dimension types for the chart of account are displayed for reference.
• In the Register resource attribute, specify the resource of an accounting register related to the chart of accounts. This resource will be used when the report indicator filling rules are automatically defined.
• Select the By selected accounts and extra dimensions check box if you want only some of the accounts and extra dimensions in your report. After selecting this check box, select the accounts and extra dimensions you want in your report.
• Select the It is the main one for the specified chart of accounts check box if you want to automatically rebuild the report structure upon any change in the structure of the selected chart of accounts.
• Select the Shorter (balance by balance indicators and turnovers by PLS) check box if you want the report to contain only the accounts with Account category indicating balance or P&L statement.
• Select the Do not generate analytical drill-downs check box to generate a synthetic trial balance that doesn't include indicator drill-down by extra dimension.
• Select the Do not display grouping accounts, Display off-balance accounts check boxes to specify the accounts you want to use for generating the report structure.
• Select check boxes from the Receive grouping account totals group to specify how formulas are generated for accounts that have subordinate accounts. You can choose to automatically calculate totals for target account value hierarchy, or to directly receive data from the accounting register.
• Select check boxes from the Used kinds of balance and turnovers by accounts to specify which accounting totals will be displayed in the report columns.
• Click Update indicators to update the report structure and indicator calculation formulas in accordance with the above settings.
• Click Update calculation formulas to update calculation formulas only. Report structure will not be updated. This might be useful, for example, when you want to use a different register resource or a different method for receiving grouping account totals.

In the report type form, you can access items of the following catalogs related to the selected item of the Report types catalog:

• Report rows.
• Report columns.
• Dimension groups.
• Report indicators.
• Report templates.

Report rows

The Report rows catalog stores general information about each report row.

To view the list of report rows, go to the Rows tab of the template development form or to the Report types catalog item form, and click More – Report rows.

You can use the Report rows catalog to do the following:

• Specify an order of displaying report indicators vertically (row sequence in the report). The Sequence number attribute is used.
• Set default settings for indicators that will be related to this row.
• Set a default row name for displaying in the report template.

You can enable item hierarchy in the Report rows catalog. When using hierarchy in the prepared report instance, you can group second-level rows by first-level rows, third-level rows by second-level rows and so on.

The Value type attribute determines the default indicator type for the current row from the following options:

• Number.
• String.
• Date.
• Boolean.

The Purpose and Item attributes are used when developing report types for budgeting.

The Drill-down group determines analytical flags that will be used by default to drill down indicator values of this row.

The Exchange rate kind attribute (found on the Currency conversion tab) is used to specify how to convert an indicator value from one currency into another one (by default for indicators of the current row) if it is necessary when you generate a report.

Report columns

The Report columns catalog stores general information about each report column.

The Report columns catalog does not allow hierarchy.

Attributes of the Report columns catalog are similar to the attributes of the Report rows catalog described above.

The Name attribute is used to store a report column name the way it will be displayed in the column title of the report to be generated.

The Sequence number attribute determines a column sequence number in the automatically generated report template.

The Value type attribute determines the default indicator type for the current column. Use this value when you generate a new report indicator manually or automatically to fill in its attribute if this attribute was not specified in the respective Report rows catalog item.

Report indicators

Data of all reports is stored by the Report indicators catalog items. Each item of the catalog is at the intersection of a report row and column.

In the indicator code, specify its short name that is unique within the report type that the indicator belongs to.

In the Row and Column attributes, specify the following items of the Report rows and Report columns catalogs: a row and a column of the cell to place the indicator value.

The Value type attribute determines an indicator value type. You can select from the following options:

• Number.
• String.
• Date.
• Boolean.

The Dimension group attribute specifies analytical flags that will be used to drill down values of this indicator.

The Asset/Liability attribute specifies whether the indicator is an asset or liability. The indicator kind is considered when balancing manual entries.

In the Exchange rate kind attribute on the Currency conversion tab, specify a method to calculate the exchange rate. This method will be used when you convert an indicator value from one currency into another if it is required when you save report data to the infobase. The following options are available to determine an exchange rate:

• Exchange rate at the beginning of the period.
• Exchange rate at the end of the period.
• Exchange rate at the end of the previous period.
• Average rate for period.
• Average rate for the previous period.
• Average rate for IFRS period equals to the arithmetic mean of the exchange rate at the beginning and at the end of the period.
• Exchange rate as of the date specified in the indicator. If you select this method, the indicator value is converted at the rate on the date specified in the related indicator.
• Exchange rate specified in the indicator. If you select this method, the indicator value is converted at the rate specified in the related indicator.

Select the Not converted check box to specify how numerical indicators (meaning ones containing Number values) are to be used.

If a numerical indicator is not convertable:

• It can't have exchange rate kinds.
• Rounding-off format and units of measurement settings are ignored when the indicator is displayed in the report.
• These indicators are not summed up during automatic procedures that allow summing up upon consolidation and collapsing dimensions, periods, and so on.

Use the Consider taking control/loss of control in the consolidation period check box for IFRS functionality and consolidation. This check box determines an indicator value from the subsidiary report to use for consolidation: full value or considering an ownership ratio.

When you fill in the report instance, you can specify scale and accuracy for displaying values of numeric indicators. However, in some cases, an indicator value does not allow changing its display properties. For example, a price when calculating an amount through quantity and price. For such indicators, select the Not scaled check box.

Dimensions for indicators and rows

You can assign additional dimensions for separate indicators and rows of the report. The total number of dimensions, both on the report level and the level of indicators and rows, cannot exceed 6.

Use the Dimension groups catalog to group dimensions assigned for indicators and rows. You can access items of the specified catalog in the report development form on the Dimension groups tab.

Each item in the Dimension groups catalog is a set of items: Dimension1, Dimension2… Dimension6. Each of these is related to an item of the Dimension types (corporate) chart of characteristic types. Dimension groups simplify the use of reporting indicators with the same dimension: you can use the same group for several indicators. You can specify only one dimension group for each indicator.

If one or several dimensions are specified on the report type level, you can specify one or several dimensions fewer in the Dimension groups catalog.

For the Dimension groups catalog item, specify the following attributes:

• Code. A dimension group code.
• Name. A dimension group name.
• You can select the Required check box for a dimension kind. The check box indicates that the dimension kind must be filled in.

If you fill the Report instance document using the developed report, each dimension group will be displayed in a separate form. This form can also contain drill-downs for indicators related to the dimension group. If the dimension group is specified for a row, you can display drill-downs in the report body in the filled Report instance document.

Report templates

The Report templates catalog item contains a form intended to display report type indicators and dimensions.

Report templates is a subordinate catalog. You can open or create it from the list form of the Report types catalog.

When creating a new item, you will be prompted to select a template type: either Report instance template or Pivot table template.

You can create any number of templates for each report type. For example, you can create two templates for your report type and use one to display data and the other to import report indicators from Microsoft Excel spreadsheets. You can also use templates to display different data for different users, companies, scenarios, periods, or projects. Report templates intended for data import must meet specific requirements. They are generated by a wizard and can't be modified manually. To specify report templates you want to use for data import and for display purposes, go to regulation settings.

Report instance template

A report instance template is an arbitrary tabular form used to display and edit indicators in customizable reports. A report instance template is similar to a Microsoft Excel sheet.

You can use report instance templates to:

• Display indicator values for any report types and any data dimensions. It is required when a user fills in a report based on data from another report. If a report template indicator matches the report instance on key filters, you can read and edit it. Otherwise, you can only read it.
• Display indicator dimensions (both key and additional) in rows and columns.
• Display dimensions hierarchically.
• Define the fixed list of dimensions to display. A report instance created based on a template with dimension filters is a report template with filled rows and you need to fill in only indicator values.
• Generate report books from the current template and any number of other templates.
• Display text messages in a variety of languages using a glossary.
• Display report and dimension indicator data as charts.
• Import data from Microsoft Excel spreadsheets.
• Create arbitrary calculated expressions from the indicators calculated and displayed in a template.

New report instance template creation wizard

The template creation wizard is the easiest method to create a new report instance template. To run the wizard, go to the template form and execute the Create\Template command. You can use the wizard to:

• Select Template appearance template. The Report template appearance templates catalog contains appearance templates for report rows, dimension drill-downs, and report type titles. Several predefined appearance templates are available for you by default. However, you can create as many custom appearance templates as you need.
• Select rows and columns you want to add to the report template.
• Specify how totals by periods are displayed.
• Specify how totals by periods are displayed.
• Specify how key parameters (such as title) of the report instance are displayed.

To generate the template, click Apply. The template created this way is not final; you can add indicators or dimensions to it, or modify it in other ways.

Working with report instance templates

A report instance template is a spreadsheet document. It contains cells of the following types:

• Cells not linked to any report indicators. These cells contain arbitrary text and formatting.
• Cells linked to report indicators. A cell can be linked to a single report indicator.
• Cells containing formula expressions built from report indicators.

Cells not linked to any report indicators are fully editable. In data display mode, they look exactly as in the template.

You can link any template cell to any report type indicator. To do this, go to the template form, click More, select the Indicators on the left panel, and drag an indicator to the template area. After that, you'll not be able to edit the cell content because the cell will display the linked indicator's value instead of editable text.

After linking a cell to an indicator, you can filter the cell content by any dimensions available for the indicator. To do this, select the indicator area and click the Set indicator value filter command on the toolbar or in the context menu. Then, select dimensions and filter type. For example, let's assume that you want to display data from the Revenue amount indicator of the current report instance in the first column of your template, and data from the Actual scenario of this indicator in the second column. To do this, drag the Revenue amount indicator to the first and second cells of your template. Then, set the additional filter Scenario equal to Actual for the Actual scenario of the Revenue amount indicator. Now, the second cell will always display data for the Actual scenario, regardless of the scenario selected in the report instance.

To create more complex expressions, you can use the template expression editor. To do this, select a template cell and click the button next to the input field Formula.

This will open the template formula editor. In this editor, you can create complex expressions by combining indicators and logical operands. To add an indicator to the formula editor field, double-click the template area containing the indicator, or drag the indicator from the Indicators page. After you add an indicator to the formula editor field, you can set filters by dimensions for this indicator by clicking Change operand filter.

Let's take a look at an example of using template formula with the Actual scenario that we discussed above. Let's assume that the template contains cells with indicators Revenue amount and Revenue amount (actual), and you want to add a column named Variance containing variance between the Revenue amount indicator and the Actual scenario. To do this, open the formula editor, and then drag indicators from template cells Revenue amount and Revenue amount (Actual) to the editor. Filters by the Actual scenario will be added automatically. Next, add the subtraction operand to the formula. Click Check to make sure the formula produces no errors. Then click Apply. From now on, whenever you select the data display mode, the formula cell will display difference between the Revenue amount indicator of the current scenario and the Revenue amount indicator of the Actual scenario.

Using analytical drill-downs in report templates

A report instance template contains two service areas, one at the top and one on the left. These areas display the templates for drill-downs by indicator dimensions, by columns and by rows correspondingly.

To create a drill-down area, select a template row or column containing indicators, and click Create\Dimension drill-down area. This command will run the template drill-down creation wizard. In the wizard, select the number of rows or columns that your drill-down templates must contain. For example, if you select two rows and set Number of rows in drill-down area to "1", two drill-down templates will be generated, each containing indicator dimensions for one row. However, if you select two rows and set this parameter to "2", one drill-down template containing two rows will be generated.

Click Next to go to the next page of the wizard. Define dimension structure and specify where the dimensions shall be located in the template. For each tree level, a separate nested grouping template will be created. Go to the next page of the wizard. Specify the fixed list of dimensions using filters. Go to the last page of the wizard. Specify ordering expressions for all grouping dimensions.

Next, click Generate. This will generate an indicator analytical drill-down template according to the specified rules, and add it to the report template. To delete a specific level of a drill-down template, delete the entire drill-down template, or change conditional formatting for it, use the standard commands available for a spreadsheet document area. To edit the drill-down list or ordering expression, click the icon corresponding to a row or column in the service area of the report template. This will open the drill-down properties.

Template control panel commands and buttons

• Switch to data display mode. This command opens the template form in data display mode without creating a report instance. This form opens data in read-only mode.
• Create\Template. This command runs the template creation wizard described above.
• Create\Analytical drill-down area. This command runs the wizard that creates a template to display and change settings of dimension list for the selected indicators, as described above.
• Create\Template. This command runs the template creation wizard described above.
• Create\Report rows. This command runs the wizard that creates new rows for a report type, based on the selected text cells in the current template. To preview the rows to be created and fill in their common attributes, go to the Generate report rows form. Before you execute the command, select cells with row names in the report template. The selected area must contain one column.
• Create\Report columns. This command runs the wizard that creates new columns for a report type, based on the selected text cells in the current template. To preview the columns to be created and fill in their common attributes, go to the Generate report columns form. Before you execute the command, select cells with column names in the report template. The selected area must contain one row.
• Create\Report indicators. This command runs the wizard that creates new indicators for a report type, based on the selected rows or columns of the current template. If no rows or columns are selected, you will be prompted to create all missing indicators for the current report type.
• Import report template. This command imports a report template from a Microsoft Excel file. Only the cell structure is imported. Conditional formatting is supported.
• Save template to file. This command exports the current template to a Microsoft Excel file. Use it to create a template for further data import from a Microsoft Excel file to a report instance. When you execute this command, specify the following parameters in a dialog box:
  • □ External infobase. Set this to Spreadsheet documents (1С or Microsoft Excel).
  • □ Password. Type in a password if you want to protect the import file.
  • □ Path to file. Select path to the import file.
  • □ Frequency. This parameter is required to create column titles containing periods.
  • □ Number of periods. Specify the number of columns with periods expected in the current import template (file).
• Insert chart. This command inserts an interactive chart into any area of the current template. After inserting a chart, link it to the report indicators. You can link the chart to indicators stored in the current template or in any other template.

To link a chart to an indicator area, select the chart area and then click Select data area in the context menu. This will open a new form. In this form, select an area with indicators containing data, and specify data to display for series and points. To change other chart properties, click Properties in the context menu. To display the chart data, go to the data display mode or open the report instance in a corresponding template.

• Freeze table. This command locks rows or columns of the current template in relation to the selected cell.
• Set indicator dimension filter. This command sets additional filters that can be used to display indicator data in the template data display mode. Filters set for the current report instance apply to all data in the template by default. If you want to display indicator data for a scenario or a period not contained in the current report instance, you need to specify it explicitly. To modify a filter, select the area containing the template indicators and execute the command. This will open a new form. In this form, specify filter parameters for dimensions.
• Display formatting bar. This command displays or hides a bar that provides quick access to the standard formatting commands for spreadsheet cells.
• Properties. This button opens the Report indicators catalog item form. Before clicking this button, select the cells containing references to the report indicators.
• More. This button displays or hides the template form area that contains attributes and additional controls.

Attributes and controls in the additional template form area

The Common tab contains the following attributes:

• Code. Code of the current report template (for reference purposes).
• Name. Name of the current report template.
• Report type. Owner of the current report template (for reference purposes).
• Purpose. If the purpose is set to For display only, you can't use this template to import data from Microsoft Excel. Such templates can have any structure that you consider best for data display purposes. If the purpose is set to For import and display, you can use this template to display data and to import data from Microsoft Excel. Please note that import templates are generated by the template creation wizard and can't be modified manually.
• Sheet name. This attribute is only available for templates with purpose set to For import and display. It contains the default name of sheet for data import from Microsoft Excel.
• Unit of measure. Determines the default value for a report instance attribute with the same name.
• Accuracy. Determines the default value for a report instance attribute with the same name.
• Multilingual. Enables glossary to display the text areas of template in a variety of languages.

The Indicators tab contains all indicators for the selected report type. You can drag any indicators from the list to the report template or to the calculated expression in the template formula designer.

The Parameters tab contains parameters available for data display mode for the current report type. You can drag any indicators from the list, or their attributes, to any area of the report template that doesn't contain a reference to an indicator. You can add several parameters to a cell, creating expressions like "text: [Parameter.Attribute]". For example, you can have a cell containing text "Company: [Parameters.Company]".

The Additional templates tab lists report instance templates to be displayed as additional tabs for the current template (similar to sheets in a Microsoft Excel book). For each template, you can specify the title, the tab background color, and the tab icon.

Pivot table template

Pivot table template displays an arbitrary slice of indicators and dimensions in the form of a pivot table. The template is configured parametrically and can't be interactively modified.

You can use the pivot table template either as a report instance template or directly, using the Pivot table data processor.

In the Template attributes group, you can optionally specify the template name and select Leading view. You should specify Leading view if you use Data views to approve indicators for the current report type and you plan to explicitly link the list of pivot table template indicators with the list of view indicators to be approved.

Pivot table template control panel commands and buttons

• Edit row list. This command selects rows to display in the current pivot table template. You can select rows from any report types. The selected rows can be grouped and sorted for your convenience.
• Edit column list. This command selects columns to display in the current pivot table template. You can select columns from any report types that have a row selected. The form specifies a column, column name in the table, and column type. The column type determines how the column data is used. Basic value displays a basic value determined by filters. ∆ (variance of current value from basic value) displays absolute variance of the current value from the basic value. ∆% (variance of current value from basic value) displays relative variance of the current value from the basic value. Scenario, Company, Period, Project. These columns specify the basic value for display data from the current report column. All basic values are set to Context value by default. This means that the current indicator filters are used to display the pivot table data. However, you can set any basic value to a fixed value or to Shift by period for the Period field. The Display in additional currency field displays the column value in the selected drill-down currency.
• Edit grouping locations. This command opens a form containing the pivot table grouping locations. The list of available grouping dimensions depends on the list of rows and columns in the current pivot table template. Available dimensions. This field contains dimensions whose data is included into the pivot table. Top (quick filters). This field contains dimensions used for quick data filters in the data display mode. Please note that the Scenario dimension is only used for quick filters. You can't move it to other fields. In rows. This field contains dimensions used to group data in the pivot table rows. The Report rows dimension is locked, so you can't move it to other fields. In columns. This field contains dimensions used to group data in the pivot table columns. The Report columns dimension is locked, so you can't move it to other fields. To edit the additional properties of a dimension, double-click the dimension field. You can't edit properties of a locked dimension. Dimension presentation in table. In this field, you can create a dimension presentation expression for the data display mode using the presentation builder. Dimension ordering expression in table. This field specifies the table field used to order the dimensions. The Display catalog hierarchy in the table check box is available for dimensions that support group or item hierarchy.
• Edit period display settings. This command opens the form that specifies how periods are displayed in the pivot table. Data frequency is a required field in this form that applies to the current template of the pivot table. Please note that a pivot table template can't contain periods of different frequencies. For example, if you keep both monthly and quarterly data, you need to create two templates, one for monthly and one for quarterly periods. In this form, you can also select totals to display for the selected frequency. The totals are calculated whenever the pivot table is displayed. Frequency of the totals must be greater that frequency of the table data. For convenience of working with numerous periods, you can apply an aggregated data grouping starting from a specified period. To enable data grouping, select the Use period grouping check box and specify the starting period since which the grouping shall apply.
• Edit dimension list settings. This command edits the list of dimensions displayed in rows or columns of the pivot table. For example, let's assume that you want to display a pivot table where the Product range dimension list for some rows is limited to a fixed list of values that's not editable by users. To do this, open the dimension list settings form, find a column with the name matching the dimension (Product range), and set any filter except All items for your rows and this column.
• Switch to data display mode. This command opens the current pivot table template in data processing mode.

<< Prev   Next >>