Reports: Customizing a Pivot Table

When designing a Pivot Table, Spiro Reports offers a wide range of options to customize it and improve the overall appearance in different ways. In this section, we will discuss various options provided by Spiro Reports to customize a Pivot Table that you create.

Customizing the Pivot Table Appearance 

Spiro Reports allows you to customize the look and feel of your Pivot Table. Spiro Reports offers options to both customize the elements inside the Pivot (hide row numbers, wrap text in header, etc) as well as enhance the complete look and feel of the Pivot Table by applying Themes. 

To customize the appearance of the Pivot, follow the steps given below:

  • Open the Pivot Table you would like to customize.
  • Select the Settings option in the toolbar. This will open up the Settings dialog box. You will notice that this dialog contains two tabs - General and Format

  • In the General tab, specify the title and description in the corresponding fields.
  • Under the Layout & Display section, you can choose to display the index number for the rows by selecting the Hide row numbers checkbox. By default, this will not be selected.
  • In case you wish to repeat the group label for each row, check the Repeat group label value in each row checkbox.
  • You can uniformly resize the columns by selecting the Set default column width to checkbox and then specify the required width in pixels in the provided field.
  • You could also choose to apply this new width to the already resized columns using the Apply to manually resized columns checkbox.
  • Null or empty value present in the underlying data of the pivot will be displayed as -No Value- by default. You can specify an alternate value that could be displayed instead of this in the Display 'Unknown' value as field.
  • Click Apply.

Applying Themes

Spiro Reports allows you to customize the look and feel of your Pivot table using colorful and attractive themes. You can customize your Pivot Table using the options provided to suit your taste. Please do note that this option is available only as a part of the new charting library that was released recently.

Watch the below video or read the below steps to learn about changing the Pivot Theme

  • Open the Pivot Table
  • Click the Themes button. The Themes dialog will open as shown below.

themes

  • You can select an appropriate theme to suit your needs and customize it using the options available. The Themes dialog allows you to select the,
    • Theme Layout: You can choose a layout from the available set of seven layouts.
    • Theme Color: Select a color that you wish to apply.
    • Font: Select the font for the text in your Pivot.
    • Zoom: You can Zoom in or Zoom out. This will increase or decrease the size of your Pivot Table.
    • Row spacing: You can alter the row spacing using the predefined options available.
  • As you choose the themes, the changes will be dynamically applied in the background.
  • If you wish to undo the changes click Reset.
  • If you want to reset the theme to the default theme click the Reset to default option.
  • Save the Pivot Table.

Show Missing Values

Show missing values feature is used to display the in-between missing values in a Pivot. This can be applied to a Date or a Category column. When creating a Pivot, if a particular data point does not have any value, then the Pivot would skip displaying that data. With this option, you can choose to display the record even if a point does not contain a value. 

Let us say that you are the manager of a team and would like to view your employee`s attendance details every week. In case an employee is not available for a particular day, his data will not be available for that day. Our database looks as shown below.

Let's now create a pivot as shown in the below snapshot to view the number of employees present in the given week. To do that, drag and drop the Date and Employee Name column in the Rows shelf and Clock-in hours column in the Data shelf.

This Pivot does not contain any record of the employees who were not available (absent). To view the name of the employees who were not available for a particular day, you can enable the show missing value function for the Employee Name column.

You can either right-click on the column name and choose Show Missing Values or click the Settings option on the toolbar.
 

In the settings tab that appears, Under the Show Missing Values section, click Choose Columns link next to For columns in the "Rows" shelf. You can choose the columns for which you wish to show the missing values.

The Pivot that is generated now, will contain the data of the employees who were absent.

In case you wish to view the details of the employees based on their location, we will be making a small change in the existing pivot. Drag and drop the Location column under the Rows shelf.

Our Pivot will look as shown in the below snapshot. If you take a closer look, you will notice that this might not be the best way to display the data as it displays the name of all employees across all locations.

In this case, you can choose to apply the hierarchy function while listing missing values.

  • Click Settings in the toolbar and in the Settings tab that appears, click Choose Columns link next to For columns in the "Rows" shelf (Under Show Missing Values). 
  • Select the column (in our case Location)
  • Check Apply hierarchy while listing missing values
  • Click Apply

The Pivot will now look as shown below.

Formatting Options

To format the columns in the Pivot, follow the steps below:

  • Open the Pivot Table you would like to customize.
  • Select the Settings option in the toolbar. This will open up the Settings dialog box. You will notice that this dialog contains two tabs - General and Format.
  • In the Format tab, you can change the display Labels for the columns as needed.
  • You can set the display format of each column by clicking the Format link. The options in the Format column dialog will vary depending on the data type of the column. These options will be similar to that of the table column formatting. 
  • Click OK. The Pivot Table will be customized based on the settings provided.

Show/Hide Totals

In Spiro Reports, by default, subtotals of individual columns, and grand total of all the rows and columns will be automatically added to the Pivot Table. Spiro Reports also allows you to turn off these totals when they are not required.

To hide or show the subtotals and grand totals do the following:

  1. Click the Edit Design option in the toolbar.
  2. Select Show/Hide Totals option in the toolbar or right-click on the corresponding cell/column in the pivot table and select the Show/Hide Totals option in the popup menu that shows up.

  3. Under this option the following sub-options are available:

  • Subtotal (column specific): Select/Deselect this option to show/hide subtotals of an individual column. This option will be available only on right-clicking the corresponding column whose subtotal has to be shown/hidden.

  • Row Grand Total: Select/Deselect this option to display/hide the Row Grand Total column in the pivot table.

  • Column Grand Total: Select/Deselect this option to display/hide Column Grand Total row in the pivot table.

  • All Subtotals: Select/Deselect this option to show/hide all subtotals in the pivot table.

Sorting a Pivot Table

In Spiro Reports, by default, a pivot table data will be sorted in ascending order by the values of the columns from the source table that you assign to Row orientation in a Pivot Table. Spiro Reports allows you to change this default sort order in a lot of different ways. Below is a brief description of various ways to sort a Pivot Table.

Sorting a Pivot column by its values (by the values of the columns in Row shelf): This option allows you to sort Pivot Table column data in ascending or descending order by its actual values.

To sort a pivot table by its column values:

  • Right-click the column header or on any cell of the corresponding pivot table column whose values has to be sorted.
  • In the pop-up menu, select the required sort order and then the By Column (column specific) option.

For example, if a pivot table has Product category and Product columns in Row shelf (Row Orientation), initially the Product Categories and Products will be ordered alphabetically in ascending order. When corresponding columns are sorted in descending order as described above, Pivot data will be rearranged as shown in the screenshots below.


Sorting a Pivot Table column by its corresponding data values(by values of the column in the Data shelf): This option allows you to sort Pivot Table columns based on data values corresponding to each pivot column value.

To sort a pivot table based on its data values:

  • Right-click the data value column header or on any data value cell corresponding to a Pivot Table column value.
  • In the pop-up menu, select the required sort order and then select the column based on which you want to sort data values as shown below.

In the above example, when you right-click Central region and select Sort Descending -> By Product Category, Sales values in Central region corresponding to Product Category column will be sorted in descending order as shown below.

When you select Sort Descending -> By Product, Sales values in Central region corresponding to Product column will be sorted in descending order as shown below.

Sorting Pivot Table columns by their corresponding summary values: This option allows you to sort Pivot Table columns based on summary values corresponding to pivot column values.

To sort a pivot table based on its summary values:

  • Right-click the summary column's header.
  • In the pop-up menu, select the required sort order and then select the column based on which you want to sort summary values as shown below.

When you right-click Summary Column and select Sort Descending -> By Product Category, Sales values in the Summary column corresponding to Product Category column will be sorted in descending order as shown below.

When you select Sort Descending -> By Product, Sales values in the Summary column corresponding to the Product column will be sorted in descending order as shown below.

You can also sort rows by column values by clicking on the down arrow icon at the heading of the corresponding column. A down arrow indicates that the column is sorted in ascending order. An up arrow indicates the column is sorted in descending order.

Conditional Formatting

Conditional formatting feature allows you to visually highlight data cells in a pivot table with different styles based on matching conditions. You have to specify the required conditions/criteria for formatting. When data in a cell meets the condition, Spiro Reports applies the corresponding formatting style that you have specified to the specific cell.   


To apply conditional formatting:

  • Open the Pivot Table for which you want to apply Conditional Formatting.  
  • Right-click on any one of the cells in the data series for which you want to apply conditional formatting.  
  • In the menu, click Conditional Formatting.
  • The Conditional Formatting dialog opens with options for specifying the conditions and selecting colors for font and background. You can also insert texts and icons using the Additional Formatting Options dialog to highlight your condition.

  • Click the drop-down arrow under the Condition header and select the type of condition that you want to apply.
  • Type the matching value that you want to use for the condition in the Value text box.
  • Select the required colors for the font and background.

  • Click the four vertical dots icon to add text and icons.
  • You can add any number of conditions using the +Add Condition link.
  • Conditions specified will be evaluated from top to bottom and the corresponding formatting options will be applied to the data cell that meets the condition.   
  • Click OK after you have added all the conditions.
  • You can also view and modify all the conditional formats applied over the pivot in the Conditional Format tab in the Settings section.   

Related Articles:

Creating a New Pivot Table

Working with Pivot Tables