Pivot Table Customization

SuiteAnalytics Workbook enables you to customize many aspects of your pivot tables. Click the following links for more information:

Customizing Numeric Values

You can customize the numeric values displayed in your pivot table using the Format window. To access the Format window, click the Field Menu icon Field menu icon next to a field in the Fields list or Layout panel and select Format... from the list.

Format window

The following options are available. After you make a selection, the Preview field displays your changes. To apply your changes to the table, click OK and refresh the table. To define your own syntax, click the Special tab and enter the syntax in the field provided.

  • Settings Applied From: Enables you to apply numeric formatting based on a specific language. To define each setting manually, select Custom. To use the numeric formatting set up in your NetSuite personal preferences, select User Preferences. For more information about personal preferences, see Setting Personal Preferences.

  • Decimal Places: The number of decimal places to include for each value.

    • Select Per Currency/Unit to use the decimal places of the currency or unit of measure for each value.

    • Select Unbound to place no limits on the number of decimal places used for each value.

  • Negative Values: The format to use for negative values.

  • Decimal Separator: The punctuation to indicate the decimal place. Select Custom to define your own punctuation.

  • Thousands Separator: The punctuation to separate groups of thousands. Select Custom to define your own punctuation.

  • Prefix: Enables you to define a prefix for the values in the field.

  • Suffix: Enables you to define a suffix for the values in the field.

  • Currency Symbol: Enables you to define where the currency symbol appears for dollar values in the field.

  • Units: Enables you to abbreviate the values of the field by thousands, millions, or billions.

Custom Number Formatting

You can customize number formatting in pivot tables in the special tab. Define your own syntax, or choose from the provided Predefined Values.

The following pivot table displays numbers numbers without custom formatting in the first column. In the second column, the numbers are custom formatted with the syntax: [0000ff]”gain “00.00,;[ff0000](##,#00.00)” loss”;[ff00ff]”nothing “0.00” zero”

Custom number formatting

The available symbols for custom numbering syntax are:

Symbol

Name

Application

#

Number Sign

Displays significant digits

0

Zero

Displays non-significant zeros

“ “

Double Quotation Marks

Displays text enclosed in the quotation marks. Text can be placed before or after numbers (positive, negative, or zero).

+

Plus Sign

Typically used to display positive numbers

( )

Paretheses

Typically used to display negative numbers

-

Minus Sign

Typically used to display negative numbers

,

Comma

Thousands separator, or scale a number by a 1000

.

Period

Decimal separator

;

Semicolon

Separates sections in the syntax. When you create custom number formats, you can specify up to three sections of syntax for positive numbers, negative numbers, and zero values, in that order.

[ ]

Brackets

Hexadecimal number (without #) in square brackets defines the color of the custom format section. For example, [0000ff] is blue.

Grouping Pivot Table Fields

Grouping fields in the pivot table Layout panel changes the granularity of the data presented in the table. By defining multiple fields as rows or columns, you can display subsets of data in the table.

For example, in the following transaction table, only the sales rep field has been defined as a row. The pivot table therefore only displays the total transactions for each sales representative.

Transaction pivot table.

If you also define the entity field as a row however, the table displays the total transactions per sales representative and customer.

Grouped pivot table fields.

Try grouping different fields in your pivot table layouts to analyze different subsets of data.

Compact and Expanded Mode

By default, if you group fields or include multicolumn hierarchical fields in your pivot table, the table expands horizontally which can make it difficult to view your data.

To display the table data vertically, click the Compact Mode icon Compact mode icon.

Additional Pivot Table Customization Options

To further customize the appearance of your pivot tables, SuiteAnalytics Workbook offers the following options:

  • To freeze column or row headers so that they are always visible as you scroll through your table, click the Freeze Column Freeze column icon or Freeze Row Freeze row icon icons.

  • To enable row or column highlighting when you point to a specific part of the table, click the Highlight Column Highlight column icon or Highlight Row Highlight row icon icons.

  • To rename a column or row, click the Field Menu icon Field menu icon and select Rename... from the menu.

  • To resize a column or row, drag one of the column or row boundaries until it is the size you want.

  • To change the width of a column based on its values, double-click the column header.

  • To expand or minimize the number of rows displayed in a column, click the Minimize column icon Expand column icon icons in the column header.

  • To expand or minimize the number of values displayed for a row, click the Expand row icon icon in the row header.

Related Topics

General Notices