Modify the Layout of Data in Views

Use the Layout pane to modify the way that data is arranged in a view.

Perform tasks such as adding and rearranging columns and adding totals.

Topics:

• Add and Rearrange Columns in Views

• Set Properties for Sections of Data in Views

• Add Totals to Tables and Pivot Tables

• Display Running Sums and Relative Values in Pivot Tables

Add and Rearrange Columns in Views

You can add and rearrange columns in views.

Topics

• Add Columns to Views

• Remove Columns from Views

• Rearrange Columns in Views

Add Columns to Views

This topic explains how to add a column to a view.

• Drag the column from the Subject Areas pane to the appropriate location in the view editor.
• Drag the column from the Subject Areas pane and drop it on a drop target on the Layout pane of the view's editor.

For example, suppose you want to include the Office column in a table of the Brand Revenue analysis. You can drag the Office column from the Subject Areas pane to a drop target after the Product column.

Remove Columns from Views

You can remove columns from views.

Removing a column from a particular view doesn’t remove it from the underlying analysis or remove it from other views. If you want to remove the column from the analysis and all views, remove it using the Criteria tab.

1. Open the view for editing.
2. In the Columns and Measures section of the Layout pane, click More Options.
3. Select Remove Column.

Rearrange Columns in Views

You can rearrange columns in views.

1. Open the view for editing.
2. Drag the column using the column handles and drop the column at a drop target.
   
   Description of the illustration views13.gif

To rearrange columns in the Layout pane:

1. Open the view for editing.

2. In the Layout pane, drag and drop the column to the required location.

   
   Description of the illustration views12.gif

   This procedure provides the most basic steps for using the Layout pane to rearrange columns. Many options are available for arranging columns in the Layout pane. See About Drop Targets in the Layout Pane.

Set Properties for Sections of Data in Views

You can specify properties for the view body (such as a pivot table) or drop target (such as a section).

For example, you can set the background color to light green and insert a page break in a long table of revenue values.

1. Open the view for editing.
2. In the view editor, display the Layout pane.
3. Click Section Properties next to the view body or drop target.
   
   Description of the illustration sectionproperties.gif
4. Set the appropriate properties.
   • Use Insert Page Break to specify whether to create a page break before the section, so that every time a value changes in the section drop target, the new section for that column is displayed on a new page. Page breaks are visible when you export an analysis to PDF. This is useful for data-driven detail analyses. Choose from:
   • • No Page Break — Don’t break pages.
     • Innermost Column — Break at the innermost column, which inserts a page break between every section.
     • Outermost Column — Break at the outermost column, which inserts a page break when the section label in the outermost column changes.

       When the value of an outermost column changes, the value of the inner column is also considered changed. Therefore, setting page breaks at the outermost column inserts manual page breaks between every section.

     • Folder.Column — For example, Markets.Region, or Products.Brand. Inserts a page break when the section label in the specified column changes. This option is available only when the Sections drop target contains a column.

   • Use Show Blank Rows to specify whether to display rows that have no data in the section. Select this option to display all rows, even if a row is an empty text string and contains no value. Deselect the option to hide rows when there are no results to display. This option might be useful for example, to hide empty address lines.

   • Use Maximum number of section slider values on graphs to specify the maximum number of values to display on a section slider bar, but not to exceed the system maximum. The administrator configures the system maximum. If you enter a number that exceeds the system maximum, then it’s ignored.
5. Click OK.

Add Totals to Tables and Pivot Tables

In the Layout pane, you can add totals for columns in tables and pivot tables.

You can position the totals at various locations in the view. You can add totals for columns that are displayed on the various edges. For each measure, the total uses the aggregation rule for that measure.

If you specify a total in the Rows or Columns drop target of a pivot table, then the totals that are displayed are the result of the columns that are specified in the Measures drop target. Total values aren’t displayed on the Columns or Rows edges of the pivot table but rather in the data in the center of the pivot table.

1. Display the Layout pane for the view.
2. To add grand totals to the entire table, in the Columns and Measures drop target, click the Totals button, then click the location such as Before.

   For an entire pivot table, in the Rows or the Columns drop target, click Totals, then the location.

3. To turn on and off the totals that apply to all the values in the drop target, click the Totals button beside the drop target name, such as Sections.

   Then select the location for the total, such as Before the data items. A totals area is added to the view.

4. To specify custom text to insert into a total heading in tables and pivot tables, enter text in the Caption box.
   • Use @ to display the data value. Suppose a total is specified for the Region column, and you enter the following text into the Caption box for the total heading: - All values in the @. The total heading displays the following text for the Western Region: - All values in the Western Region.
   • Use "@" to display the @ symbol.
   • Use "\" to display double quote. The double quote syntax isn’t limited to a single character. In general, you can use a backslash escaped string inside double quotes. For example: "1234567890\\abc\\d\"x\"yz!@#$%ˆ&*()-+=_{}[];:'|?/><,.` ~" is display as 1234567890\abc\d"x"yz!@#$%ˆ&*()-+=_{}[];:'|?/><,.` ~
   • Use \ to display the \ symbol.

Display Running Sums and Relative Values in Pivot Tables

You can use the Layout pane to display running sums or the relative value of measure columns in pivot tables.

Topics

• Display Running Sums for Measure Columns in Pivot Tables

• Display Relative Values for Measure Columns in Pivot Tables

Display Running Sums for Measure Columns in Pivot Tables

In a pivot table, you can display numeric measures as running sums, where each consecutive cell for the measure displays the total of all previous cells for that measure. This option is a display feature only that has no effect on actual pivot table results.

Typically, running sums are displayed for duplicated attribute columns or for measure columns for which the option to show data as a percentage of the column has been selected, with the last value being 100 percent. For example, you can display a running sum and percentage, to view progress toward next year's revenue target of $2 million. Running sums apply to all totals. The running sum for each level of detail is computed separately.

Column headings aren’t affected when the running sum option is selected. You can format the column heading if you want it to indicate that the running sum option is in effect.

The following usage rules are in effect for running sums:

• A running sum is incompatible with the SQL RSUM function (the effect would be a running sum of the running sum).

• All running sums are reset with each new section. A running sum doesn’t reset at a break within a section or continued across sections.

• If a measure doesn’t display in a single column or in a single row, then the measure is summed left to right and then top to bottom. (The lower right cell contains the grand total.) A running sum doesn’t reset with each row or column.

1. Open the pivot table view in the view editor.
2. In the Layout pane, in the Measures area, click More Options for the row or column to be summed.
3. Select Display as Running Sum.

Display Relative Values for Measure Columns in Pivot Tables

In a pivot table, you can dynamically convert a stored or calculated measure into a percent or an index.

This shows the relative value of the item, compared to the total, without the need to explicitly create a calculated item for it. You can view the measure as a percentage between 0.00 and 100.00, or as an index between 0 and 1.

For example, if you’re using a pivot table to examine sales by product, then you can duplicate the sales measure and view it as a percentage of the total. This enables you to see the actual sales, and the percentage of sales, that each product accounts for.

1. Open the pivot table in the view editor.
2. In the Layout pane, click More Options for the item that you want to show as a relative value.
3. Optional: To duplicate the measure column, select Duplicate Layer.

   The item is displayed in the pivot table, with the same name.

4. Select Show Data As.
5. Select Percent of or Index of.
6. Select the appropriate value such as Column, Row, or Section.

   The column is displayed in the pivot table view.

   
   Description of the illustration views20.gif
7. To rename the column, click More Options then Format Headings.

   In the Edit Format dialog, enter a value in the Caption field.