In this article, we’ll tell you about the Pivot Table element features that have become available since the 2022.3 version.

---

Using this dashboard element, you can easily analyze data by matching row and column values across one or several Total columns. Besides, rows and columns may have nested data. In this case, when viewing a dashboard, you can expand rows and columns, thus detailing data. Now we have added the ability to sort the headers of the element rows and columns and define a condition of expanding rows and columns to detail data by default. But first things first.

To change the sort direction of the headers of rows and columns, you should open the Pivot Table editor, select a data column in an appropriate field, and set a necessary value for the Sort Direction property. You should know that there is a nuance associated with the sort direction of detailed data in rows and columns, but about that a little later.

Detailing in the Pivot Table

Drilling down in a pivot table does not mean moving to a new data view (another dashboard, report, element) but expanding the row or column header where detailed values are located. To create headers with detailing, you should add several data columns to the Rows and Columns fields of the Pivot Table element. You can read about how to create a drill down in the Pivot Table element in the documentation.

Now let's take a step back and investigate the topic of sorting for detailing fields. Here it's important to understand that if a pivot table contains several detailing columns for rows and columns, you can define the sort direction of headers for each of them. By selecting each data column in the element editor and changing the value of the Sort Direction property, you can achieve the result you need. For example, you can use the Descending direction for parent headers, the Ascending direction for child headers, and without sorting for sub-detail headers. As it was mentioned above, by default, the Ascending sorting mode is applied to all headers of rows and columns.

---

Expanding rows and columns by default

By default, all values of rows and columns are collapsed when there is detail. When viewing a dashboard in the viewer or the Preview tab, you can expand a header to detail by clicking on it. Now you can define a condition of expanding by default. In other words, when carrying out a condition, a header of a row or column may be detailed, i.e., expanded automatically when viewing a dashboard. Conditions of drill-down values of rows or columns are formed in a special editor.Important to know!

Before we take a closer look at the Condition editor, the two moments should be mentioned.

• First, the ability to expand is relevant only when data detailing is formed in a pivot table. In other words, when more than one data column is added to the Rows and Columns fields;


• Secondly, you can define a condition of data expanding for each drill-down level. For example, categories will be expanded starting with the second, and products in these categories will only be if their names match.

---

Forming a detail condition

To open the Condition editor, you should select a data column in the element editor from the Rows and Columns fields and click the Browse button next to the Expand property in the Properties panel. The editor contains two variables – index and value. The index variable is actually an ordinal number of a row (from top to bottom) or column (from left to right). The value variable is a value of a row or column, i.e., actually the name of a row header or a column in a pivot table.

Using these variables and operators (logical and mathematical), a condition of detailing rows or columns is formed. After, if the condition is carried out, a row or a column is expanded in the viewer by default. If the condition is not carried out, a row or a column will be collapsed. However, it can be expanded with a click when viewing a dashboard.

For example, if you can specify the index > 2 expressions in the editor of the row expanding condition, it means that rows after the second will be detailed by default. If you specify the value == "CategoryName" expression in the column expanding condition editor, the column of the pivot table with the name of the CategoryName header will be expanded. Thus, a condition of rows or columns expanding is formed by default. Besides, using logical operators, you can create a complex expanding condition, for example, index > 2 && value == "CategoryName". In this case, a row or column will be expanded if its serial number exceeds 2 and the header name is CategoryName.

For example, for categories, we specify the condition index > 2 && value == "Condiments", and expand all rows starting from the second, but only if the category name is Condiments. For products, we specify the expression value == "Genen Shouyu", and expand rows of all products if their name is Genen Shouyu.

---

New properties considerably advance features of the Pivot Table as an analysis element for your dashboards.