Storing Planning Data in IBM Cognos: D-Cube (Part 2)

0
96
5 min read

Exporting data from the D-Cube

You can export data from the D-Cube into an ASCII file or to the clipboard. The export function gives you the ability to format how you want to export the data. You can set the delimiter, insert headers, and arrange the order of the dimensions.

You can also suppress zero values in calculated data so that the export function will not include records that have zero or null data.

To export from a D-Cube:

  1. Open the D-Cube.
  2. Click on D-Cube|Export.
  3. The Export function displays four tabs:
    1. Export
    2. Header/Footer
    3. Zeros
    4. Show Det/Tot

Export

Observe the following:

  1. Under the Export to option, select whether you want to export to a file or to the clipboard. If you are exporting to a file, enter the path and name of the file that you want to export the data to. Alternatively, you can click on the Browse button to save the file.
  2. Click the Select button to open the item selection box, and then select the dimension items containing the data you want to export.

    If you have a saved selection containing the data that you want to export, then you can load that selection into the dimension selection box.

Groups

Select how you want the dimensions to be displayed as columns:

  • Single Column: Export each dimension as a single column.
  • Multiple Column: Select one dimension whose items you want displayed as separate columns, and set the rest of the dimensions as single columns. The last dimension marked as [data] under the Dimension Order box contains the items that will used as multiple columns (see the following example):

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Single vs. multiple columns

The following table illustrates a single column file with each dimension laid out as a separate column:

 

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

 

 

The following table illustrates a multiple column file. In this example, the items in the Month dimension are displayed as separate columns.

 

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

 

 

Format

In the Format section, select the following:

  • Separator: Set the delimiter to be used in the export file. Select Comma, Tab, Semicolon, or Aligned Columns.
  • Column Headings
    • Normal: This is the default setting. If you export in multiple columns, this option will include columns headers on each page but not for the rows and page dimensions. If you export in single columns, this no column headers will be included.
    • At Top: This option includes the D-List names and column headers at the top of first page only.
    • Above Each Page: This option includes the D-List names and column headers at the top of each page of the export.
    • None: This option does not include column headers at all.
  • Mode
    • Append: Add the data to any previously-exported data in the same file.
    • Overwrite: Overwrite any previously-exported data in the same file.
  • Data formats:
    • Apply Regional Settings: Select this checkbox to use the regional settings in your operating system as the format for the export.
    • Pipes as Spaces: Select this checkbox to replace any pipe symbols (|) with spaces.
    • Plain Number Format: Select this option to remove any numeric formats that you applied in the D-List. The values are exported in as many decimal places as is necessary in is basic format. All commas, currency signs, and percentages will be removed. Negative values enclosed in parenthesis will be prefixed with a minus sign. Non-numeric formats (Text, Date, and D-List) will be retained.
    • Text Qualifier: Choose whether you want the data exported with a single quote or double quote text qualifier.
  • Dimension Order: Determine the order of the dimensions that will be exported as columns. Move the dimensions up or down using the arrows, or click the Dimension Order button to arrange the dimensions according to their order in the D-Cube.

Header/Footer

This option lets you enter a title and/or footer to the export file. Enter the title or footnote by typing directly into the text box.

Zeros

This option suppresses any record with zero values. This option is independent of the zero suppression in force when you are viewing the D-Cube. You can suppress zeros in rows or columns by highlighting the dimension labeled R or C respectively. You can deselect a selected dimension by pressing Ctrl and clicking on the highlighted item. To suppress zeros in pages, select Suppress Zero Pages. If you want to suppress zeros in all of the dimensions, highlight all of the dimensions and then select Suppress Zero Pages.

Show Det/Tot

This option lets you choose whether you want to export only detail items or calculated items by highlighting the dimensions containing the detail or subtotal items, as appropriate. There are separate selection boxes for detail and total items.

Breakback

Breakback is a powerful feature in IBM Cognos Planning. With Breakback, you can enter data into calculated cells and change the variables that make up the formula according to rules that you specify. Breakback is commonly used to propagate changes to a total across its detail items, in proportion to the value of the detail items.

Suppose you have five products, showing a total of 1500 units:

 

Product

Current

Product A

100

Product B

200

Product C

300

Product D

400

Product E

500

TOTAL

1500

With Breakback, if you enter 3000 in the total, the detail products will change as follows:

 

Product

With Breakback

Product A

200

Product B

400

Product C

600

Product D

800

Product E

1000

TOTAL

3000

Breakback distributes the changes in the TOTAL to Products A to E in proportion to their original share of the total. Breakback works on addition, subtraction, multiplication, and division. It can handle multiple calculations across multiple dimensions and hierarchies.

Breakback on hierarchies

You can apply Breakback on a grand total consisting of multiple subtotals across various hierarchical levels. In a simple hierarchy where you have only one subtotal, Breakback distributes the value across its children proportionately. In a multi-level hierarchy, Breakback cascades the changes one level at a time down through the hierarchy. If you enter a value into a total, Breakback will distribute the value proportionately to the subtotals immediately below the total, then to the subtotal the next level down, and so on.

LEAVE A REPLY

Please enter your comment!
Please enter your name here