Pivot Values Rendering User Macro

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:

  1. Go to  > General Configuration > User Macros
  2. Choose Create a User Macro
  3. Enter the macro details (see table below)
  4. Click Add
Macro details fieldDescription
Macro namepivot-values-rendering
Visibility 

Visible only to system administrators

Macro TitlePivot Values Rendering
DescriptionWhen 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.
CategoriesReporting
Icon URLEmpty
Documentation URLPivot 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.

typeOfRenderingDescription
tickDisplay a green tick if bodyTableValues contains at least one value
starDisplay one to 5 stars depending on the size of the bodyTableValues list
listDisplay all the bodyTableValues into the cell separated by a comma
minDisplay the smallest value of the bodyTableValues list
maxDisplay the largest value of the bodyTableValues list
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
  • Customize the above template to create your own renderers
  • Use HTML and Confluence-specific XML elements in the macro template. Details of Confluence's storage format are in Confluence Storage Format.
  • You can use the Velocity templating language. Here is more information on the Velocity project.