How to calculate numbers stored in List Data macro

Owned by
Nirosan
Last updated: Sept 27, 2023 by
Elena Amandola (Deactivated)
4 min read

Scenario

Currently, the Evaluate Data macro can't be stored within List Data macro. However, to achieve this use case, there is a workaround by using the Reporting for Confluence app. Specifically, Report Eval macro which allows the computation of the List data macro and much more.

Result

  • N/A

Recipe

Apps

Scaffolding, Reporting for Confluence Server & Data Center

Apps

Scaffolding, Reporting for Confluence Server & Data Center

Level

Advanced

Estimated time

15 minutes

Macros

Report Eval

Suppliers

 

Storage format

You can copy and paste this code into the Confluence Source Editor:

<ac:structured-macro ac:name="table-data" ac:schema-version="1" ac:macro-id="33abc796-1284-4678-b6ce-c0460bda9c37"><ac:parameter ac:name="name">IVEWeightingTable</ac:parameter><ac:parameter ac:name="width">100%</ac:parameter><ac:parameter ac:name="initialRows">1</ac:parameter><ac:rich-text-body> <table class="relative-table wrapped" style="width: 100.0%;"><colgroup> <col style="width: 20.6715%;" /> <col style="width: 20.869%;" /> <col style="width: 20.869%;" /> <col style="width: 20.869%;" /> <col style="width: 16.7215%;" /> </colgroup> <tbody> <tr> <th>Initiative</th> <th colspan="1" style="text-align: center;"><u>I</u>mpact<br />(1-10)</th> <th colspan="1" style="text-align: center;">E<u>V</u>idence<br />(see chart in<br />form instructions)</th> <th colspan="1" style="text-align: center;"><u>E</u>ffort to Implement<br />(1-10)</th> <th colspan="1" style="text-align: center;">IVE Score<br />(=I*V*E)</th></tr> <tr> <td> <div class="content-wrapper"><ac:structured-macro ac:name="text-data" ac:schema-version="1" ac:macro-id="a22f6c0d-2bfb-4be7-b601-ff10ada8e681"><ac:parameter ac:name="name">IVEInitiative</ac:parameter><ac:parameter ac:name="maxLength">300</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:rich-text-body> <p><br /></p></ac:rich-text-body></ac:structured-macro></div></td> <td colspan="1" style="text-align: center;"> <div class="content-wrapper"><ac:structured-macro ac:name="list-data" ac:schema-version="1" ac:macro-id="107a3038-8873-4aff-b52f-4b6156e69cd5"><ac:parameter ac:name="blank">false</ac:parameter><ac:parameter ac:name="name">Impact</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:parameter ac:name="blankText">(select)</ac:parameter><ac:rich-text-body><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="70b15429-e163-4794-b1cf-fcc47b95d8e4"><ac:parameter ac:name="sortValue">01</ac:parameter><ac:parameter ac:name="label">10%</ac:parameter><ac:parameter ac:name="value">10</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">10%</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="f1d51221-232a-41cc-9766-7446257f10f0"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setImpact</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[10]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="a06230db-3135-49d7-8c8d-de4d46e411f0"><ac:parameter ac:name="sortValue">02</ac:parameter><ac:parameter ac:name="label">40%</ac:parameter><ac:parameter ac:name="value">40</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">40%</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="f642a314-fe73-4911-b9c8-f7322dd130bd"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setImpact</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[40]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="0e49a40b-f544-480e-8196-bfb2aa2550c1"><ac:parameter ac:name="sortValue">03</ac:parameter><ac:parameter ac:name="label">70%</ac:parameter><ac:parameter ac:name="value">70</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">70%</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="de28d89c-a6e7-4c64-a71b-b2ddc0599d34"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setImpact</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[70]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="6ab19851-b95f-4c56-82ca-2853ba1643d8"><ac:parameter ac:name="sortValue">04</ac:parameter><ac:parameter ac:name="label">100%+</ac:parameter><ac:parameter ac:name="value">100</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">100%+</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="61a66c96-0d99-4c7f-ad03-70a8afd686ef"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setImpact</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[100]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro></div></td> <td colspan="1" style="text-align: center;"> <div class="content-wrapper"><ac:structured-macro ac:name="list-data" ac:schema-version="1" ac:macro-id="a097c42a-9e22-4f27-9d16-21aed88500cb"><ac:parameter ac:name="blank">false</ac:parameter><ac:parameter ac:name="name">Evidence</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:parameter ac:name="blankText">(select)</ac:parameter><ac:rich-text-body><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="cb37981b-e013-473a-9c89-0f064b03b308"><ac:parameter ac:name="sortValue">01</ac:parameter><ac:parameter ac:name="label">.05 - Self Conviction, Pitch Deck, or Strategic Alignment</ac:parameter><ac:parameter ac:name="value">.01</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">.05 - Self Conviction, Pitch Deck, or Strategic Alignment</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="d97f8bf3-457a-4069-9ce4-ccb679b88ee5"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEvidence</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[.05]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="2a365610-01fc-4de9-b5e4-0d85495d2a70"><ac:parameter ac:name="sortValue">02</ac:parameter><ac:parameter ac:name="label">.3 - Opinion or Estimate &amp; Plans</ac:parameter><ac:parameter ac:name="value">.3</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">.3 - Opinion or Estimate &amp; Plans</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="9f44a31d-bb7f-4395-9b63-153e0364d373"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEvidence</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[.3]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="1c812d69-92d0-4d33-bb3d-d41f285ee08e"><ac:parameter ac:name="sortValue">03</ac:parameter><ac:parameter ac:name="label">.6 - Anecdotal Evidence</ac:parameter><ac:parameter ac:name="value">.6</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">.6 - Anecdotal Evidence</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="d6b74a7a-2853-4258-84eb-deeae1edb3bb"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEvidence</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[.6]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="00fef021-13c9-4c1a-a2de-624f7bcb7051"><ac:parameter ac:name="sortValue">04</ac:parameter><ac:parameter ac:name="label">1 - Market Data</ac:parameter><ac:parameter ac:name="value">1</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">1 - Market Data</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="4a4c00fc-4994-453b-9719-301c0d5556d1"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEvidence</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[1]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="8d02d675-96ae-47d7-8293-6c12db6d0795"><ac:parameter ac:name="sortValue">05</ac:parameter><ac:parameter ac:name="label">2 - User/Customer Evidence</ac:parameter><ac:parameter ac:name="value">2</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">2 - <span style="color: rgb(0,0,0);">User/Customer Evidence</span></p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="9497f6ab-e8a1-4f3d-96e9-302e791c829a"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEvidence</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[2]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="7780287e-1dd9-4de2-929b-1f1094d679dd"><ac:parameter ac:name="sortValue">06</ac:parameter><ac:parameter ac:name="label">3 - User Test Results</ac:parameter><ac:parameter ac:name="value">3</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">3 - User Test Results</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="8e4f7869-fbac-4c9f-9845-c60dae6b6952"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEvidence</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[3]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro></div></td> <td colspan="1" style="text-align: center;"> <div class="content-wrapper"><ac:structured-macro ac:name="list-data" ac:schema-version="1" ac:macro-id="0114fe7b-e54a-43dd-8928-fc808136b753"><ac:parameter ac:name="blank">false</ac:parameter><ac:parameter ac:name="name">Effort</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:parameter ac:name="blankText">(select)</ac:parameter><ac:rich-text-body><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="d065cd54-72a5-42fc-bcb6-fdea0d8aae91"><ac:parameter ac:name="sortValue">01</ac:parameter><ac:parameter ac:name="label">≥45 weeks (&gt;3 quarters)</ac:parameter><ac:parameter ac:name="value">1</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">≥41 weeks (&gt;3 quarters)</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="f9e5bf2c-ed23-4da5-96ad-61c69027aaaa"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEffort</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[1]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="f93c5f23-0754-404a-b711-0b277e707460"><ac:parameter ac:name="sortValue">02</ac:parameter><ac:parameter ac:name="label">33 - 40 weeks (~3 quarters)</ac:parameter><ac:parameter ac:name="value">2</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">33 - 40 weeks (~3 quarters)</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="122238e6-f484-47e7-92a1-89f8066afa93"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEffort</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[2]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="b25261ff-4dda-4fb8-bcc2-7293b90ec8f9"><ac:parameter ac:name="sortValue">03</ac:parameter><ac:parameter ac:name="label">27 - 32 weeks (2 -3 quarters)</ac:parameter><ac:parameter ac:name="value">3</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">27 - 32 weeks (2 - 3 quarters)</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="e0890ca5-2640-454c-997e-bea0ea5b8668"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEffort</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[3]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="4b3376b2-fe66-4b61-9587-ceae084cdf9f"><ac:parameter ac:name="sortValue">04</ac:parameter><ac:parameter ac:name="label">21 - 26 weekes (~2 quarters)</ac:parameter><ac:parameter ac:name="value">4</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">21 - 26 weekes (~2 quarters)</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="41f6c704-4624-4c0d-b084-84fb23c42e9a"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEffort</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[4]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="f317b1b1-2cd8-4ecc-a4e6-9188f3f224e8"><ac:parameter ac:name="sortValue">05</ac:parameter><ac:parameter ac:name="label">15 - 20 weeks (1 - 2 quarters)</ac:parameter><ac:parameter ac:name="value">5</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">15 - 20 weeks (1 - 2 quarters)</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="cbb90b71-55a9-40bd-832c-03345839d63d"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEffort</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[5]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="77eccf4f-b2b1-4eda-a2f7-4fc829708b47"><ac:parameter ac:name="sortValue">06</ac:parameter><ac:parameter ac:name="label">9 - 14 weeks (~1 quarter)</ac:parameter><ac:parameter ac:name="value">6</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">9 - 14 weeks (~1 quarter)</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="6e77f8fc-6c69-43f1-ac90-c6c1280516af"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEffort</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[6]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="list-option" ac:schema-version="1" ac:macro-id="9cdd7a2f-1b21-4494-a549-c7f1b95f8b77"><ac:parameter ac:name="sortValue">07</ac:parameter><ac:parameter ac:name="label">≤8 weeks (&lt;2 months)</ac:parameter><ac:parameter ac:name="value">7</ac:parameter><ac:rich-text-body> <p class="auto-cursor-target">≤8 weeks (&lt;2 months)</p><ac:structured-macro ac:name="set-data" ac:schema-version="1" ac:macro-id="4a79f6cc-6678-4a63-ac5e-9f85eb9e7c7d"><ac:parameter ac:name="hidden">true</ac:parameter><ac:parameter ac:name="name">setEffort</ac:parameter><ac:parameter ac:name="type">number</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:plain-text-body><![CDATA[7]]></ac:plain-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro></ac:rich-text-body></ac:structured-macro></div></td> <td colspan="1" style="text-align: center;"> <div class="content-wrapper"><ac:structured-macro ac:name="report-eval" ac:schema-version="1" ac:macro-id="e65019db-3096-44e0-953e-fde51b1644ff"><ac:parameter ac:name="default">0</ac:parameter><ac:parameter ac:name="format">#,##0.0</ac:parameter><ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter><ac:parameter ac:name="">IVEScore</ac:parameter><ac:plain-text-body><![CDATA[%data:setImpact%*%data:setEvidence%*%data:setEffort%]]></ac:plain-text-body></ac:structured-macro></div></td></tr></tbody></table></ac:rich-text-body></ac:structured-macro>

Macro structure

You can recreate the example in the editor view:

  • N/A

Steps

  1. Create a Table Data macro and set the Name of the table

  2. Inside the Table Data macro create a table with 5 columns and 2 rows, like the one in the image:

  3. Inside the first column insert a List Data macro and set the Name to "Impact" and blank to "false"

  4. Inside the List Data macro, add a List Option macro and set Value to 10 and Label to 10%

  5. Inside the List Option macro create a Set Data macro and set Name to "setimpact" and Hidden to "true" and Type to number. 

  6. Inside the Set Data macro type 10

  7. Inside the List Data macro, add another List Option macro and set Value to 40 and Label to 40%

  8. Inside the List Option macro create a Set Data macro and set Name to "setimpact" and Hidden to "true" and Type to number. 

  9. Inside the Set Data macro type 40

  10. Inside Effort to Implement column insert a List Data macro and set Name to "Effort" and blank to "false"

  11. Inside the List Data macro, add a List Option macro and set Value to 1 and Label to ≥45 weeks (>3 quarters)

  12. Inside the List Option macro create a Set Data macro and set Name to "seteffort" and Hidden to "true" and Type to number  

  13. Inside the Set Data macro type 1

  14. Inside the List Data macro, add a List Option macro and set value to 2 and Label to 33 - 40 weeks (~3 quarters)

  15. Inside the List Option macro create a Set Data macro and set Name to "seteffort" and Hidden to "true" and Type to number  

  16. Inside the Set Data macro type 2

  17. In the IE Score column, create a Report Eval macro and set the following:
    Name: "IVEScore"
    Default Value 0
    Format #,##0.0

  18. Inside the Report Eval macro type the following formula: 

    %data:setImpact%*%data:setEffort%

    Â