Column Generation
Column generation templates
In column generation, a number of similar columns is generated horizontally. It is possible to generate dimensions, accounts, data types, years, periods, and document series in columns. By defining one column to be generated you can produce, for example, a template with a column for each month (generating periods) or columns for units under a specified folder in the organization. The generated dimension(s) change from column to column while the other attributes remain unchanged. You can generate a single column or a block of adjacent columns. In addition to generated columns, the same area on the template can also contain normal data columns. The XML definitions for the normal columns and the generated columns must be made with separate datacolumns elements.
In the workbook just one column should be reserved for each column to be generated. If, for example, all twelve months of a year are generated, only one column should be reserved for that in the workbook. In the XML definitions file, settings for the generated columns are made with the datacolumns > generates element. The dimension(s) to be generated across columns are defined with generateitem elements, one for each dimension to be generated.
Note: Cells in generated columns may not be merged with cells in other columns.
Note: v25.06: Column width applies to dynamically generated columns as well. When a base column is marked as generated and has a specified column width, all derived columns will inherit this width. If the width is set to 0, the column(s) will be effectively hidden.
v25.07: Summarization for Column Generate Reports
Full-fledged summarization functionality is supported for generate templates in column generate reports. This feature allows you to aggregate data from multiple units across dimensions while maintaining the flexibility of column generation.
Supported Generate Types
Summarization for all dimensions is supported when the Generate Type is set to one of the following:
- Selected
- Input
- Next
- Branch
- All
Summarized data reflects the combined values of the selected units or those defined in the template XML.
Flexible Data Consolidation
You can select multiple units from various locations across the hierarchy and aggregate their data into a single report. Data consolidation does not require units to be on a parent level or under a common parent within the hierarchy.
The generated report includes the same units from generated dimension as before. However, for other dimensions, you can select one or more units from various points within the hierarchy. The report will then display data exclusively for those selected units, regardless of their hierarchical position.
Smart Validation and Restrictions
Note: For any dimension where 'Generate' is applied (e.g., Dim00), summarization cannot be applied. Only for those dimensions it will work where Generate is not applied. This rule applies across all generate types.
The feature includes smart validation that prevents summarization conflicts - dimensions with existing generate configurations are automatically excluded to prevent conflicts, and you receive clear validation messages when dimensions have conflicting settings.
Validation Message
For any dimension where 'Generate' is applied, the following message will be shown:
"Summarization will not occur for DIMNN as 'Generate' is applied. Uncheck the summarization checkbox for DIMNN to enable summarization with other dimensions."
How Summarization Works in Column Generation
In a multi-dimensional reporting environment, data can be structured across various dimensions—such as organizational units, product categories, geographical regions, or any other relevant dimensions.
Data Generation Process
Data generation can be applied to one or more selected dimensions, producing distinct columns for each unit within the dimension(s) as per report specifications. For the remaining dimensions, you can select specific units from any level or branch of the hierarchy. These selections do not need to share a common parent or follow a fixed structure.
Summarization Logic
The report will display the generated columns based on the selected units from the generated dimension(s). The data within those columns will be filtered and aggregated based on the units selected from the other dimensions. Only data that matches the selected units across all dimensions will be included in the report.
Drill-Down Support
Summarized columns support drill-down functionality, allowing you to explore unit-level data within the aggregated results—providing transparency and detail similar to standard reports.
Note: Performance impact may occur if multiple units are selected within the same dimension. For example, selecting a top parent unit fetches all child data efficiently, but selecting 10 different individual units may slightly affect performance due to running multiple SQL queries.
Normal data columns and generated columns in the same template
In the Input, One Column Generation example (EX_IT102) a column with actual month values is generated for each of the four regions and 4 normal (non-generated) columns with previous month values for the regions are defined.
This requires 1 (generated) + 4 (non-generated) data columns in the workbook.
In the XML file the generated column is defined under one datacolumns element and the four ordinary columns are defined under another datacolumns element.
For the generated column:
headers are defined
generateitem dimensiontype specifies that the dimension changing between generated columns is dim01, that is, region
generatetype, hierarchy and udid give further details of the generation
datacolumn is otherwise defined like an ordinary data column but the dim01 dimension is not defined here as that is what is defined with generateitem
For the ordinary data columns:
the region name (dim01) is defined as header
the region (dim01) is specified separately for each data column: D01 in datacol1, D02 in datacol2 and so on
Generating sets of columns
In the Input, Set of Columns Generation example (EX_IT103) a set of two columns, current and previous month values, is generated for the four regions. So the same data as in the Input, One Column Generation example (EX_IT102) is shown, but it is grouped differently.
This requires 2 (generated) data columns in the workbook.
In the XML file the first datacolumns element is changed and the other one is removed. Under the remaining datacolumns element the generates element stays unchanged, because columns are still generated by dim01.
The other datacolumn element is added (copy the existing element) and names generatecolumn1 and generatecolumn2 are given to the datacolumn elements. The following changes are made to the definitions:
headers are changed to suit the grouping of columns
the finper dimensionudid of the first datacolumn (generatecolumn1) is set to %PERIOD CODE%
The definitions of the second datacolumn (generatecolumn2) are identical with the settings of generatecolumn1, except for headers and finper dimensionudid.
Formulas in generated columns
Formulas can be used both in the workbook and the XML file. Note that formulas can only refer backwards in the file (to columns before the formula column), not forward (columns to be generated). For example, you can add a formula to a generated block and refer back to another column.
Similarly in a template containing several areas with generated columns, formulas between areas in the workbook must refer backwards from a latter area to the previous one.