Pivot Table Properties
The property panel contains all configuration settings for your Vizlib Pivot Table, organized into templates, dimensions, measures, sorting, data handling, appearance, interactivity, and pivot table settings.
This topic contains the following sections:
Templates
Vizlib Pivot Table supports Vizlib Templates, so you can pick the look and feel you want and apply it with one click.
Using Templates
You can find template settings in the property panel under Templates.
Figure 1: Templates
To use templates, click on View Templates to open the template screen. You'll see a list of available themes, so you can choose the theme you want and it'll be applied right away. If you need to change the template theme, select one and apply the changes with one click.
Figure 2: Templates Example
Managing Templates
If you find you need to make more changes to the template theme, you can use the settings in the Appearance tab to set the look and feel you want.
If you'd like to know more about managing or working with Vizlib Templates, please see our article on creating and saving templates here.
Dimensions
Vizlib Pivot Table supports an unlimited number of Dimensions.
Adding Dimensions
Click the Pivot Table to select and view its properties.
Click the Data section of the property panel to view the Dimensions and Measures.
To add a dimension, click Add or Add Alternative to add alternative dimensions that offer users different views of the data in one chart.
To select a field for your dimension, click inside the expression field and either scroll down through the list or start to type the name of the field, as it features predictive search and will suggest field names as you type.
Figure 3: Selecting and adding Dimensions
When you add a dimension, click the arrow ( >) on the right of the dimension field name to view the dimension settings.
Applying Dimension Settings
Dimension settings include:
Entering a dimension Label for the Field.
Check the box to Include Null Values in the chart.
Set a Limitation on your chart data, choosing from a Fixed number (e.g. the top 10 values), the Exact value, or a Relative value (e.g. only showing values above 50%).
Set a Calculation condition using an expression to determine whether the dimension is displayed in the pivot table.
Set the Dimension Visibility.
Figure 4: Applying Dimension Settings
Setting the Dimension Position - Rows
The Dimension Position can be set to Rows (left) or Columns (top).
If you choose rows to set the dimension to the left of the pivot table, you can customize the Styling by setting the Background Color, the Font Color, and the Left Column Width.
Figure 5: Setting the Dimension position - Rows
Setting the Dimension Position - Columns
If you choose to set the dimension position to Columns, this sets the dimension at the top of the pivot table. Other settings include:
Setting the Header Label Position.
Specifying Row Height.
Customizing the Background Color and the Font Color. Use the option for Dynamic Styling to apply different colors to dimension values.
Figure 6: Settings for using Dimensions position - Columns
Enabling Tooltips
Choose whether to enable Tooltips settings for both columns and rows. Settings include:
Choosing to use Tooltip Content, set to Auto (displays field value as a Tooltip), or enter a custom Tooltip Value. In the example below, the tooltip value is set to 'Hi there,' which displays as a Tooltip.
Select the Tooltip Color for the background to the text color.
Enabling a Search Icon.
Figure 7: Enabling Tooltips and applying settings
Applying Dynamic Styling
Dynamic Styling allows you to apply a custom style to rows or cells within the pivot table using an expression.
At the top of this section, the expression from the field List value(s), displays as its name. Click the arrow ( >) on the right to view the Dynamic Styling settings. Click Add Condition to apply further conditional coloring based on other criteria.
Figure 8: Applying Dynamic Styling to your pivot table
Adding an Expression or List of Values
You can apply Dynamic Styling with a defined Field expression or add a List of Values... separated by a specific sign (the default is a comma). The sign that acts as the separator for your list of values is defined in the next field, Separator.
The full name for the list of values field is List of value(s), separated by a specific sign; the default is a comma. Click the expression button to view or edit the expression.
Although the default separator is a comma, you can use semi-colons, pipe ( | ), the back ( \ ) or forward ( / ) slash, and hyphens ( - ).
Example of an expression: =Concat(aggr(if(SalesOffice='Lund',OrderID),OrderID),',')
Example of a list of values: ='Total EBITDA,Total OpEx,Indirect OpEx,Direct EBITDA,Direct OpEx'
Dynamic Styling Settings
Style settings include customizing the Background Color, Font Color, Font Size, Font Style, and Border (Font Color and Border Size).
Figure 9: Applying Dynamic Styling settings
Totals and Formatted Export Settings
Slide the toggle to hide or display Totals; set the Total label if you display the totals.
In the Formatted Export Settings slide, the toggle to automatically Export to Excel.
Click the Delete button to remove the dimension from the pivot table.
Figure 10: Totals and Formatted Export Settings
Turn Text into a Clickable Link
With Vizlib Pivot Table v3.13.0, you can turn a dimension defined as URL into a clickable link. Previously, the software displayed the dimension URL as a plain text. If users wanted to open the URL, they needed to copy and paste the text from the app into a browser. With Vizlib Pivot Table 3.13.0 you can transform a plain text into a clickable link using the Cell text is URL option in the Dimension settings. When you enable the Cell text is URL option, you can further define the Link label by inserting a custom text that's displayed instead of the URL.
Note: If the Pivot Table is in the Indent Mode and the URL dimension is presented inside a tree of dimensions on the left, a link is clickable only if the last dimension of this tree is a URL.
Measures
Vizlib Pivot Table supports unlimited Measures. You can see an example of how to add measures using the pivot table wizard here.
Measure
The first group of settings shows the Measure name, and a setting to enable Master measure formatting, which means formatting for the measure will be taken from the master item definition (you can find master items in the Custom Objects menu in the left hand panel). You can also enter a Calc. condition to display the measure only if the condition is met, and also set the Measure Visibility without entering a condition.
Note: Master measure formatting is not displayed for measures created using the Fields list.
Figure 11: Measure
Representation
Vizlib Pivot Table allows you to choose a Representation; the options are Text, Indicator and Progress. Text representations have no extra settings, while Indicator representations control settings for displaying an icon. There are two Indicator modes: Threshold allows you to set the Icon and Icon Color displayed for values less than, equal to and more than the threshold value, and Expression allows you to use an expression to control the Icon and Icon Color.
Figure 12: Indicator Expression
If you choose a Progress representation you'll choose a Bar Scale, Auto or Fixed (entering a fixed value). You can set the Progress Bar Color, show or hide Negative Bars, set Rounded corners and an Overflow border using the sliders, pick an Overflow border color, choose to display a Vertical Axis and pick Show Value.
Figure 13: Progress
Styling
For all representation types, you can set the Header Label Position, display the label as plain text, or select Text is HTML or Text is URL. You can also choose a Font Color and choose to Apply Style In Totals Cell or Apply Styles in Title Cell.
Figure 14: Styling
You can select a custom Font Size, Font Alignment, Font Style and Background Color if you need to, and choose to Apply Style in Title Cell.
Figure 15: Font
Border, Tooltip
You can choose between Auto and Custom Border settings, choosing a Border Color, whether to Apply Style in Title Cell and set a border Size. If you enable Tooltip, you can choose between auto and custom Tooltip Content, setting a Tooltip Color, and a Tooltip Value for custom content.
Figure 16: Border and Tooltip
Examples
Here are examples showing a pivot table with Indicators and Progress.
Figure 17: Indicators
Figure 18: Progress
Sorting, Data Handling
These settings control how your data is ordered and displayed in the Vizlib Pivot Table.
Sorting
The Sorting tab controls how you order your data and has a default setting of Auto for both dimensions and measures. You can switch to Custom sorting if you'd like to set your own values and Sort by expression, numerically or alphabetically.
Figure 19: Sorting
Vizlib Pivot Table currently supports YYY, YYYYMMM and MMMM date formats.
Data Handling
Data Handling contains settings relating to the data in the object. You can control the Data downloading limit, Suppress Zero Values, enter a Calculation condition using the editor, and also enter a message to display if the calculation is not fulfilled.
Figure 20: Data Handling
Appearance
The Appearance tab manages settings relating to the look and feel of Vizlib Pivot Table.
General
In the General tab, you can choose to show or hide a title, add title details using the expression editors and show or hide the pivot table details.
Figure 21: General
Alternate States
Alternate States has settings related to adding the object to master visualizations. You can find more about alternate states here.
Figure 22: Alternate States
Columns / Rows Definition
Columns / Rows Definition contains several sections which handle the appearance settings for pivot table columns and rows. The Visibility settings allow you to display or hide measure titles in the pivot table definition panel, showing the components used to construct the pivot table.
Figure 23: Visibility
Definition Styling controls the background and font color for the definition panel.
Figure 24: Definition Styling
Enabling Button Borders allows you to choose color, radius and size of borders for the pivot table buttons.
Figure 25: Button Borders
Header Cells
The Header Cells panel controls the appearance of cells in the pivot table header.
Header Settings controls the Header Row Height, and Cells Text has settings for Horizontal Alignment and Wrap Text.
Figure 26: Header Settings
Cells Styling allows you to choose a background color and font color, size, family and style. There's also a setting for displaying a shadow with cell text.
Figure 27: Cells Styling
Enabling Borders allows you to set the border color and size.
Figure 28: Borders
Note: If you want to Hide the Border to remove all color from the top borders you will see that the resize column element is still present. To remove this you can type in transparent into the Border color input box.
Figure 29: Column control border still visible
Figure 30: All borders are transparent
Null Cells allows you to set the background and value color for any cells holding a NULL value, as well as letting you define your own Null Symbol.
Figure 31: Null Cells
Rows (Left) Cells
Rows (Left) Cells controls the appearance of cells in the pivot table rows. Column Settings controls the Expand Button Position, and Cells Text has settings for Horizontal Alignment and Wrap Text.
Figure 32: Cells Text
Cells Styling allows you to choose a background color and font color, size, family and style. There's also a setting for displaying a shadow with cell text.
Figure 33: Cells Styling
Enabling Borders allows you to set the border color and size.
Figure 34: Borders
Null Cells allows you to set the background and value color for any cells holding a NULL value.
Figure 35: Null Cells
Columns (Right) Cells
Rows (Left) Cells controls the appearance of cells in the pivot table rows.
Cells Text has settings for text alignment and wrapping within a cell.
Figure 36: Cells Text
Cells Styling allows you to choose a background type, background color and font color, size, family and style. There's also a setting for displaying a shadow with cell text.
Figure 37: Cells Styling
Enabling Borders allows you to set the border color and size.
Figure 38: Borders
Null Cells allows you to set the background and value color for any cells holding a NULL value.
Figure 39: Null Cells
Zero Value Cells allows you to set the background and value color for cells holding a ZERO value.
Figure 40: Zero Value Cells
Totals
The Totals settings control the appearance of the cells which display totals for pivot table values.
Position allows you to align the total value within a cell.
Figure 41: Totals Position
Totals Styling allows you to choose a background type, background color and font color, size, family and style.
Figure 42: Total Styling
Interactivity, Actions
The Interactivity tab contains settings related to showing or hiding functions when the app is in analysis mode.
Interactivity
Pivot Table mode converts the table to read-only mode, where selections are disabled when a cell is clicked. Choosing Allow Selection on Measure selects the related dimension value when the measure is clicked. In the Pivot Header Actions section there are settings to show or hide various controls: Show/Hide Export Button Icon, Show/Hide Alternative Selector and Hide False Dimension/Measure Calculation Condition, where the user won't see dimensions and measures where the Calculation Condition is set to false. Show Maximize Icon controls the ability for users to maximize objects.
Figure 43: Pivot Table Mode, Pivot Header Actions
In Formatted Export Settings, you can choose a format to Export to Excel, Export column width, Include selections and titles, select an option for Export Icon Settings and switch the Loading screen between Vizlib and Qlik Sense.
Figure 44: Formatted Export Settings
Enable or Disable Formatted Export
You can enable or disable the formatted export using the Allow formatted export option in the Property Panel > Interactivity > Formatted export settings.
Figure 45: The Allow formatted export option in the Property Panel
When the Allow formatted export option is Disabled, all formatted export options are unavailable. The end users can't export formatted data. The feature applies to Vizlib Pivot Table 3.12.0 and above.
Actions
The Actions settings integrate the pivot table with Vizlib Actions. If an action is added, it will run after the user clicks on a cell to make a selection. Currently, the actions Vizlib Pivot Table supports are Lock Field, Lock All Selected, Unlock All Selected, Activate Next Sheet, Activate Previous Sheet, Activate Sheet, Open URL, Set Variable, and Sleep.
Figure 46: Actions
Integrating with Vizlib Actions will allow you to drill through the data and learn about the underlying data which creates the pivot table. You can find out more about Vizlib Actions in the reference here.
Pivot Table Settings
Pivot Table Settings provide you with options to control the structure and design of the Vizlib Pivot Table object.
Scrollbar
The Scrollbar settings control the starting point for the horizontal scroll bar within the pivot table.
Figure 47: Scrollbar
Rows
With the Rows (left) menu, you can set the row Height to Auto or Fixed using the slider, enable a hierarchical view with Indent Row, set the Indent left margin, and have the pivot table set to Always Fully Expanded.
Figure 48: Rows
Columns
You can control the pivot table columns with Columns, setting Stretch Columns to either Fill to width or Size to data, set an automatic Column Size (or have a Custom width using the slider), and control the width of the column containing dimension names, setting Left Dim Mode as Pixels or Percentage.
Figure 49: Columns
Highlight
The settings related to highlighting allow you to choose to Highlight Rows On Hover, Highlight Columns On Hover, choose a setting for Highlighting Dimension Values (Last Level, All Levels or Disabled), and set a color for the Hover Background.
Figure 50: Highlight