On this page:

Overview

The Pivot Table macro allows you to display a pivot table based on tabular data.

You can define your own types of rendering by adding a pivot-values-rendering user macro.

You can perform calculations on non-numeric character string (such as $20, 4 hours ...) by defining a regular expression and a formula replacement string.

This macro can be easily nested within the Chart Macro to display a chart based on the pivot table generated from your tabular data.

JIRA macro can also be nested within the Pivot Table macro.

Availability

Server version

Parameters

^{The below table has been automatically generated by Macro Documentation Helpers add-on.}

Name	Description	Type	Default Value	Possible Values
Values	Column name of the body table(s) used to feed the values of the Pivot table	string
Type of Calculation *	Function (count, sum, average) or custom rendering types used to calculate the values of the Pivot Table	enum	count	count, sum, average, custom types
Rows	Column name of the body table(s) used to feed the rows of the Pivot table.	string
Columns	Column name of the body table(s) used to feed the columns of the Pivot table	string
Display Grand Total for Rows	Add a Grand Total column in the Pivot Table and compute a total for each row. This parameter is taken into account only if Rows parameter is not empty	boolean	false
Display Grand Total for Columns	Add a Grand Total row in the Pivot Table and compute a total for each column. This parameter is taken into account only if Columns parameter is not empty	boolean	false
Display Body Tables	The body table(s) can be displayed 'before' or 'after' the Pivot table	enum		before, after
Regular Expression for Values	Regular expression used to evaluate the values of the body table in order to replace them by the below replacement string. See examples here	string
Replacement String for Values	Replacement string corresponding to the above regular expression. The replacement string can be a formula that must follow EvalEx syntax. EvalEx is a handy expression evaluator that allows to evaluate simple mathematical and boolean expressions. See examples here	string

Using the Pivot Table Macro

To add the Pivot Table macro to a page:

Edit the page
Prepare a table with an heading row and some data in it. (See Screenshot 1 below)
Use autocomplete to add the Pivot Table macro. (Type '{pivot-table}')
The message "Please insert a table in the body" is displayed. It's because there is no data in the macro body yet (See Screenshot 2 below)
Click Insert to add the Pivot Table macro to your page.
Move your table in the body of the Pivot Table macro (See Screenshot 3 below)
Click the Pivot Table placeholder and choose Edit. The pivot table appears in the 'Preview Area'. By default, it displays the count of rows of the first column of the body table. (See Screenshot 4 below)
Select a column name in the Values parameter corresponding to a column of your table containing numeric values and change the Type Of Calculation parameter value to sum
Click Refresh in the 'Preview' area, to check that the pivot table sums now the values. (See Screenshot 5 below)
Select a column name in the Rows parameter corresponding to a column of your table
Click Refresh in the 'Preview' area, to check that the pivot table presents the sum of Values Parameter per Rows parameter. (See Screenshot 6 below)
Select a column name in the Columns parameter corresponding to another column of your table
Click Refresh in the 'Preview' area, to check that the pivot table presents the sum of Values Parameter per Rows and per Columns parameters. (See Screenshot 7 below)
Click Save to add the Pivot Table macro to your page.
Click Save again when you are ready to save the page.

Screenshot 1: Example of a table with an heading row and some data in it

Date of Sale	Sales Person	Item Sold	Color of Item	Units Sold	Per Unit Price	Total Price
10/01/2013	Imran	Notebook	Black	8	25000	200000
10/01/2013	Imran	Laptop	Black	18	24000	432000
10/02/2013	Larry	Laptop	Red	4	35000	140000
10/03/2013	Imran	Mouse	Red	6	850	5100
10/04/2013	Larry	Notebook	White	10	27000	270000
10/05/2013	Larry	Mouse	Black	4	8	32
10/05/2013	Imran	Mouse	White	200	8	1600

Screenshot 2: Missing table body message

Screenshot 3: Populate the Pivot Table macro body with a table

Screenshot 4: Pivot Table retrieved 7 records in the body table for the first column Date of Sale

Screenshot 5: Pivot Table summed Per Unit Price column

Screenshot 6: Pivot Table: Sum of Per Unit Price column per Sales Person

Screenshot 7: Pivot Table: Sum of Per Unit Price column per Sales Person per Color of Item

Examples

^{All the examples below have been automatically generated by Macro Documentation Helpers add-on.}

Pivot Table without column and row

Parameters in Macro Browser

Data in Macro Placeholder

Rendered data

Key	Value
Values	Per Unit Price
Type of Calculation (*)	sum

Pivot Table

Date of Sale	Sales Person	Item Sold	Color of Item	Units Sold	Per Unit Price	Total Price
10/01/2013	Imran	Notebook	Black	8	25000	200000
10/01/2013	Imran	Laptop	Black	18	24000	432000
10/02/2013	Larry	Laptop	Red	4	35000	140000
10/03/2013	Imran	Mouse	Red	6	850	5100
10/04/2013	Larry	Notebook	White	10	27000	270000
10/05/2013	Larry	Mouse	Black	4	8	32
10/05/2013	Imran	Mouse	White	200	8	1600

Sum of Per Unit Price
111866

Pivot Table with row

Parameters in Macro Browser

Data in Macro Placeholder

Rendered data

Key	Value
Values	Per Unit Price
Type of Calculation (*)	sum
Rows	Sales Person

Pivot Table

Date of Sale	Sales Person	Item Sold	Color of Item	Units Sold	Per Unit Price	Total Price
10/01/2013	Imran	Notebook	Black	8	25000	200000
10/01/2013	Imran	Laptop	Black	18	24000	432000
10/02/2013	Larry	Laptop	Red	4	35000	140000
10/03/2013	Imran	Mouse	Red	6	850	5100
10/04/2013	Larry	Notebook	White	10	27000	270000
10/05/2013	Larry	Mouse	Black	4	8	32
10/05/2013	Imran	Mouse	White	200	8	1600

Sales Person	Sum of Per Unit Price
Imran	49858
Larry	62008

Pivot Table with row and regular expression on values

Parameters in Macro Browser

Data in Macro Placeholder

Rendered datas

Key	Value
Values	Per Unit Price
Type of Calculation (*)	sum
Rows	Sales Person
Regular Expression for Values	(\$)(.*)
Replacement String for Values	($2)

Pivot Table

Date of Sale	Sales Person	Item Sold	Color of Item	Units Sold	Per Unit Price
10/01/2013	Imran	Notebook	Black	8	$ 2500.45
10/01/2013	Imran	Laptop	Black	18	$ 240.56
10/02/2013	Larry	Laptop	Red	4	$ 35000
10/03/2013	Imran	Mouse	Red	6	$ 850
10/04/2013	Larry	Notebook	White	10	$ 2700.50
10/05/2013	Larry	Mouse	Black	4	$ 8
10/05/2013	Imran	Mouse	White	200	$ 8

Sales Person	Sum of Per Unit Price
Imran	3599.01
Larry	37708.5

Pivot Table with column and row

Parameters in Macro Browser

Data in Macro Placeholder

Rendered data

Key	Value
Values	Per Unit Price
Type of Calculation (*)	average
Rows	Sales Person
Columns	Color of Item

Pivot Table

Date of Sale	Sales Person	Item Sold	Color of Item	Units Sold	Per Unit Price	Total Price
10/01/2013	Imran	Notebook	Black	8	25000	200000
10/01/2013	Imran	Laptop	Black	18	24000	432000
10/02/2013	Larry	Laptop	Red	4	35000	140000
10/03/2013	Imran	Mouse	Red	6	850	5100
10/04/2013	Larry	Notebook	White	10	27000	270000
10/05/2013	Larry	Mouse	Black	4	8	32
10/05/2013	Imran	Mouse	White	200	8	1600

Average of Per Unit Price	Black	Red	White
Imran	24500	850	8
Larry	8	35000	27000

Pivot Table nested within Chart Macro

Parameters in Macro Browser

Data in Macro Placeholder

Rendered data

Key	Value
Type	bar
Show shapes	true
Show Legend?	true
Forgive	true

Chart

Pivot Table

Date of Sale	Sales Person	Item Sold	Color of Item	Units Sold	Per Unit Price	Total Price
10/01/2013	Imran	Notebook	Black	8	25000	200000
10/01/2013	Imran	Laptop	Black	18	24000	432000
10/02/2013	Larry	Laptop	Red	4	35000	140000
10/03/2013	Imran	Mouse	Red	6	850	5100
10/04/2013	Larry	Notebook	White	10	27000	270000
10/05/2013	Larry	Mouse	Black	4	8	32
10/05/2013	Imran	Mouse	White	200	8	1600

JIRA macro nested within Pivot Table

Parameters in Macro Browser

Data in Macro Placeholder

Rendered datas

Key	Value
Values	Key
Type of Calculation (*)	count
Rows	Assignee
Columns	Status

Pivot Table

JIRA

Count of Key	Done	In Progress	To Do
Seuqra	1	1
admin		1	2

JIRA macro nested within Pivot Table nested within Chart Macro

Parameters in Macro Browser

Data in Macro Placeholder

Rendered datas

Key	Value
Type	bar
Show shapes	true
Show Legend?	true
Forgive	true

Chart

Pivot Table

JIRA

JIRA macro nested within Pivot Table with a regular expression to calculate Original Estimate

Parameters in Macro Browser

Data in Macro Placeholder

Rendered datas

Key	Value
Values	Original Estimate
Type of Calculation (*)	sum
Rows	Person
Regular Expression for Values	(((\d)([\.\|,]){0,1}(\d))( ){0,1}(w)){0,1}(\D){0,1}(((\d)([\.\|,]){0,1}(\d))( ){0,1}(d)){0,1}(\D){0,1}(((\d)([\.\|,]){0,1}(\d))( ){0,1}(h)){0,1}(\D){0,1}(((\d)([\.\|,]){0,1}(\d))( ){0,1}(m)){0,1}(\D){0,1}(((\d)([\.\|,]){0,1}(\d))( ){0,1}(s)){0,1}(\D*){0,1}
Replacement String for Values	ROUND((0$2 * 5 * 5) + (0$10 * 5) + 0$18 + 0$26/60 + 0$34/3600,0)

Pivot Table

JIRA

Task Id	Person	Original Estimate
1	Imran	1 week, 1 day, 1 hour
2	Imran	2 hours
3	Larry	1w 1d 1h
4	Imran	1w, 1d, 1h, 1m, 30s
5	Larry	12.51w 13.65d, 14.78h, 18.54m, 30.90s
6	Larry	2 weeks, 5 days, 6 hours
7	Imran	1 week

Person	Sum of Original Estimate
Imran	89
Larry	508

Pivot Table with pivot-values-rendering macro

Parameters in Macro Browser

Data in Macro Placeholder

Rendered datas

Key	Value
Values	Item Sold
Type of Calculation (*)	tick
Rows	Sales Person
Columns	Color of Item

Pivot Table

Date of Sale	Sales Person	Item Sold	Color of Item	Units Sold	Per Unit Price	Total Price
10/01/2013	Imran	Notebook	Black	8	25000	200000
10/01/2013	Imran	Laptop	Black	18	24000	432000
10/02/2013	Larry	Laptop	Red	4	35000	140000
10/03/2013	Imran	Mouse	Red	6	850	5100
10/04/2013	Larry	Notebook	White	10	27000	270000

Item Sold	Black	Red	White
Imran
Larry