Date hierarchies and pivot mode

  • 5 minutes

Date hierarchies

In analysis mode, the system generates date fields of the dataset in a Year-Quarter-Month hierarchy of three separate fields. This hierarchy is based on the normal calendar, not defined fiscal calendars in Business Central.

The extra fields are named <field name> Year, <field name> Quarter*, and <field name> Month. For example, if the dataset includes a field called Posting Date, then the corresponding date hierarchy consists of fields called Posting Date Year, Posting Date Quarter, and  Posting Date Month.

Currently, the date hierarchy only applies to fields of type date, not for fields of type datetime.

Pivot mode

You can use pivot mode to analyze large amounts of numerical data, subtotaling data by categories and subcategories. The pivot mode is similar to  pivot tables in Microsoft Excel.

To turn on and off the pivot mode, slide the Pivot mode switch in the  Columns pane. When you turn on the pivot mode, the Column labels  area appears in the pane. Use the Column labels area to group sum totals for rows into categories. Fields that you add to the Column labels area show as columns in the data area.

Building the data analysis in pivot mode involves moving fields into the three areas: Row groups, Columns labels, and Values. Columns that only have a few possible values are the best candidates for use in the Values column.

Analyze large amounts of data

If the dataset that you want to analyze exceeds 100,000 rows, the system suggests that you enter an analysis mode that's optimized for large datasets. Currently, two limitations are in place if you switch to this mode:

  • Formatting fields of the following four data types might change:

    • Currency

    • Decimals (always shown with two decimals)

    • Dates (always shown in the format YYYY-MM-DD)

    • Time zones

  • Fields that you use in pivot mode and add to column labels must have a low number of distinct values.

If you turn on pivot mode and drag a field into the Column labels area, where the underlying data for that field has too many distinct values, then the browser tab might become unresponsive and might eventually close. As a result, you need to start over in a new session. In this case, you shouldn't pivot on that field, or you can set a filter on the field before adding it to the Column labels area.