4.8.10 Unpivoting a Table

In some cases it is useful to unpivot a table from crosstab form into normal “flat” form. This operation converts the column headers into an additional 'Dimension' column.

To unpivot a crosstab, follow the steps below:

1. Right-click the table title bar, and select 'Unpivot' from the context menu. This opens the 'Unpivot Data' dialog box.

2. In the 'Levels of Row Headers' field, enter the number of columns containing row headers.

3. Press 'OK' to close the dialog box.

This creates a new table which contains the same data from the original crosstab, but in flattened form (i.e., with column headers converted to an independent 'Dimension' column). The unpivoted table remains linked to the original crosstab so that changes to data in the crosstab are automatically propagated to the new table.

Example: Unpivoting a Crosstab  >>

To understand how to unpivot a crosstab, first create a crosstab by following the steps below:

1. Create a new Worksheet by clicking on the 'New Worksheet' button.

2. Expand the 'Data Source' node, and the 'Orders' node.

3. Drag the 'All Sales' query from the tree to an empty cell in the Worksheet. This creates a new table named 'All Sales1'. You will use this table to create a crosstab.

4. Press the 'Group and Aggregate' button in the table's title bar. This opens the 'Aggregate' dialog box.

5. Press the 'Switch to Crosstab' button at the bottom of the dialog box. This updates the dialog box to display the crosstab binding interface.

6. In the 'Row Header' region, select the following two fields: 'Employee' and 'Company'.

7. In the 'Column Header' region, select the 'Order Date' field.

8. In the menu next to the 'Order Date' field, select 'Year'.

9. In the 'Measure' region, select the 'Total' field.

 

10. Press 'OK' to close the dialog box. This creates a new crosstab in the Data Worksheet.

11. In the crosstab title bar, click the 'Change View' button, and select 'Live Preview' from the menu.

12. Drag the bottom border of the table to expand the table.

 

This crosstab has two levels of row headers ('Employee' and 'Company'), and one level of column headers ('Order Date', broken out by year: 2008, 2009, 2010, 2011).

You will now unpivot the crosstab to create a flat table that contains the same data. Follow the steps below:

1. Right-click the crosstab title bar, and select 'Unpivot' from the context menu. This opens the 'Unpivot Data' dialog box.

 

2. In the 'Unpivot Data' dialog box, enter a value of “2” for the 'Levels of row headers' value. (The two levels are 'Employee' and 'Company'.)

 

3. Press 'OK' to close the dialog box. This creates a new table containing the unpivoted (flattened) data.

4. In the table title bar, click the 'Change View' button, and select 'Live Preview' from the menu.

5. Drag the bottom border of the table to expand the table.

 

Observe that the unpivoting operation transforms the values of the row header in the crosstab ('Order Date') into a new column called 'Dimension'. The new unpivoted table retains the row headers of the original crosstab in the first two columns ('Employee' and 'Company'), but now repeats each combination of 'Employee' and 'Company' for each value of 'Order Date'.

The data presented in the unpivoted table is exactly the same as the data presented in the original crosstab. However, the flattened version may be more conducive to certain data operations such as trending.

 

See Also

Importing Data Into an Embedded Table, for information on unpivoting a crosstab table during import.

<< 4.8.9 Rotating a Table © 1996-2013 InetSoft Technology Corporation (v11.5) 5 Combining Data Tables >>