See also Front-ends to Tracker data

Plugin PivotTable

Introduced in Tiki 16.2

Use this wiki plugin to create dashboards with summaries of data in Tiki objects through the unified search index to produce a pivot table report of your choice. Initially this plugin works with tracker data, but other Tiki objects can be connected later with this plugin. Results for the variables of interest (tracker fields, as well as creation_date, modification_date and tracker_status of the items) are aggregated by criteria selected by the user.

It produces the JavaScript Pivot Table (aka Pivot Grid, Pivot Chart, Cross-Tab) implementation from Nicolas Kruchten with drag'n'drop (see the list of changes in each version).

Parameters

Create and display data in pivot table for reporting
Introduced in Tiki 16.1. Required parameters are in bold.
Go to the source code
Preferences required: wikiplugin_pivottable

Parameters Accepted Values Description Default Since
(body of plugin) Leave one space in the box below to allow easier editing of current values with the plugin popup helper later on
data text
separator: :
For example 'tracker:1' or 'activitystream' 0
dataCallback text Pass a custom javascript function to tweak the final layout and data traces before rendering them. 24.7
chartTitle text Override title when using Chart renderers. 16.3
menuLimit digits Pivottable menuLimit option override - number of entries to consider the menu list too big when filtering on a particular column or row. 16.2
inclusions text Filter values for fields in rows or columns. Contains JSON encoded object of arrays of strings.
xAxisLabel text Override label of horizontal axis when using Chart renderers. 16.3
yAxisLabel text Override label of vertical axis when using Chart renderers. 16.3
aggregateDetailsFormat text Uses the translate function to replace %0 etc with the aggregate field values. E.g. "%0 any text %1" 22.1
aggregateDetailsCallback text Use custom javascript function to build the aggregate details popup window. 24.1
colOrder text The order in which column data is provided to the renderer, must be one of "key_a_to_z", "value_a_to_z", "value_z_to_a", ordering by value orders by column total. key_a_to_z
height word Height of charts. You have to only put the value (Unit: px). For instance, use 500 for 500 pixels. 400px
lang text This helps to avoid pivotUI missing the choosen aggregator next time you change the site language. Default value: "site" if you want to keep using the site language site 26
rowOrder text The order in which row data is provided to the renderer, must be one of "key_a_to_z", "value_a_to_z", "value_z_to_a", ordering by value orders by row total. key_a_to_z
highlightChartType text 24.7
width word Width of charts. You have to only put the value (Unit: px). For instance, use 500 for 500 pixels. 100%
aggregatorName Count| Count Unique Values| List Unique Values| Sum| Integer Sum| Average| Minimum| Maximum| Sum over Sum| 80% Upper Bound| 80% Lower Bound| Sum as Fraction of Total| Sum as Fraction of Rows| Sum as Fraction of Columns| Count as Fraction of Total| Count as Fraction of Rows| Count as Fraction of Columns Function to apply on the numeric values from the variables selected. Count
allowStickyHeaders (blank)
n
y
Sticky Headers for the Pivot Table when scrolling top or left Default value: No n 26
chartHoverBar y
n
Display the Chart hover bar or not. y 16.3
heatmapColors text
separator: :
17
highlightGroupColors text
separator: :
18.1
highlightRequest text
separator: :
Highlight items' values matching those coming from request like a search form POST. List pairs of tracker field names and incoming request variable names separated by a dash. 24.7
highlightGroup (blank)
y
n
Highlight items' values belonging to one of my groups in Charts. n 16.3
highlightMine (blank)
y
n
Highlight owned items' values in Charts. n 16.3
displayBeforeFilter (blank)
n
y
Load PivotTable results on initial page load even before applying "editable" filters. Turn this off if you have a large data set and plan to use "editable" filters to dynamically filter it. Default value: Yes y 21.1
overridePermissions (blank)
y
n
Return all tracker items ignoring permissions to view the corresponding items. n 18.1
rendererName Table| Table Barchart| Heatmap| Row Heatmap| Col Heatmap| Line Chart| Bar Chart| Overlay Bar Chart| Stacked Bar Chart| Relative Bar Chart| Boxplot Chart| Horizontal Boxplot Chart| Area Chart| Histogram| Density Histogram| Percent Histogram| Probability Histogram| Density Histogram Horizontal| Percent Histogram Horizontal| Probability Histogram Horizontal| Horizontal Histogram| Histogram2D| Density Histogram2D| Percent Histogram2D| Probability Histogram2D| Density Histogram2D Horizontal| Percent Histogram2D Horizontal| Probability Histogram2D Horizontal| Horizontal Histogram2D| Scatter Chart| Treemap Display format of data Table
translate (blank)
n
y
Use translated data values for calculations and display. Default value: No n 18.3
heatmapDomain text
separator: :
17
aggregateDetails text
separator: :
When enabled, clicking a table cell will popup all items that were aggregated into that cell. Specify the name of the field or fields to use to display the details separated by colon. Enabled by default. To disable, set contents to an empty string. 16.2
vals text
separator: :
Variable with numeric values or tracker field permNames, on which the formula from the aggregator is applied. It can be left empty if aggregator is related to Counts. Use permanentNames in case of tracker fields, separated by : in case of multiple fields function.
cols text
separator: :
Which field or fields to use as table columns. Leaving blank will use the first available field. Use permanentNames in case of tracker fields. Separated by colon (:) if more than one.
rows text
separator: :
Which field or fields to use as table rows. Leaving blank will remove grouping by table rows. Use permanentNames in case of tracker fields. Separated by colon (:) if more than one.



Notes on aggregateDetails:

  • The aggregateDetails accepts multiple field names or permNames separated by colon.
  • The aggregateDetails parameter is also enabled by default and can be disabled setting aggregateDetails to an empty string.
  • Each item has the associated object_link available by default and clickable in the popup where the aggregateDetails field data is shown.
    • It will work with other unified search index content entries (not only tracker items) but might be slow for large result sets.
    • It is only activated if aggregateDetails is not disabled. Therefore, there is a workaround to disable this feature for large sets of data (e.g. containing several or hundreds of thousands of items).

Basic Usage

Basic usage requires just to provide the data source (e.g. a tracker with id 1: "tracker:1" since Tiki16, or activitystream also since Tiki19 ), and the rest will be taken as default values by the pivot table plugin, and you will be able to edit it through the PivotTable UI itself. That will allow you to display all field names of the tracker, and will let you drag and drop them in rows or columns of the pivot table editor.

That will cover most use cases. However, if your dataset is huge, or the tracker has many fields, and some of them carrying heavy data (long text fields, or big files/images attached to the tracker items in files tracker fields), you can use an advanced syntax to filter the number of items or reduce the amount of tracker fields exposed to the pivot table to work with, so that performance of the pivot table plugin is fast again. See below for "Advanced Usage"

Example 1

After installing the Bug_Tracker_16 profile on a brand new Tiki 16, you will get a new tracker with id 1 to hold the data of the bug reports/issue tickets. When you add a few dozen items, you can use some syntax like the one indicated below to produce some demo pivot tables table with default values as a starting point, to let you start reviewing the data as wiki-wiki (quick) as possible.

This code:

Copy to clipboard
{pivottable data="tracker:1"}


Would produce with the data from that profile (at the time of this writing):

Click to expand
Click to expand


Once saved, you can click on any cell of the pivottable report, and you will be shown a popup with the information tracker items that produced the count for that cel, with a link to view the full record of each of the tracker items.

Click to expand
Click to expand


From there we can edit the Pivottable again through the PivotTable UI itself, and modify the variables to be used as row or column data, or add new variables in columns, change the type of table or chart produced, etc.

A table can even consider more than one value in a single dimension. The following example therefore uses both Status and Priority on the horizontal axis (meaning a column can have subcolumns):

Click to expand
Click to expand

Example 2

A default configuration for each parameter of the plugin can also be specified. For instance, the values considered in both dimensions can be specified, using the rows and cols parameters, as in the following example (which considers 2 values on the horizontal axis, as in the previous screenshot).

This code:

Copy to clipboard
{PIVOTTABLE(data="tracker:1" width="100%" height="500px" rows="bug_tracker_severity" cols="bug_tracker_bug_status:bug_tracker_priority" rendererName="Heatmap" aggregatorName="Count as Fraction of Columns" vals="bug_tracker_priority")} {PIVOTTABLE}


Would produce with the data from that profile (at the time of this writing):

Click to expand
Click to expand

Example 3

You can also make some charts:

  • Line Chart
  • Bar Chart
  • Stacked Bar Chart
  • Area Chart
  • Scatter Chart


For instance...

This code:

Copy to clipboard
{PIVOTTABLE(data="tracker:1" width="400px" height="300px" rows="bug_tracker_severity" cols="bug_tracker_bug_status" rendererName="Stacked Bar Chart" aggregatorName="Count")} {PIVOTTABLE}


Would produce:

Click to expand
Click to expand

Example 4 (subtotals since Tiki 18)

Since Tiki18 new renderers were added to allow displaying subtotal sums for rows in the table, through the addition of subtotal.js to the plugin:

  • Table With Subtotal
  • Table With Subtotal Bar Chart
  • Table With Subtotal Heatmap
  • Table With Subtotal Row Heatmap
  • Table With Subtotal Col Heatmap

Click to expand
Click to expand


If you click on the triangle at the left of each row name ("Severity" values, in this example), you will get the options of the next column ("Bug Status", in this example) contracted, hiding the different values of this other column, and showing only the subtotals for the field where you first clicked at (a "severity" value, or the whole column "Severity").

Click to expand
Click to expand

Example 5 (activity stream since Tiki19)

Since Tiki19, you can display data from the PluginActivityStream into the Plugin PivotTable.

Minimum syntax to let the user choose options throught the PivotTable UI:

Copy to clipboard
{pivottable data="activitystream"}


Example:

Copy to clipboard
{pivottable data="activitystream" rows="object:type" cols="modification_date" width="100%" height="1000px" rendererName="Bar Chart" aggregatorName="Count" inclusions="{}" menuLimit="500" aggregateDetails="object_type"}

Advanced Usage

If your dataset is huge (many thousands), or the tracker has many fields (many hundreds), and some of them carrying heavy data (long text fields, or big files/images attached to the tracker items in files tracker fields), you can use an advanced syntax to filter the number of items or reduce the amount of tracker fields exposed to the pivot table to work with, so that the good performance of the pivot table plugin is preserved.

You can use the filter or display commands (both from PluginList ) to indicate which items (filter) or tracker fields (display) you want to use, respectively, in the pivot table plugin.

Example:

Copy to clipboard
{display name="tracker_field_JobType"}


See:

Add creation_date, modification_date and status

You can also indicate if you want the creation_date, modification_date and status if the tracker items to be displayed as optional variables to be used in the report.

Copy to clipboard
{display name="creation_date" format="datetime"} {display name="modification_date" format="datetime"} {display name="tracker_status"}

Customize aggregation date values

See Derived Attribute of a date

Advanced Example 1


This code:

Copy to clipboard
{PIVOTTABLE(data="tracker:4" rows="bug_tracker_submitted_by:bug_tracker_severity:" cols="bug_tracker_bug_status:bug_tracker_priority:" rendererName="Heatmap" aggregatorName="Count as Fraction of Total")} {display name="tracker_field_bug_tracker_submitted_by" default=""} {display name="tracker_field_bug_tracker_severity" default=""} {display name="tracker_field_bug_tracker_bug_status" default=""} {display name="tracker_field_bug_tracker_priority" default=""} {display name="tracker_field_bug_tracker_version" default=""} {PIVOTTABLE}


Would produce with the data from that profile (at the time of this writing):

Click to expand
Click to expand


And once you click at the Edit Pivot Table button, you would see the controls to edit variable selection, but notice that you have less amount of variables to choose from than before; only the ones you have selected in the display commands of the plugin body above:

Click to expand
Click to expand

Advanced example 2

This code:

Copy to clipboard
{PIVOTTABLE(data="tracker:4" rows="bug_tracker_submitted_by:bug_tracker_severity:" cols="bug_tracker_bug_status:bug_tracker_priority:" rendererName="Heatmap" aggregatorName="Count as Fraction of Total")} {filter field="tracker_field_bug_tracker_bug_status" content="new"} {display name="tracker_field_bug_tracker_submitted_by" default=""} {display name="tracker_field_bug_tracker_severity" default=""} {display name="tracker_field_bug_tracker_bug_status" default=""} {display name="tracker_field_bug_tracker_priority" default=""} {display name="tracker_field_bug_tracker_version" default=""} {PIVOTTABLE}


Would produce the same as before, but restricting the data set to only those items tagged as new bugs (bug status is "new"):

Click to expand
Click to expand


Again, if you edit the pivot table, you will see that also have the restricted the number of fields, as well as the data points, that comply with your filtering criteria:

Click to expand
Click to expand


Advanced example 3

Since Tiki 16.2, any plugin using unified index search formatter and wikibuilder (aka filter, output, display, format, etc. wiki syntax, such as PluginPivottable ) now accepts {filter field=... editable=...} syntax to allow user enter a search value instead of hard-coding it. This means a trackerfilter-like functionality for unified index-based plugins.

You can see this feature in action if you apply profile Bug_Tracker_16

Therefore, this code:

Copy to clipboard
{PIVOTTABLE(data="tracker:4" rows="bug_tracker_severity" cols="bug_tracker_bug_status" rendererName="Heatmap" aggregatorName="Count")} {filter field="tracker_field_bug_tracker_priority" editable="content"} {filter field="tracker_field_bug_tracker_assignee" editable="content"} {filter field="tracker_field_bug_tracker_summary" editable="content"} {PIVOTTABLE}


Would produce the expected pivottable report, with some fields on top to allow the user to filter results before re-drawing the table or chart:

Click to expand
Click to expand


Aliases

Plugin Pivot Table | Plugin PivotTable | PluginPivot Table | Pivot Table | PivotTable | Plugin Pivot Tables | Plugin PivotTables | PluginPivot Tables | Pivot Tables | PivotTables | Plugin Data Pilot | Plugin DataPilot | PluginData Pilot | Data Pilot | Data Pilot |