- Created by Robert Reiner, last modified on 05. Jan 2024
projectdoc Toolbox
Run calculations on a set of result documents and renders the results.
- Identifier
projectdoc-calculation-macro- Since
- 6.0
- Page Size Support
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
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. |
|
| 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 ( 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
datenumber
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
Optional identifier for the HTML element.
Exclude Self
If checked excludes this document from the query result.
Max Hit Count
The maximum number of hits requested.
Restrict to immediate Children
If checked, only immediate children of the current document are valid as hits.
Restrict to Favored
If checked, only documents marked with a star are valid hits.
This feature allows to filter documents in the result set that are not marked as favored.
Pages that are marked with are star a currently defined as "Saved for Later" and where previously deemed as favorite (or favourite).
Since 7.0
This feature is available since version 7.0 of the projectdoc Toolbox.
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 Result Text
Text to be rendered if no result is available. This overrides the default text.
Macro Body
The body contains the set of formula specifications to calculate values. Each specification of a formula contain a
- Name (required),
- the Format,
- the Document Formula,
- the Result 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 | Document Formula | Result Formula | Input Mapping | Output Mapping | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Input Format | Output Format | ||||||||||||||
| ... | ... | ... | ... | ... | ... |
Name
Identifies the formula and the calculated result.
Format
The format used to run the calculations of the formulas. The format is also used to translate the calculated value of the Document Formula and the input value to the Result Formula. Per default the format is also used to parse the input value for the Input Mapping and the output value of the Output Mapping, but only if the column is specified left of the mapping columns.
This format is also called the Formula Format.
The format to parse the input value or to format the output value can be specified with the mapping columns (see below).
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.
Format applied to Formulas
Note that the format will be applied to the Document Formula (right column) and the Result Formula (left column) in case the Format column is specified left of them.
Document 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
${Story Points} * ${Story Weight}
${Team Size}
The Document Formula may also access the result of formulas specified in a line prior to this one. Also the results of formulas in Calculation Macros already processed can be accessed.
Example to reference the Result of a previous Macro
The following example access the result value of a formula named a-formula-name within the Calculation Macro identified by my-macro-id.
${my-macro-id.a-formula-name}
The following example shows the use of a reference with the Result Formula.
The following example shows the use of a reference with the Document Formula (the source table is unchanged from the previous example):
The formula name of the reference may be qualified as shown in Result Formula.
Result Formula
The formula to be applied to the result of the Document Formulas.
The formula may contain placeholders to values of the document that contains the macro.
The formula may use the placeholders ${SUM}, ${PROD}, ${COUNT}, ${MIN}, and ${MAX}. Formulas may use values calculated by formulas defined in a line before it.
| Placeholder | Description |
|---|---|
${SUM} | The sum of values calculated by the Document Formula. |
${PROD} | The product of values calculated by the Document Formula. |
${MIN} | The minimum value calculated by the Document Formula. |
${MAX} | The maximum value calculated by the Document Formula. |
${COUNT} | The count of documents that provided a value for the Document Formula. For instance if a document does not apply properties for all values of the Document Formula, it does neither contribute to the sum nor to the count. |
${AVG} | The average value calculated by the Document Formula. |
${name-of-formula_LIT} | The literal value calculated by the formula named name-of-formula. Useful if the calculated value is rendered in an HTML element without value or if the output mapping maps the result values to an array of values. The value is rendered in the Output Format. |
${name-of-formula} | The result value of the formula. The value is rendered in the Formula Format. |
${name-of-formula_SUM} | The sum value calculated by the formula named name-of-formula. The value is rendered in the Formula Format. |
${name-of-formula_PROD} | The product value calculated by the formula named name-of-formula. The value is rendered in the Formula Format. |
${name-of-formula_MIN} | The minimum value calculated by the Document Formula of name-of-formula. The value is rendered in the Formula Format. |
${name-of-formula_MAX} | The maximum value calculated by the Document Formula of name-of-formula. The value is rendered in the Formula Format. |
${name-of-formula_COUNT} | The count value calculated by the formula named name-of-formula. The value is rendered in the Formula Format. |
Examples for Aggregate Formula
${SUM}
${AVG}
${PROD}/${COUNT} + 10
Input Mapping
A two-column table to map an input to be applied to a value read from the documents.
This allows to translate text to double values and map values provided in the Input Format to be grouped by ranges.
The mapping is applied to the documents of the result set and the document that contains the macro.
The first column contains the value in its normalized rendered form or a range specification. The second value is the numeric value to replace that input form.
The identifier (first column) must be unique within the Input Mapping of a Formula Specification.
Example configuration for the Input Mapping
| Input Mapping | ||||||
|---|---|---|---|---|---|---|
|
Above the table the Input Format may be specified. If it is not specified, the Input Format defaults to the Formula Format provided with the parameter Format.
Example configuration for the Input Mapping with a specific Format
| Input Mapping | ||||||
|---|---|---|---|---|---|---|
percent
|
Example configuration for the Input Mapping to group Values
| Input Mapping | ||||||
|---|---|---|---|---|---|---|
|
Input Format may clash with Formula Format
If the values are numeric, it is recommended to use an Input Format that is compatible with the Formula Format.
Output Mapping
A two-column table to map a double result value to a textual value, such as the Status Macro. The mapping is applied to the final result of the Result Formula.
The identifier (first column) must be unique within the Output Mapping of a Formula Specification.
The first column allows single numeric values and ranges of the format:
D MIN ; MAX D
| Token | Description |
|---|---|
D | The delimiter
|
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 | The maximum value of the range. If omitted, the maximum value of the range is the absolute maximum value. Any value greater than |
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
[10;30]
[;30]
30 [30;30]
]10;]
Example configuration for the Output Mapping
| Output Mapping | ||||||
|---|---|---|---|---|---|---|
|
Above the table the Output Format may be specified. If it is not specified, the Output Format defaults to the Formula Format provided with the parameter Format.
Example configuration for the Output Mapping with specific Format
This example adds the placeholder for the Literal Value of the result. The value is referenced with the placeholder ${LIT}. The label has the suffix (${LIT}) in the following example.
| Output Mapping | ||||||
|---|---|---|---|---|---|---|
percent
|
Uppercase Issue
The Status Macro is unfortunately altering the input label to upper case. Therefore only properties with uppercase names can be referenced with this macro.
Details
Double Precision
All calculations are run with double precision, therefore the macro is not suitable for formulas require with discrete values, not allowing limited precision.
For instance use cases requiring calculations based on currencies (integer values) are not supported.
Access Results from other Calculation Macros
To access the result from another Calculation Macro simply prefix the placeholder reference with the identifier of the macro like this:
${macro-id.formula-name}
All values of a formula can be accessed as defined in Result Formula.
Result Calculation only
The macro allows to skip the Document Formula and only provide a Result Formula. This way a value can be calculated by the use of results previously calculated - either by a formula above in the table or by other instances of the Calculation Macro specified previously on the same page.
If no Document Formula is specified, the default results, like SUM or AVG are not present, because the formula does not iterate over the documents in the result set. Only the representation, the literal, and the result are provided.
No Document Formula
This example shows how the formula named main uses the value of the revelvance-factor formula of the macro result-repo.
| Name | Format | Result Formula |
|---|---|---|
| main | percent | ${result-repo.relevance-factor}*2 + 10% |
Data Center Compatibility
To support Atlassian Data Center the macro will limit the number of documents to use in a calculation. If the result set is larger than the maximum number of documents, an error message is rendered.
For more information, please refer to Limitations on Query Results.
Since 6.0
The limitation is checked since version 6.0.
Related Macros
- 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.
References
Evidence for the information is available by the following references.
- Limitations on Query Results
- The number of hits for document queries is limited due to performance reasons.
