Menu

Create a pivot table (Pivot Table Editor)

  • Updated
Create a pivot table (Pivot Table Editor)

The first step in creating any pivot report is to create a pivot table. The pivot table defines the name of the report, the type of data to include (the dataset), and the specific data fields that may be used to build a report.

For example, to report on your top selling products over the past year, you would pull data from the Sales Analysis dataset including Sales, Products, and Year. You might also include Profit, so you can use the same pivot table to create reports for both the Top Selling Products and the Top Profitable Products over the past year from a single pivot table.

Saved pivot tables are available for anyone with adequate permission in your organization to use to create their own pivot reports.

  1. Go to Analytics > Pivot Views > Pivot Table Editor .
  2. Click New.

  3. In the Pivot Table box, type a name for your table, and then press the Tab key.
  4. From the Dataset list, choose the type of data that you want to include in your report. For details on the available data in each, look in the See Also links, below.
    • Inventory Analysis: Reports on value, turns and GMROI information to determine how your inventory products are performing. All calculations are based off of the Sales Order GP% Cost Column, which is set in your Company Master under the Pricing/Costing tab.
    • Sales Analysis: Reports on sales and profit figures, with grouping options like salesperson, customer, invoice date, product and vendor. All sales and cost information is based off of the price and cost of the product on the invoice.
    • Sales Variance Analysis: Reports on yearly sales and profit figures, allowing you to compare your company's progress this year to last year. All sales and gross profit information is based off of the price and cost of the product on the invoice.
    • Salesperson Reporting: Runs a sales analysis report for an individual salesperson.

  5. Now choose the data that you want to include in the report. All pivot reports include numeric data, which is grouped under the Measures heading, as well as categorical data. You must add at least one of each type of data to your table.

    For example,

    Under <Dataset> Fields, expand Measures. Drag Extended Sales to the <Pivot Table Name> Fields list. You will know when to drop it because the list will be outlined in red.

    Tip: Alternatively, double-click the field you want to add,

    All other data groups represent categorical data. For example, to add Products, expand Product, and then drag Product over to the <Pivot Table Name> Fields list.

    Tip: You can find date based information such as Invoice Year and Order Date under the Invoice Summary data group.

  6. For the Sales Analysis dataset, you can also apply date filters that run before data is pulled for report generation. This pre-filter limits the amount of data pulled so speeds up the initial load when you build report layouts,

    At the bottom of the Pivot Table page, select the Filter by Date check box, and then choose the

    • Range: The range of dates, such as Current YTD or 3 months rolling,
    • Determined By: On which you are basing that range, on the Sales Journal Period, Fiscal Period, or Invoice Date.
    • Range End: The last date from which you want to pull data, either the Last Complete Month or the Current Period.
  7. Click Save.

    8. To build the pivot report, either open the saved table from the Pivot Table Explorer, or click the Pivot Table button.

Related articles