Pivot Values Rendering User Macro
- Xavier Arques
On this page:
Overview
By default Pivot Table macro proposes 3 functions (count, sum and average) to calculate the values displayed in each cell of the Pivot Table.
Since Pivot Table version 1.5.0, the field Type Of Calculation can be enriched with custom types defined in a user macro named pivot-values-rendering containing parameter typeOfRendering of type enum. This user macro has to be added by a Confluence administrator (see Writing User Macros tutorial).
When such a pivot-values-rendering user macro is defined, the enum values of parameter typeOfRendering are automatically added to the Type Of Calculation field in the macro browser of Pivot Table macro.
Then, during the rendering of the Pivot Table, if Type Of Calculation contains a value different from [count, sum, average], Pivot Table macro will delegate the rendering of each cell to the user macro pivot-values-rendering.
For each enum value sets in parameter typeOfCalculation, the pivot-values-rendering macro must implement the rendering logic.
This user macro has access to the list of values computed for a cell thanks to the property bodyTableValues available through the Velocity context variable conversionContext.
## Retrieve the List<String> for the cell to render
#set ($bodyTableValues = $conversionContext.getProperty("bodyTableValues"))
Create the pivot-values-rendering User Macro
To add pivot-values-rendering user macro:
- Go to
> General Configuration > User Macros - Choose Create a User Macro
- Enter the macro details (see table below)
- Click Add
| Macro details field | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Macro name | pivot-values-rendering | ||||||||||||
| Visibility | Visible only to system administrators | ||||||||||||
| Macro Title | Pivot Values Rendering | ||||||||||||
| Description | When pivot-values-rendering user macro is defined, the enum values of parameter typeOfRendering are automatically added to the Type Of Calculation field in the macro browser of Pivot Table macro. | ||||||||||||
| Categories | Reporting | ||||||||||||
| Icon URL | Empty | ||||||||||||
| Documentation URL | Pivot Values Rendering User Macro | ||||||||||||
| Macro Body Processing | No macro body | ||||||||||||
| Template | The below template defines 5 new types of rendering for the Pivot Table macro.
pivot-values-rendering user macro ## Copy/Paste this code block in the Template Text area
## Macro Name: pivot-values-rendering
## Visibility: Visible only to system administrators in the Macro Browser
## Macro Title: Pivot Values Rendering
## Macro Body Processing: No macro body
##
## Developed by: xavier.arques@seuqra.com
## Date created: 08/05/2016
## Installed by:
## @param typeOfRendering:title=Rendering Type|type=enum|enumValues=tick,star,list,min,max|required=true|desc=The rendering types this macro supports
## Retrieve the List<String> for the cell to render
#set ($bodyTableValues = $conversionContext.getProperty("bodyTableValues"))
## list macro
## Return the $bodyTableValues values separated by a comma
#macro (list)
#set ($listValue = "")
#foreach ($value in $bodyTableValues)
#if ($listValue.length() > 0)
#set ($listValue = $listValue +", " + $value)
#else
#set ($listValue = $value)
#end
#end
$listValue
#end
## min macro
## Return the min value of the $bodyTableValues list
#macro (min)
#set ($minValue = $textUtil.parseFloat($bodyTableValues.get(0)))
#foreach ($value in $bodyTableValues)
#if ($textUtil.parseFloat($value) < $minValue)
#set ($minValue = $value)
#end
#end
$minValue
#end
## max macro
## Return the max value of the $bodyTableValues list
#macro (max)
#set ($maxValue = $textUtil.parseFloat($bodyTableValues.get(0)))
#foreach ($value in $bodyTableValues)
#if ($textUtil.parseFloat($value) > $maxValue)
#set ($maxValue = $value)
#end
#end
$maxValue
#end
## emoticon macro
## Display the emoticon given as parameter
#macro (emoticon $icon)
<ac:emoticon ac:name="$icon"/>
#end
#macro (stars)
#if ($bodyTableValues.size() == 1)
#emoticon("yellow-star")
#end
#if ($bodyTableValues.size() > 1)
#emoticon("yellow-star")
#end
#if ($bodyTableValues.size() > 2)
#emoticon("yellow-star")
#end
#if ($bodyTableValues.size() > 3)
#emoticon("yellow-star")
#end
#if ($bodyTableValues.size() > 4)
#emoticon("yellow-star")
#end
#end
## You can now define your own internal macro rendering and declare them below by adding the #elseif clause
## Here is the code executed when this macro is used
#if ($bodyTableValues.size() > 0)
#if ($paramtypeOfRendering.equals("tick"))
## No calculation: Display a green tick if there is at least one value on the cell
#emoticon("tick")
#elseif ($paramtypeOfRendering.equals("star"))
## No calculation: Display one too five 5 depending on the number of values
#stars()
#elseif ($paramtypeOfRendering.equals("list"))
## No calculation: Display all the body table values on the cell
#list()
#elseif ($paramtypeOfRendering.equals("min"))
## Display the smallest value of the list
#min()
#elseif ($paramtypeOfRendering.equals("max"))
## Display the largest value of the list
#max()
#end
#end
|