/
How to calculate numbers stored in List Data macro

How to calculate numbers stored in List Data macro

Scenario

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

Recipe

Apps

Scaffolding, Reporting for Confluence 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>

Steps

  1. Create a Table Data macro (from the Scaffolding app) with Name: IVEWeightingTable.

  2. Inside the Table Data macro body, create a table with three columns and two rows with titles in the header row - Impact (1-10), Effort to Implement (1-10), and Score (=I*E).

  3. Inside the Impact column, create a List Data macro (from the Scaffolding app) with Name: Impact and Blank: cleared.

  4. Within the Impact List Data macro, create a List Option macro with Value: 10 and Label: 10%.

  5. Within the 10% List Option macro, create a Set Data macro with the following parameters:

    • Name: setimpact

    • Hidden: selected

    • Type: number

    • Macro body content: 10

  6. Within the Impact List Data macro, create another List Option macro with Value: 40 and Label: 40%.

  7. Within the 40% List Option macro, create a Set Data macro with the following parameters:

    • Name: setimpact

    • Hidden: selected

    • Type: number

    • Macro body content: 40

  1. Inside the Effort to Implement column, create a List Data macro with Name: Effort and Blank: cleared.

  2. Within the Effort List Data macro, create a List Option macro with Value: 1 and Label: ≥45 weeks (>3 quarters).

  3. Within the ≥45 weeks List Option macro, create a Set Data macro with the following parameters:

    • Name: seteffort

    • Hidden: selected

    • Type: number.  

    • Macro body content: 1.

  4. Within the Effort List Data macro, create another List Option macro with Value: 2 and Label: 33 - 40 weeks (~3 quarters).

  5. Inside the 33 - 40 weeks List Option macro, create a Set Data macro with the following parameters:

    • Name: seteffort

    • Hidden: selected

    • Type: number.

    • Macro body content: 2.

  6. In the Score column, create a Report Eval macro with the following parameters:

    • Name: IVEScore

    • Default Value: 0

    • Format: #,##0.0

  7. Inside the Report Eval macro, enter the following formula: 

    %data:setImpact%*%data:setEffort%