Designing a View as a Flat or Pivot Table

To ease analysis and understanding, a view's Owner or Editor can set its data to appear as a flat table or pivot table.

Setting Up the Table's Layout

To represent a view as a table, click Layout , and select how to display the query results. For example, you can design the view as a flat table for general reference or analysis, or a pivot table to help summarize the data. Layout menu

  • Flat View displays the results as a flat table with all of the query's fields as separate columns.
  • Classic Pivot displays the results as a pivot view, with the selected fields as separate rows and columns, with totals at the end of each row.
  • Compact Pivot displays the data as a compressed pivot view, the fields selected to display as rows in a single hierarchical column.

To help summarize the table's data, click Subtotals to select whether to include totals for columns or row. If you include grand totals or subtotals, select whether to show them for only rows or columns.

For a pivot view, select which of the query results to use as filters, and which to display as rows or columns.

1
Click Fields , or click Pivot Builder on the right panel.
2
Under All Fields, select the query fields to include in the view.
3
To use a field as a filter, row, or column in the view, drag it to under View Filters, Rows, or Columns respectively.

Formatting Columns and Values

To filter or sort a table by a column, click its header and apply its settings.

  • To use the column as a filter, select the values to include in the table.
  • To sort the column's values in ascending or descending order, click AZ or ZA, respectively.

For each column, you can set up how its data should appear.

1
Click Format , and select Format Cells ; right-click on a value, and select Number Formatting; or click Column Formats on the right panel. Column Formats panel
2
Select the column to format.
3
In Column Type, select whether to display the column's data as a string, number, date string, or timestamp.
4
In Text Align, select whether to align the data to the left or right.
5
In Thousand Separator, select the separator to use with amounts in the thousands, such as a comma, period, or none.
6
In Null Value, enter the text to appear when a column is empty. To not display any text, leave blank.
7
For a number column, specify how to display its amounts.
  • In Decimal Separator, select whether to separate the integer and fractional parts of the number with a period or comma.
  • In Max Decimal Spaces, enter how many digits to display for the fractional part of the number.
  • In Currency Symbol, enter the symbol to use with monetary amounts, such as $.
  • Select whether to format the number as a percent.
8
Click Apply.

To help ease understanding or highlight special circumstances, you can set conditional formatting for values in the table.

1
Right-click on a value, and select Conditional Formatting; or click Format , and select Conditional Formatting . Conditional formatting
2
To add a new format, click Add (+).
3
For Value, select whether to apply the format to all values or only a specific value, and when to apply the format, such as "Less than 0" or "Empty".
4
For Format, select the font type, size, and color to display values as with the format applied.
5
Click Apply.