projectdoc Toolbox

Run calculations on a set of result documents and renders the results.

Identifier: projectdoc-calculation-macro
Since: 5.2

Provide a query to select a set of documents and use formulas to calculate a couple of values. One of these values is rendered, the rest can be accessed by additional macros.

The advantage is mainly that calculating a couple of values is calculated with a higher performance.

Parameters

The macro is configured by the following parameters.

Doctype(s)

Specify the type of the documents to select. If documents from more than one document type are to be selected, enumerate them in a comma-separated list. Leave blank to select documents from any doctypes.

If a property selected from a document is not supported by a doctype, a blank cell will be rendered.

Space Keys

To limit the search on documents to the spaces with the given keys. Use this if you want to search in several spaces.

Leave blank, if you want to search in the current space only. Use "@all" to search in all spaces.

Space key may reference space categories introduced by the hashmark (e.g. #myproject).

Refer to Search Space for more information on selecting spaces by their Confluence Space Category (specified via the space admin page).

Where

A Lucene search expression to filter on the results. If the property contains spaces, remove them or use $[...].

Examples:

Name = "foo" AND ShortDescription NOT \"bar\"
Name = "foo" AND $[Short Description] NOT \"bar\"

Please refer to Search Tips for information on limitations and extensions on the search expression syntax provided by projectdoc.

Since 4.13

Since version 4.13 the parameter supports to reference a space property. The name of the space property has to be prefixed with the paragraph sign ('§').

For instance, if the value for the where parameter is specified by the space property my-where, then the value of the select parameter is §my-where.

Curly Braces

Curly braces may cause problems on some instances of Confluence when used in a string parameter field of a macro. This is especially true (and reproducible) if you use an opening and an immediate closing bracket like this: "{}".

This is a known issue (CONFSERVER-33399) and is also discussed in Do curly braces in string macro parameters break the macro?

To work around this problem you may escape the curly braces with a backslash as in

$[Story Points] = $\{Magic Value\}

You need to use this workaround if you cannot save the page (as described in the issue above). Otherwise it is just a failed rendering of the macro in the macro editor.

This issue is solved since version 5.0.

Since version 2.0 of the projectdoc Toolbox Deep Links are supported for property references (on the right side) as an experimental features.

$<Story Points> = [${Master->Ref Story Points}]

Note that you cannot use deep links on the left side of the where clause without Materialization. Since version 4.5 this is supported by property control mat, by space property Materialize by Doctype, or by Doctype Descriptor. Materialization is also possible in prior versions of the projectdoc Toolbox with a little more verbose approach. Read Materialize Properties for more information on this.

Sort By

The comma-separated list of document property names to use for sorting.

Per default the sort key then the name of the document is used.

The last modification date is the last arbiter if all other properties are equal.

Since 4.13

Since version 4.13 the parameter supports to reference a space property. The name of the space property has to be prefixed with the paragraph sign ('§').

For instance, if the value for the sort parameter is specified by the space property my-sort-by, then the value of the select parameter is §my-sort-by.

Sort Order

Add a '+' (default) for ascending, a '-' for descending order.

Sort Directives

The directive selects the sort order breadth first (display direct children, then children at level two, the three, and so on) or depth first (run from the root node to the first leaf, second leaf, and so on until no leaf is left, then proceed with the parents sibling and so on). The sorter takes the position of the child (as specified via the space content tool to reorder pages) into account. This may be called the natural sort order since it is the sort order imposed by Confluence and it does not depend on any document property.

Note that all documents in the result set must have a common ancestor.

Directives cannot be used together with other sort constraints. A directive starts with a hashmark (#).

Directive	Description	Syntax
Breadth First	A breadth first sort of a page tree.	`#BREADTH_FIRST`
Depth First	A depth first sort of a page tree.	`#DEPTH_FIRST`

There are three different implementations to choose from. Per default the Memory Implementation is used, which should be fine for almost all use cases.

Implementation Description Syntax

No Memory The sorter does not use additional memory to speed up sorting. #BREADTH_FIRST:no-mem
#DEPTH_FIRST:no-mem

Memory

The sorter uses additional memory to store intermediate results for reuse. This speeds up the sorting process for larger result sets.

This is the default implementation.

#BREADTH_FIRST:mem
#DEPTH_FIRST:mem #BREADTH_FIRST
#DEPTH_FIRST

Materialize

The sorter materializes the complete subtree and stores it for all look-ups. This is typically the fastest sorter, but requires to know the root node in advance (root page ID).

If the page identifier is not specified, the Memory implementation is used.

#BREADTH_FIRST:mat:{root page ID}
#DEPTH_FIRST:mat:{root page ID}

Artificial Properties

The projectdoc Toolbox provides a number of artificial properties, some of which may be helpful for sorting.

Property Name	Example Value
Creation Date§	20151008
Creation Timestamp	0000001444337808000
Last Modification Date§	20170429
Last Modification Date Timestamp	0000001493416800000

Type Conversion

Add a type descriptor of the form

@{type/pattern}

The type is mandatory if the additional type descriptor is given. Valid values are

date
number

The pattern is optional to define a parsing pattern for the specified type.

Here are some examples

Sort By	The sort order is defined by the ...
`Name-`	... alphanumerical order of names, descending.
`Calendar Week@{number}`	... numerical order of the calendar week, ascending.
`Date of Birth-@{date}`	... date of birth, descending.
`Date of Birth@{date/dd.MM.yyyy}`	... date of birth, using the defined date pattern.

Identifier

Unique identifier of the rendered content.

Exclude Self

If checked excludes this document from the query result.

Restrict to immediate Children

If checked, only immediate children of the current document are valid as hits.

Render individual Results

If checked there is a data attribute added to the rendered element for each line result.

If not checked only the data attributes provided by default are added.

Data Attribute		Description
`data-projectdoc-result-count`	Provided by Default	The size of the result set. Note that the count of formulas may be smaller than this number if documents in that set do not provide all values required for the Line Formula.
`data-projectdoc-result-...-N`	Requires value of true	The infix for this attribute name (`...`) is derived from the name of the formula that created it. The 1-based index of the document that provided the values for it.
`data-projectdoc-finalresult-...`	Provided by Default	The final result of the formula with a derived name of the formula is used as a suffix.

CSS Classes

List of HTML class names to be added to the rendered HTML element.

Render

Controls the rendering of the calculated value.

The following values for this parameter are valid.

Parameter Value Description

first The value calculated by the first formula is rendered (default).

none

Nothing is rendered as text, but the element with the data elements still is.

Choose this option if all calculated values are rendered at different locations down the document.

last

The value calculated by the last formula is rendered.

Choose this option if your formula requires the result of prior defined formulas

Render no hits as blank

If the query found no hits, the result is rendered as a short text message. If a result with no hits should not be rendered at all, check this box.

No Hits Text

Text to be rendered if no hits are shown.

This allows to customize the text message.

Macro Body

The body contains the set of formula specifications to calculate values. Each specification of a formula contain a

Name,
the Aggregate Formula,
the Format,
the Line Formula,
the Input Mapping, and
the Output Mapping.

The elements of the specification are provided in any order within the cells of a table.

Name

Format

Line Formula

Aggregate Formula

Input Mapping

Output Mapping

...

Name

Identifies the formula and the calculated result.

Aggregate Formula

The formula to aggregate the single values extracted by the Line Formula.

The formula may contain placeholders to values of the document that contains the macro.

The formula may use the placeholders ${SUM} and ${COUNT}. Formulas may use values calculated by formulas defined in a line before it.

Placeholder	Description
`${SUM}`	The sum of values calculated by the Line Formula.
`${COUNT}`	The count of documents that provided a value for the Line Formula. For instance if a document does not apply properties for all values of the Line Formula, it does neither contribute to the sum nor to the count.
`${`NAME_OF_FORMULA`}`	Where NAME_OF_FORMULA stands for the name of a formula that has been define above the formula that wants to use it.
`${`NAME_OF_FORMULA`$}`	The sum value calculated by the formula named NAME_OF_FORMULA.
`${`NAME_OF_FORMULA§`}`	The count value calculated by the formula named NAME_OF_FORMULA.

Examples for Aggregate Formula

Use the sum

${SUM}

Use the average

${SUM}/${COUNT}

Format

The format used to translate the calculated value, both of the Aggregate Formula and the Line Formula.

The value is a valid number format in Java and defaults to decimal.

Valid values are integer, decimal, currency, and anything that can be parsed by DecimalFormat.

Line Formula

The formula applied to calculate from each document in the result set.

Use placeholders, like ${Document_Propery_Name} to select values from the documents in the result set.

Examples for Line Formula

Calculating story points

${Story Points} * ${Story Weight}

Add up the team sizes

${Team Size}

Input Mapping

Not yet implemented

A table to map an input to be applied to a value read from the documents.

This allows to translate text to double values.

Output Mapping

A table to map a double result value to a textual value, such as the Status Macro.

The first column allows single numeric values and ranges of the format:

Range format

D MIN ; MAX D

Token Description

D

The delimiter D may be either [ or ].

If [ is specified for
- the start value, then the start value is included.
- the end value, then the end value is excluded.
If ] is specified for
- the start value, then the start value is excluded.
- the end value, then the end value included.

MIN

The minimum value of the range.

If omitted, the minimum value of the range is the absolute minimum value. Any value smaller than MAX is accepted.

MAX

The maximum value of the range.

If omitted, the maximum value of the range is the absolute maximum value. Any value greater than MIN is accepted.

The defined mappings are processed from top to bottom. The first matching rule provided the representation.

Where one space is shown in the format above there may be any number of spaces, including zero spaces.

Range examples

Every value greater than or equal to 10 and smaller than or equal to 30 is accepted

[10;30]

Any number smaller than or equal to 30 is accepted

[;30]

The number must be exactly 30 - both representations are correct

30

[30;30]

Every value greater than 10 is accepted

]10;]

Example configuration for the Output Mapping

Output Mapping

[;10]	GOOD
]10;20]	GREAT
]20;]	TOP NOTCH!

Related Macros

Calculation Macro: Run calculations on a set of result documents and renders the results.
Calculation Result Macro: Renders a single result of a calculation provided by the Calculation Macro.
Property Calculation Macro: Execute a calculation in the context of a property value.

Space shortcuts

Page tree

Docs

Support

Free Blueprints

Tooling for Blueprints

Free Extensions

Spaces

Plugins for Maven

Incubator

Libraries for Java

Incubator

Add-ons for Confluence

Tools

Support

Team

Legal

Parameters

Doctype(s)

Space Keys

Where

Sort By

Sort By

Sort Order

Sort Directives

Artificial Properties

Type Conversion

Identifier

Exclude Self

Restrict to immediate Children

Render individual Results

CSS Classes

Render

Render no hits as blank

No Hits Text

Macro Body

Name

Aggregate Formula

Format

Line Formula

Input Mapping

Output Mapping

Related Macros

About

Keep up-to-date!

Powered by