Pivot Tables: Data Presentation

Because pivot tables can contain a lot of data, users who analyze their data need the data to be presented in a meaningful way. On the Pivot Tables (SM208010) form of MYOB Acumatica, you can flexibly configure the format of the fields to be displayed in a pivot table.

Format of Field Values in Pivot Tables

If you want to change the format of the fields to be displayed in a pivot table, you can specify the required format in the Format box of the Properties pane on the Pivot Tables (SM208010) form. (This pane contains the display properties for any field that is selected in the Filters, Columns, Rows, or Values pane.) In this box, you can type one of the standard formats defined for the format function in .Net. In the following table, you can find examples of format definitions for numbers and the corresponding output.

Number or Percentage of the Total

On the Pivot Tables (SM208010) form, you can specify how the system should display numeric values by using the options in the Show Value As drop-down box of the Properties pane. The following options are available:

• Number
• % of Grand Total
• % of Column Total
• % of Row Total
• % of Total by Column Group
• % of Total by Row Group

The Values pane of this form contains a list of fields from the selected generic inquiry whose values are displayed in cells of the pivot table. You can add the same field to this pane multiple times and specify different display options for each entry.

For example, suppose that you want to analyze activities that your employees have with customers—that is, review the number of phone calls, sent emails, and completed work items for each customer. Also, you would like to know this activity type's percentage of the grand total of all activities that employees have with customers. For each activity type shown in a column, you add two values: a value that is the count of the activities of the type, displayed as a number; and the same value displayed as a percentage of the grand total of all activities for all customers.

Format Options for Date Fields

For date fields, on the Properties pane of the Pivot Tables (SM208010) form, you can use extended format settings to do the following:

• Round data with the specified accuracy: You can select the accuracy of calculating data gathered by date. For example, suppose that for each sales manager, you want to analyze sales amounts aggregated by quarter years. Suppose that sales amounts are collected daily, which means that the date field in the generic inquiry with the required data contains the day, the month, and the year (such as 04/11/2023). To aggregate the sales amounts by quarter in the pivot table, in the Round To box of the Pivot Tables form, you select Quarters, and the date will contain only the year and the quarter (such as 2023 Q4) in the pivot table.
• Aggregate data by a particular date part: You can select the part of a date field by which you want to aggregate data in a pivot table. For example, suppose that you want to analyze the seasonal demand of T-shirts for the past five years. To do this, you can build a pivot table that displays sales amounts aggregated by month. Suppose that the date field in the generic inquiry with sales amounts for T-shirts contains the month and the year (such as July 2023). To make the system aggregate data by month and display only months in the pivot table, in the Date Part box, you select Month.
• Display hierarchical column or row headers: If you want to configure the dates in columns or rows for a pivot table to meet your needs, you can set up a hierarchical structure of the parts of the dates. For example, suppose that you want to analyze the number of shipped laptops by month for the past three years. Suppose that the date in the generic inquiry contains the day, the month, and the year (such as 04/11/2023). In the pivot table, you can display a one-level column that will contain the following date values: Jan 2023, Feb 2023, and so on through Dec 2023. Alternatively, you can configure the system so that it displays two levels of column headers: In the first level, the system displays the years, and under each year, the system displays the months. To configure the system in this way, you add two copies of the date field to the Columns pane of the Pivot Tables form. For the first copy, in the Round To box, you select Years; for the second copy, in the Date Part box, you select Month.

Format Options for Fields with Segmented Keys

For fields for which segmented keys have been configured to have multiple segments, on the Properties pane of the Pivot Tables (SM208010) form, you can use format settings to do the following:

• Aggregate data by the particular segment: You can select the segment by which the system will aggregate data in a pivot table. For example, suppose that your company sells fruit and vegetables to restaurants. Suppose that the item class values consist of the following segments: the item category (FR or VEG), the item type (APL, ORG, CBR, or PTO), and the country of origin (such as ES, CN, or MA). If you want to use a pivot table to analyze the numbers of items of each type shipped to each customer, in the Segment box, for the Class ID field, you select the segment that corresponds to the item type. With these settings, the system will aggregate sales data by the item type (such as apples, designated by APL).
• Display hierarchical column or row headers: If you want to analyze data by using a combination of two segments of a field value, you can set up a hierarchical structure of the segments. Suppose that in the example described in the previous list item, you also want to add to the pivot table the country of origin for each item type. To do this, you add to the Columns pane two copies of the Class ID field. For the first copy, in the Segment box, you select the segment used for the item category; for the second copy, in the Segment box, you select the segment used for the country of origin.