Skip to main content

Row Generation Templates

Using a generated template, a user can enter or retrieve data for combinations of organization units, dimensions and accounts with one input or report template. This type of template may be useful, for example, for entering data for several units and dimensions (for example, a region as in the Static Row Generation example template, EX_RT103). Using a normal input template, the data would have to be entered for one unit at a time. With a generated template, data can be entered for several units at the same time using just one template. Also, data can be entered for several region and account combinations as in the Static Row Generation example (EX_RT103).

Sheets can be generated by rows or columns. It is also possible to create combined row and column generation templates in which both rows and columns are generated, for example, accounts on rows and dimension units on columns. Generated input or reporting typically includes processing only a few accounts in relation to units or dimensions. It can be used to process larger numbers of accounts, but note that this increases the number of rows on the template.

Static Row Generation

The following figure depicts how the Simple Report example (EX_RT101) is changed to show the figures by region.

In the Static Row Generation example (EX_RT103) this is done with static row generation over 2 dimensions. The desired combinations (here regions and accounts) are entered in the workbook and elements for row generations are added to the XML definitions file.

Columns for region code and name need to be added to the workbook. Name definitions must be changed correspondingly. The following figure depicts the workbook of the Static Row Generation example (EX_RT103).

In the XML file a generates element and definition columns for region are added:

The generates element contains information about how to build the generated report and what combinations of dimensions to show.

  • reportsheet: the value static indicates that the combinations (here dim01 (region) and account) are already in the workbook. This info tells Clausion that the workbook does not need to be changed when opening the report. This speeds up the opening.

  • generateitem: defines which dimension to generate and how to do it. One generateitem element is needed for each generated dimension.

    • dimensiontype: the dimension to generate

    • generatetype: the value selected indicates that the units, accounts, and so on (depending on what dimension is generated) are listed under the generateitem element with udid elements. Listing the units here tells Clausion that only data for the listed units need to be retrieved from the database. This speeds up the opening of the template. In static generation the units, accounts, and so on, are defined both in the workbook and the XML file.

    • static: the value true indicates that this dimension is generated statically. In the next example (Dynamic & Static Row Generation example, EX_RT104) dim01 is generated dynamically

    • udid: the codes on, for example, units, accounts to generate

Two new definition columns are defined in the same way as the account definition columns:

Combined Static and Dynamic Row Generation (Total of 2 Dimensions)

The same result as in the Static Row Generation example template (EX_RT103) may be achieved by a combination of dynamic and static row generation. In the Dynamic & Static Row Generation example (EX_RT104) the regions are generated dynamically (values derived from organization, dimension or account structure) and accounts are generated statically (values entered to the workbook).

In Dynamic & Static Row Generation the account blocks (accounts 1000, 2000 and 9000) are automatically created for all regions under the TOP folder in the region structure (dim01).

The columns for region code and name are kept in the workbook, but the rows added in the Static Row Generation example (EX_RT103) and the dim01 codes are removed. Name definitions are changed correspondingly.

The following figure depicts the workbook of the Dynamic & Static Row Generation example (EX_RT104).

In the XML file the following changes are made:

  • reportsheet: the value is changed to dynamic so it is in line with the dynamic row generation

  • generatetype: the value input means that all units under the folder indicated with the udid element are generated to the report

  • udid: the value is set to the folder (TOP), whose child units are needed to be shown on the report

  • static: the value false means that this dimension is generated dynamically

When opening the Dynamic & Static Row Generation example (EX_RT104) we get a slightly different report than with Static Row Generation (EX_RT104) . There is one more block, namely the BWNonAllocatedItem region. There would be figures in the BWNonAllocatedItem region if data had been entered for Company X on an input sheet where the region was not specified.

Now as this 'region' has no entries we might not want to show it on the report. This is achieved by adding the removeemptyblocks element (with the value true), which defines whether all account, unit, and dimension combinations are shown in the generated template, or only those for which data exists.

Dynamic Row Generation over Two Dimensions

It is also possible to generate several dimensions dynamically. This is done in the Dynamic Row Generation, 2 Dimensions example (EX_RT105), which is a sales report where segment and region combinations are generated on rows.

The following figure depicts the segment structure.

When opened in Clausion, Dynamic Row Generation, 2 Dimensions looks like the following:

The columns for region code and name are kept in the workbook, but compared to Dynamic & Static Row Generation (EX_RT104) the account codes are removed. Name definitions are changed correspondingly.

In the XML file the following changes are made:

  • generateitem with dimensiontype [account] is deleted

  • generateitem with dimensiontype [dim00] is added

  • definitioncolumn (accountcode) and definitioncolumn (accountname) are deleted

  • definitioncolumn (unitcode) and definitioncolumn (unitname) are added before definition columns for region

  • account dimension with dimensionudid [1000] is added to datacolumn (datacol1) and datacolumn (datacol2)

Generating More than Two Dimensions on Rows

It is possible to generate more than 2 dimensions. This is done in the Row Generation, 3 Dimensions example (EX_RT106) with 2 dynamically and 1 statically generated dimensions.

Taking the Dynamic & Static Row Generation example (EX_RT104) as a basis, two more columns are added to the workbook. The XML definitions file is enhanced with a third generateitem and two additional definition columns.

Generated Headers for Blocks of Rows

In the Row Generation, 3 Dimensions example (EX_RT106) the unit, region, and account codes and names are repeated many times. By generating headers for each block of accounts and hiding the first four definition columns we get a more easily readable input template. This has been done in the Report, Block Headers example (EX_RT107) and the following figures depict how it looks when opened in Clausion.

To add readability in the workbook:

  • add a row for block headers

  • merge cells A4:I4 to center block headers over all columns

  • hide columns A to D (do not delete them, just hide)

Do not type anything in the udid column (a column that contains the definition presentationproperty="udid" in the XML file) on the generate block header row in the Excel file. Otherwise the row will be hidden when the Hide Zero Rows function is used.

The following changes are made to the XML definitions file:

  • generateheaders element is added to the first definition column

  • header elements for all language versions are added

    • %DIM00 NAME% gives the organization unit name

    • %DIM01 NAME% gives the region name

Was this article helpful?

We're sorry to hear that.