Pivot Tables: Creation of a Pivot Table as a Separate Form

You can create pivot tables and share them with other users as forms. You can place any pivot table in a workspace by adding it to the site map and then specifying the needed levels of access rights to the pivot table for the user roles available in the system.

Access Rights for Modifying Pivot Tables

MYOB Acumatica uses roles to restrict access to the system. Administrators assign users one role or multiple roles, and based on these roles, the users are then granted the appropriate levels of access to system objects. For details, see Managing User Access.

To be able to create, delete, or modify standalone pivot tables, you need a role that provides sufficient access rights (the Delete level) to the Pivot Tables (SM208010) form. By default, the built-in Administrator role provides the access rights for working with pivot tables created as forms.

Creation of a Pivot Table

You perform the following general steps to compose a pivot table:

  1. Preparation: You determine which generic inquiry will be used as a data source for the pivot table.
  2. Creation: You create a pivot table on the Pivot Tables (SM208010) form, specify the generic inquiry to be used as the data source in the Screen ID box of the Summary area, and configure the table layout. While configuring the table, you can preview it at any time.
  3. Publication: After you have previewed the finished pivot table and made any needed changes, you publish the table, which makes it available for other users.

Configuration of the Table Layout

The configuration of the layout of a pivot table in MYOB Acumatica is similar to this process in Microsoft Excel. You use multiple panes to configure a pivot table.

The Fields pane of the Pivot Tables (SM208010) form lists all the fields that have been added to the related inquiry on the Results Grid tab of the Generic Inquiry (SM208000) form, regardless of their visibility settings. You move fields between the panes by dragging them. When you click a field in the Filters, Rows, Columns, or Values pane (see Item 1 in the following screenshot), the system displays its properties in the Properties pane (Item 2). By using the settings in the Properties pane, you define how the data of the field is to be presented in the table.

Figure 1. Configuration of the layout of a pivot table


Application of Shared Filters

The generic inquiry that you select as a data source for your pivot table may have advanced shared filters, which are displayed as tabs on the inquiry form and contain filtered data that meets particular specifications. To reduce the amount of data in the pivot table for easier analysis, you can apply any advanced shared filter created for the generic inquiry. To do this, while configuring the pivot table, you select the filter in the Shared Filter to Apply box on the Pivot Tables (SM208010) form.

When the pivot table is displayed, the filter is applied to the generic inquiry, and then the pivot table uses the filtered data.

Data Filtering in Pivot Tables

As described in the previous section, when you are defining a pivot table on the Pivot Tables (SM208010) form, you can specify an advanced shared filter to be applied to the generic inquiry; the pivot table then uses the filtered data. In addition, you can filter the data in a pivot table in any of the following ways:

  • By the values of the fields added to the Filters pane: When you want to filter the data in a pivot table by fields that are not used in the table, you add these fields to the Filters pane during the design of the pivot table. The system displays these fields in the pivot table as quick filters, and the users can filter the data in the table to meet their current needs.
    Attention:
    When a field is added to the Rows pane and the resulting pivot table column has more than 100 unique records to display, for reasons related to system performance, the quick filter pop-up window does not display items for multiple selection.
  • By the values of fields added to the Columns or Rows pane: For fields that you add to the Columns or Rows pane, the system automatically adds simple filters to the pivot table.