1. Import and export data
  2. Export Layouts
  3. Export Data in Tabular Multiple Column Layout

The Tabular Multiple Column export layout allows you to include multiple data columns in the export.

Importantly, it also allows you to control which cells are exported from a module by filtering on a Boolean line item. The Boolean flag that controls what data are exported can be calculated so that rule-based filters are possible. This is particularly useful for large modules or modules with many dimensions where manually checking the required cells for export would be a long and tedious process.

Boolean line item filters can't be used in conjunction with show or hide, normal data filters, or the Omit Empty Rows option — you must choose one or the other. However, the desired criteria for export can be achieved by calculating the Boolean line item. For example, to exclude zero cells from the export the Boolean line item could be a conditional formula that tests for zero cells.

To use a Boolean filter to control the export, select the Tabular Multiple Column option with line items pivoted to be on columns and not nested:

  • The filter can either be a single hierarchical list dimension (not Time or Versions, just normal lists) or it can contain exactly the same dimensions as the module you are exporting from. Not only must the dimensions be the same, but they must be in the same order — you can check this under Applies To in the module blueprints.
  • Up to two filters can be set. If you choose a multi-dimensional filter as your first filter, the second one must be a single-dimensional filter. Where the filters overlap, the Boolean flag must be checked in both or it will not export.
  • A line item subset is treated like a normal hierarchy, so it can be used to filter numerical line items. The module you export from must contain the line item subset.

When you use the Tabular Multiple Column export layout, it removes line item styles. Use the Grid export layout if you need to retain line item styles.

Examples

We'll show you how to:

Filter an export to include selected organization units

This example uses a single dimension to control the export of a multi-dimensional module. Only the selected organizational units will be exported.

To use a flag to control the export, make sure that line items are on columns and choose the Tabular Multi-column export layout. A Boolean line item restricts the items that are exported.

  1. On the Data menu, click Export.
  2. The Export dialog appears.

  3. Choose the file type and select the Tabular Multiple Column layout.
  4. Select Filter Rows Based Upon Boolean Line Item then select the line items from the lists:
  5. Click Run Export. You will be prompted to open or save the exported file:
  6. In this example, only the data for the flagged organization units — London and Portsmouth — are exported.

Export using two independent filters

Two single-dimension filters can be combined to control the export. In this example, a filter has been set up to choose selected organization units and a second filter to control which products to export.

  1. On the Data menu click Export.
  2. Choose the file type then select the Tabular Multiple Column layout.
  3. Select Filter Rows Based Upon Boolean Line Item then select the line items from the lists.
  4. Two filters can be used in combination. Both filter criteria must be satisfied for a record to be exported.

  5. Click Run Export . You will be prompted to open or save the exported file:
  6. In this example, the export contains only the selected products — Product A and Product E — for the selected organization units — London and Portsmouth.

Filter an export by products, organization units, version and time

This example uses a Boolean line item that has the same dimensions, in the same order, as the module that is being exported.

You can control what will be exported right down to the item level on each dimension.

  1. On the Data menu click Export.
  2. Choose the file type then select the Tabular Multiple Column layout.
  3. Select Filter Rows Based Upon Boolean Line Item then select the line items from the lists.
  4. Click Run Export . You will be prompted to open or save the exported file:
  5. In this example, only the selected cells are exported.

Export using two overlapping filters

This example shows how two overlapping filters can be used. Cells will only be exported if they are selected in both filters.

The cells highlighted for Paris and Lyon will not be exported. If filters overlap, they must be checked in both filters to be exported.
  1. On the Data menu click Export.
  2. Choose the file type then select the Tabular Multiple Column layout.
  3. Select Filter Rows Based Upon Boolean Line Item then select the line items from the lists.
  4. Click Run Export . You will be prompted to open or save the exported file:
  5. Each record must satisfy both filter criteria for it to be included in the export.

Export controlled by formulas

You can make the Boolean filter that controls the export a formula, for example.

Flag = ‘Version and Time Flag'.Flag

In this example the time periods and versions are chosen. The formula sets the flag. Remember that the Boolean flag must have the same dimensions, in the same order, as the module that is being exported. This means that even though the export is being controlled by a two dimensional grid (time and versions), the Boolean flag needs to be set in a line item — Flag 2 using Formula — with dimensions Product by Organization by Time by Versions.

  1. On the Data menu click Export.
  2. Choose the file type then select the Tabular Multiple Column layout.
  3. Select Filter Rows Based Upon Boolean Line Item then select the line item using a formula from the list.
  4. Click Run Export. You will be prompted to open or save the exported file:

Export just what's changed since you last ran the Export

How useful would it be to just export the cells that have changed since the last export? Do you want to save on data volumes when downloading? Using just two formulas, you can set a Boolean flag that highlights the difference between the current and the old values then export cells only if they have changed.

In this example, the Line Item Subset 5D module has the line item subset as a dimension and uses a COLLECT() function to pull the current data from the Test 5D Export source module for the line item subset into the Current Value line item:

Current Value = COLLECT()

We then set up a Line Item Subset Control module — with matching dimensionality to the line item subset module — for the export. This sets a Boolean flag by comparing the Current Value against the Old Value. If there's a difference between current and old value, the flag is set to TRUE:

Flag if Changed = Current Value <> Old Value

Since a line item subset acts like a normal hierarchy, the cells to export can be controlled at the line item level. (If the line items being exported are non-numeric, then a line item subset cannot be used.)

  1. On the Data menu for the line item subset module, click Export:
  2. The Export dialog appears:

  3. Choose the file type then select the Tabular Multiple Column layout.
  4. Select Filter Rows Based Upon Boolean Line Item then select the Line Item Subset Control Boolean line item from the list.
  5. Click Run Export. You will be prompted to open or save the exported file. Only the changed cells are exported:

Configuring Row Header Labels and Selecting List Dimension Names and Properties

Select the Labels tab to choose whether to include or exclude Row Label Headers. list Names, Codes, or Properties :

  • To exclude dimension labels from the export, delete the value (the default is Name) by clicking the cross next to it. The field will then be blank.
  • To include dimension labels, select the name, code, and/or properties from the drop-down.

Here, we've selected to export the name for each of the Cities and Products list items that are exported.

If there are any list formatted line items in the module, you will see a Line Items of Data Type "List" tab also. You can use this to select the name or code for the lists used for these line items.