Customizing columns in views

You can customize how individual columns in Analytics views are displayed on screen, and displayed and processed in Analytics reports.

You can specify:

  • which columns from the table layout are included in the view
  • the order of columns
  • how numeric values are displayed in individual columns
  • column display names
  • column properties in print reports

Add columns to a view

You can add any of the physical data fields or computed fields defined in a table layout as columns in a view.

You can also add columns to a view based on ad hoc expressions created using the Expression Builder.

  1. Do one of the following:
    • Right-click a column header in the view to insert one or more columns to the left of the selected column
    • Right-click in the display area without a column selected to add one or more columns after the last column
  2. Select Add Columns.
  3. In the Add Columns dialog box, complete any of the following steps:
    • Click Add All to add all of the columns to the view.
    • Click an individual column in the Available Fields list and then click the right-arrow button to add it to the view.
    • Ctrl+click multiple columns in the Available Fields list and then click the right-arrow button to add them to the view.
    • Click Expr to open the Expression Builder and create an expression to add to the view.
    • If you want to use an expression to modify a column after you add it to the Selected Fields list, select the column and click Edit to open the Expression Builder.
  4. If the table has one or more related tables associated with it, you can add fields from any of the related tables to the view by selecting a related table in the From Table drop-down list, and adding the fields you want to include to the Selected Fields list.
  5. Click OK.

Remove columns from a view

You can remove unwanted columns from a view. The physical data fields or computed fields the columns are based on are still present in the table layout, and the columns can be re-added whenever necessary and remain available for use in other views.

  1. In the View tab, click the column header of the column you want to remove.

    You can Shift+click to select multiple adjacent column headers, and Ctrl+click to select multiple non-adjacent column headers.

  2. Right-click in the data area of the view and select Remove Selected Columns.

    Tip

    Do not right-click in the column header row if you have selected multiple columns, or only the column you right-click will be selected.

  3. Click Remove in the Remove Columns dialog box.
  4. To save your changes to the view, select File > Save Project and click Yes in the confirmation dialog box.

Resize columns in a view

You can resize one, several, or all columns in a view.

Do one of the following:

To manually resize a single column Position the cursor over the right-hand separator between column headers and drag the column to the required size.
To automatically resize one or more columns to the width of the column contents

Select the column header(s) and double-click one of the right-hand column separators.

You can Shift+click to select multiple adjacent column headers, and Ctrl+click to select multiple non-adjacent column headers.
To automatically resize all columns in a view to the width of the column contents Right-click in the data area of the view and select Resize All Columns.

Reorder columns in a view

You can modify the order in which columns are displayed in a view. If you want to reorder multiple columns, you need to select each column individually and move it to the appropriate position.

  1. Click the header of the column you want to move and drag it to a new location.

    You need to drag the column near the line separator between the two columns where you want to move the selected column.

  2. Reposition any other columns you want to move, and then select File > Save Project to save your changes to the view.

Rename columns in a view

You can rename one or more columns in a view, if required. Renaming a column changes only the display name and has no effect on the underlying name of the field in the table layout.

You can rename either the default column name used by all views associated with the table, or you can rename only the column name used by a particular view.

Note

Column names specified within individual views override default column names.

Rename the default column name

  1. Select Edit > Table Layout.
  2. In the Edit Fields/Expressions tab, double-click the column name you want to rename.
  3. Change the column name in the Alternate Column Title field.
  4. Click Accept Entry  and click Close  to exit the Table Layout dialog box.

    All columns that use the default name in all views associated with the table are automatically updated. Columns that have a name specified within an individual view are not updated.

Rename the column name used by a particular view

  1. Right-click the column header and select Properties.
  2. Change the column name in the Alternate Column Title field, and click OK.

Change a column from a view-level name to the default name

  1. Right-click the column header and select Properties.
  2. Delete the name from the Alternate Column Title field, and click OK.
  3. Switch back and forth between views, or close and reopen the table, to refresh the column name.
  4. Click Yes when you are prompted to save the changes to the table.

Format numeric values in a view

The formatting applied to numeric values displayed in Analytics views and reports can be configured at three different levels:

  • application level (global)
  • field level
  • column level

Note

These formatting options apply to numeric values in fields that use the numeric data type. They do not apply to numbers in fields that use the character data type.

Level

Overrides

Location to set formatting

Description

Application level

n/a
  1. Select Tools > Options > Numeric.
  2. Select or specify a format in Default Numeric Format.

Specifies the formatting for all numeric fields and columns in Analytics that do not have field-level or column-level formatting specified

Field level

Application level
  1. Open a table.
  2. Select Edit > Table Layout.
  3. Double-click a field name.
  4. In the Edit Fields/Expressions tab, select or specify a format in Format.

Specifies the formatting for an individual numeric field in a table layout, and the associated column in all views that use the table layout, unless column-level formatting is specified

For a change in field-level formatting to take effect in a view, you must remove the associated column and re-add it to the view, or create a new view containing the column

Column level

Application level

Field level
  1. Open a table.
  2. In the view, right-click a column header and select Properties.
  3. In the Modify Column dialog box, select or specify a format in Format.

Specifies the formatting for an individual numeric column in an individual view

If you have more than one view of a table, you can format the same column differently in the different views. For example, you could display a column with dollar signs in a view you use for printed reports, and omit the dollar signs in a view you use for analysis.

Numeric format syntax

The formatting applied to numeric values in Analytics is specified using a format mask that defines the required layout for each numeric value. For example, the format mask $99 could be used to display any value less than $100, because each 9 indicates that any digit between 0 and 9 can be displayed, with the dollar sign displayed next to the value.

Default format masks

The table below lists the default format masks that are available in Analytics. It also shows how each mask displays the number -100234.56.

You can use the default format masks as defined, modify them to suit your requirements, or define you own format masks.

Default format mask

Displays -100234.56 as:

-999999.99

-100234.56

-9,999,999.99

-100,234.56

(9,999,999.99)

(100,234.56)

-$9,999,999.99

-$100,234.56

($9,999,999.99)

($100,234.56)

9,999,999.99-

100,234.56-

Format mask components

Format masks are defined using the following components:

Component Description
Digit placeholder

The number 9 is used to specify each place where a single digit between 0 and 9 can be displayed.

If you specify more placeholders for digits than required, the extra digits and anything between them, such as commas, are not displayed. For example, if the format mask was specified as $9,999, a value of 310 would be displayed as $310.

You should specify formatting for the maximum number of digits that may appear in the column if you are using special formatting because any extra digits in your data are added immediately to the left of the leftmost 9, with no additional punctuation. For example, if your format mask is -9,999.00 a value of 1000000.00 will be incorrectly formatted as 1000,000.00 (with no thousands separator after the 1).
Negative value indicator

The negative value indicator can be:

  • a minus sign before or after the number: -100.00 or 100.00-
  • CR before or after the number: CR100.00 or 100.00CR
  • parenthesis: (100)

If no negative value indicator is specified, a minus sign before the number is used by default.
Thousands separator (if any)

Large numbers often have formatting applied between groups of digits to make them easier to read. The most common separators are commas (100,000.00) or spaces (100 000.00).

Some regional settings use a period as the separator and a comma to indicate decimal values. The default value used in Analytics is specified in the Thousands Separator text box in the Numeric tab in the Options dialog box (Tools > Options).
Decimal point indicator

A period is most often used to indicate decimal values, but a comma is used in some regional settings.

The default value used in Analytics is specified in the Decimal Place Symbol text box in the Numeric tab in the Options dialog box (Tools > Options).
Value indicator sign (if any) A dollar sign, percentage sign, and so on, can be added to the format to identify the type of value being displayed.

Modify column properties

Each column in an Analytics view has a number of properties that can be configured to modify how data is displayed on screen and in reports generated from the view. The properties configured for columns in a view do not alter the settings in the table layout or in other views associated with the table.

If you create a copy of a view, using the Save As command, the properties of columns in the view are copied, but subsequent changes to column properties in either view only apply to the view in which the changes are made.

  1. In the View tab, right-click the column title and select Properties, or double-click the column title.
  2. Make one or more changes in the Modify Column dialog box, using the table below as a guide, then click OK.
Column type Option Description
Any Column Contents

Allows you to modify the values displayed in the column.

Click Column Contents and use the Expression Builder to create or edit an expression. Only the display of the values is modified, not the physical data.

The expression in Column Contents must return the correct data type for the column. For example, an expression for a numeric column must return a numeric value.

Any fields referenced in the expression must exist in the table layout.
Alternate Column Title

The text for the display name of the column in the view.

Note

You are changing the column title in the current view only, which overrides the default column title specified in the Table Layout dialog box. For more information, see Rename columns in a view.
Width

The display width of the column on screen or in a report. Enter the width in characters.

For numeric columns, ensure that the column is wide enough to display the values with the largest number of digits. If a full numeric value cannot be displayed on screen, a string of number signs (######) is displayed to indicate an error.
Sort Key Column

Report output only

The column is used to sort data in report output.

Select Sort Ascending or Sort Descending to choose the sort order.

Note

You must also select Presort in the Report dialog box.
Numeric Format

The format used to display numbers in the column.

Select the appropriate format from the Format drop-down list, or enter a custom format.

If no format is specified, the Default Numeric Format is used, which is specified in the Numeric tab in the Options dialog box (Tools > Options).
Suppress Totals

Report output only

Prevents the values in the column from being totaled.

By default, Analytics automatically totals numeric fields in reports. You can suppress this behavior if the field contains data, such as unit prices, for which calculating the total is not meaningful.
Blank if Zero

Report output only

Substitutes blank entries for zero values in the column.

A report that includes a large number of zero values in a column is more readable if only the non-zero values are displayed.

Character

(must be the leftmost column or columns in the view)

 Break Column

Report output only

Creates a subsection each time the value in the column changes, and calculates a subtotal for all numeric fields.

You can specify multiple break columns to create nested subsections. All break columns must be positioned together, on the left side of the view, before the first non-break column.

This option is enabled only if the Sort Key Column checkbox is selected.

Note

You must also select Presort in the Report dialog box.

A heavy, dashed separator appears to the right of the break column or columns in the view.
Page Break

Report output only

Inserts a page break each time the Break Column value changes.

This option is enabled only if the Break Column checkbox is selected.
Suppress Duplicates

Report output only

Substitutes blank entries for repeated values in the Break Column.

For example, if the customer name is listed for each invoice record, you can make the report more readable by listing only the first instance of each customer name.

This option is enabled only if the Break Column checkbox is selected.