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.
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.
Figure 58: Example of normal data columns and generated columns in same template This requires 1 (generated) + 4 (non-generated) data columns in the workbook.
Figure 59: 1 generated and 4 non-generated data columns In the XML file the generated column is defined under one datacolumns element and the four ordinary columns are defined under another datacolumns element.
Figure 60: Generated column and four ordinary columns under separate datacolumns elements 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
Figure 61: Definitions of the generated columns 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
Figure 62: Definitions of the ordinary 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.
Figure 63: Grouping of generated columns This requires 2 (generated) data columns in the workbook.
Figure 64: 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.
Figure 65: Changes in datacolumns element |
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. |