Overview

The project status and budget page displays all the subprojects listed in a space and provides milestones and budget details at a glance.

Furthermore, charts can be generated using the data from the Reporting table to keep track of current cash flow.

Suppliers used

When to use the Collection Supplier

Use the object supplier if you are unsure of the object type. Pair any key with an object:class keychain and it will return the type of object.

For example: data:Project Members > object:class returns

class java.util.ArrayList

This means the Project Members Scaffolding data is stored as an Array List.

This keychain is useful for debugging purposes.

Content and Structure

First, set the main structure of our report, which is a table.

create a report-table macro

Next, define what to report by using a content-reporter block

nest the content-reporter block inside the report-table block
edit the content-reporter block by setting
- Types to +pages, -attachment
- Labels to subproject
- Scope to Cafe Rockettoria Projects > children

This means Reporting only fetches pages labeled as subproject and are a child of Cafe Rockettoria Projects.

-attachment means attachments are excluded.

In the content-reporter block

insert a text-sort block
edit the text-sort block
- type content:title for Key
- choose 'ascending' for Order

Now we are ready to setup the report columns.

Report columns

1 Project Name
2 Project Status
3 Days left: if-else statements and parameter injection
4 Number of Milestones
5 Budget Total
6 Expenditure Total
7 Budget Deficit
8 Budget Deficit %

Project Name

Within the report-table block

insert a report-column macro underneath the content-reporter block
- name this column Project Name
insert a report-info block inside this report-column
edit the report-info block
- name the Key content:title
- check Link To Item

Project Status

insert a report-column macro
- name this column Project Status
insert a report-info block inside this report-column
Eedit the report-info block
- name the Key data:Project Status

Days left: if-else statements and parameter injection

Create a simple if-else statement that will display the days left for ongoing projects or display a completed message if the project is over.

With report-eval, we can do calculations on predefined variables, suppliers or Scaffolding data blocks.

In the report-eval block, we will perform the calculation of Days Left using parameter injection

set the parameters for report-eval as
- Name - Days Left
- Default Value - 0
- Format - #,00
- check Hidden

For the report-eval calculation formula

type the following

(% data:Project End Date > date:milliseconds % - % global:current date > date:milliseconds %)/1000/60/60/24

What does this mean?

In the first part

%data:Project End Date calls a Scaffolding Data block that contains the end date of the project
a keychain is then used to convert this date object to milliseconds

The second part

%global:current date calls the current date via the Global supplier
a keychain is then also used to convert the date object into milliseconds

Together, the above formula

converts both end date and current date into milliseconds
gets the difference (in milliseconds)
converts back to days with division

The report-eval variable can then be accessed via the Variable supplier. The key is the name of the report-eval block - variable:Days Left.

Now create a report-block macro for the if-else statement.

In the report-block macro

insert a local-reporter block
set the keychain to @self

create a number-filter macro
set the keychain to variable:Days Left
set Above Value to 0

insert a report-body block
set injected to true

Inside the report-body block

type:

% variable:Days Left > number:# % day(s)

Now create the 'else' statement by

inserting another number-filter macro
set the keychain to variable:Days Left
set Below Value to 0

insert a report-body block
type: This project has ended

This creates a report-block shown below.

Haven't I done this in the subproject template?

report-eval creates a temporary variable in the given page

the variable is only accessible from that page and cannot be fetched and displayed in other pages

Using the flexibility of report-eval's calculation abilities, we created a temporary variable in the subproject page, and now we need to create another temporary variabe in the current report to get Days Left, using the same formula and report structure.

Number of Milestones

insert a report-column under the previous Days Left column
- name this column Number of milestones
insert a report-info block inside this report-column
edit the report-info block
- name the Key data:Project Milestones > collection:size

Budget Total

This column will return the sum of all milestone budgets from a particular subproject. The sum of all subproject's budget can also be calculated using report-column.

insert a report-column
- name this column Budget total
edit the report-column settings
- Summary Type to sum
- Summary Value to data:Budget Total
- Summary Format to $#,##0.00

insert a report-info block inside the report-column
- name the Key data:Budget Total
- set the Format to $##,##0.00

Expenditure Total

This column returns the sum of all milestone expenditures from a particular subproject. The sum of all subprojects expenditures can also be calculated using report-column.

insert a report-column
- name this column Expenditure total
edit the report-column settings
- Summary Type to sum
- Summary Value to data:Expenditure Total
- Summary Format to $#,##0.00
insert a report-info block inside the report-column
- name the Key data:Expenditure Total
- set the Format to $##,##0.00

Budget Deficit

This column returns the sum of all budget deficits from a particular subproject. It is calculated using the difference of actual expenditure and allocated budget for each subproject.

insert a report-column
- name this column Budget deficit
edit the report-column settings
- Summary Type to sum
- Summary Value to variable:Budget Deficit
- Summary Format to $#,##0.00

insert a report-eval inside the report-column
name it Budget Deficit

In the report-eval

type %data:Budget Total% - %data:Expenditure Total%

Budget Deficit %

This column returns the budget deficit in percentage. It is calculated using the Budget Deficit variable created in the previous section and dividing that amount by Budget Total.

insert a report-column
- name this column Budget deficit percentage
insert a report-eval block inside report-column
name the report-eval block Percentage Deficit
edit the report-eval block
- set Default Value to 0
- set Format to #%.

In the report-eval

type %variable:Budget Deficit% / %data:Budget Total%

Project Status & Budget table

<ac:structured-macro ac:macro-id="38aec1b2-1f0e-4759-8be8-e20dbcf76023" ac:name="report-table" ac:schema-version="1">
  <ac:rich-text-body>
    <ac:structured-macro ac:macro-id="38314951-78e6-4773-b549-e46d953e2e5e" ac:name="content-reporter" ac:schema-version="1">
      <ac:parameter ac:name="types">+pages, -attachment</ac:parameter>
      <ac:parameter ac:name="scope">Cafe Rockettoria Projects &gt; children</ac:parameter>
      <ac:parameter ac:name="labels">subproject</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="d1e9c74e-605d-4a53-93f3-38d195a200b2" ac:name="text-sort" ac:schema-version="1">
            <ac:parameter ac:name="">content:title</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="aea2847c-88ea-4257-a597-ad46237c0264" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Project Name</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="59a534ec-dac2-4750-a69c-747c111a21ae" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="link">true</ac:parameter>
            <ac:parameter ac:name="">content:title</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="92c73454-6ef4-432a-b915-c55216fd744b" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Project Status</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="304641a2-8b08-4275-80a7-af69a6797359" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">data:Project Status</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="acfe8cfe-a648-410c-bba0-66d9ce8fde86" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Days Left</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="5d66226b-1f24-4836-9858-fe0eb8fb71dc" ac:name="report-eval" ac:schema-version="1">
            <ac:parameter ac:name="default">0</ac:parameter>
            <ac:parameter ac:name="hidden">true</ac:parameter>
            <ac:parameter ac:name="format">#</ac:parameter>
            <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
            <ac:parameter ac:name="">Days Left</ac:parameter>
            <ac:plain-text-body><![CDATA[(%data:Project End Date > date:milliseconds% - %global:current date > date:milliseconds%)/1000/60/60/24]]></ac:plain-text-body>
          </ac:structured-macro>
          <ac:structured-macro ac:macro-id="cb311386-9493-44a6-af29-ab7c190ed0c1" ac:name="report-block" ac:schema-version="1">
            <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
            <ac:rich-text-body>
              <ac:structured-macro ac:macro-id="4e7c657c-0c99-4c95-b343-8f3f58fe4c05" ac:name="local-reporter" ac:schema-version="1">
                <ac:parameter ac:name="">@self</ac:parameter>
                <ac:rich-text-body>
                  <p> </p>
                </ac:rich-text-body>
              </ac:structured-macro>
              <p>
                <ac:structured-macro ac:macro-id="8381c68f-f058-47c6-839f-2a0f47f460cb" ac:name="number-filter" ac:schema-version="1">
                  <ac:parameter ac:name="aboveValue">0</ac:parameter>
                  <ac:parameter ac:name="">variable:Days Left</ac:parameter>
                </ac:structured-macro>
              </p>
              <ac:structured-macro ac:macro-id="af877eef-0630-442c-adf7-dc516a17a520" ac:name="report-body" ac:schema-version="1">
                <ac:parameter ac:name="injected">true</ac:parameter>
                <ac:rich-text-body>
                  <p>%variable:Days Left &gt; number:#% day(s)</p>
                </ac:rich-text-body>
              </ac:structured-macro>
              <p>
                <ac:structured-macro ac:macro-id="20f5798d-143e-41d8-9569-dd0564fd327c" ac:name="number-filter" ac:schema-version="1">
                  <ac:parameter ac:name="belowValue">0</ac:parameter>
                  <ac:parameter ac:name="">variable:Days Left</ac:parameter>
                </ac:structured-macro>
              </p>
              <ac:structured-macro ac:macro-id="90136fad-3ffa-45ae-bfa9-17fd2df2f8ea" ac:name="report-body" ac:schema-version="1">
                <ac:rich-text-body>
                  <p>This project has ended</p>
                </ac:rich-text-body>
              </ac:structured-macro>
            </ac:rich-text-body>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="eebaac3b-8536-4d30-a992-dbf180241743" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">No. of milestones</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="92368731-3d0c-4ddb-9d73-16d10779a968" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">data:Project Milestones &gt; collection:size</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="ec1ac3e7-6f19-4954-8470-59f9b4b7c090" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="summaryFormat">$#,##0.00</ac:parameter>
      <ac:parameter ac:name="summaryType">sum</ac:parameter>
      <ac:parameter ac:name="summaryValue">data:Budget Total</ac:parameter>
      <ac:parameter ac:name="title">Budget Total</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="75d93000-4110-47c5-8f92-f80f5f388038" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="format">$##,##0.00</ac:parameter>
            <ac:parameter ac:name="">data:Budget Total</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="480602e1-27e7-4f72-b029-5e1c8563ff95" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="summaryFormat">$#,##0.00</ac:parameter>
      <ac:parameter ac:name="summaryType">sum</ac:parameter>
      <ac:parameter ac:name="summaryValue">data:Expenditure Total</ac:parameter>
      <ac:parameter ac:name="title">Expenditure total</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="ac5e3f34-6661-46b0-917e-5f989fc91b52" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="format">$##,##0.00</ac:parameter>
            <ac:parameter ac:name="">data:Expenditure Total</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="bea3b9c2-a11d-4d7a-9dd0-0e6f0d2bd294" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="summaryFormat">$##,##0.00</ac:parameter>
      <ac:parameter ac:name="summaryType">sum</ac:parameter>
      <ac:parameter ac:name="summaryValue">variable:Budget Deficit</ac:parameter>
      <ac:parameter ac:name="title">Budget Deficit</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="a5fef81f-7ccd-474f-abe5-d6e228a0a49d" ac:name="report-eval" ac:schema-version="1">
            <ac:parameter ac:name="format">$##,##0.00</ac:parameter>
            <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
            <ac:parameter ac:name="">Budget Deficit</ac:parameter>
            <ac:plain-text-body><![CDATA[%data:Budget Total% - %data:Expenditure Total%]]></ac:plain-text-body>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="36174fbf-2441-4bf8-b1eb-70f9a3ae138f" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Budget Deficit %</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="18b2002e-3d2f-47ee-b629-2f642c9af30e" ac:name="report-eval" ac:schema-version="1">
            <ac:parameter ac:name="default">0</ac:parameter>
            <ac:parameter ac:name="format">#%</ac:parameter>
            <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
            <ac:parameter ac:name="">Percentage Deficit</ac:parameter>
            <ac:plain-text-body><![CDATA[%variable:Budget Deficit% / %data:Budget Total%]]></ac:plain-text-body>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
  </ac:rich-text-body>
</ac:structured-macro>

Chart: Subproject Budget Deficit

Using the same Reporting elements, we can construct charts with simplified Reporting tables such as this one.

This table is built using the columns Project Name and Budget Deficit %.

Insert a Chart macro and make the following settings:

Type: Bar
Display orientation: Vertical
Chart title: Subproject Budget Deficit
(Check) Show Legend
Columns: Project Name, Budget Deficit %
Content Orientation: Horizontal
Range Minimum Value: -100
Range Maximum Vale: 100

For the macro structure, see Chart Macro Structure.

Subproject Budget Deficit chart

<div class="tablesorter-header-inner">
  <ac:structured-macro ac:macro-id="62e45cfa-e49d-47a1-b620-1e3ccf6e60d5" ac:name="chart" ac:schema-version="1">
    <ac:parameter ac:name="orientation">vertical</ac:parameter>
    <ac:parameter ac:name="columns">Project Name, Budget Deficit %</ac:parameter>
    <ac:parameter ac:name="width">500</ac:parameter>
    <ac:parameter ac:name="rangeAxisLowerBound">-100</ac:parameter>
    <ac:parameter ac:name="dataOrientation">horizontal</ac:parameter>
    <ac:parameter ac:name="title">Subproject Budget Deficit</ac:parameter>
    <ac:parameter ac:name="type">bar</ac:parameter>
    <ac:parameter ac:name="rangeAxisUpperBound">100</ac:parameter>
    <ac:parameter ac:name="height">500</ac:parameter>
    <ac:rich-text-body>
      <ac:structured-macro ac:macro-id="acf935c7-2b99-4f5e-a3f3-f09f2ead1685" ac:name="report-table" ac:schema-version="1">
        <ac:rich-text-body>
          <ac:structured-macro ac:macro-id="09e59f25-8132-4bb6-ad37-dbc82230bc31" ac:name="content-reporter" ac:schema-version="1">
            <ac:parameter ac:name="types">+pages, -attachment</ac:parameter>
            <ac:parameter ac:name="scope">Cafe Rockettoria Projects &gt; children</ac:parameter>
            <ac:parameter ac:name="labels">subproject</ac:parameter>
            <ac:rich-text-body>
              <p>
                <ac:structured-macro ac:macro-id="8259caed-26ab-43ec-8f8f-9525ce1c8bc9" ac:name="text-sort" ac:schema-version="1">
                  <ac:parameter ac:name="">content:title</ac:parameter>
                </ac:structured-macro>
              </p>
            </ac:rich-text-body>
          </ac:structured-macro>
          <ac:structured-macro ac:macro-id="1e79e38f-ad94-4140-868b-9d786e88f314" ac:name="report-column" ac:schema-version="1">
            <ac:parameter ac:name="title">Project Name</ac:parameter>
            <ac:rich-text-body>
              <p>
                <ac:structured-macro ac:macro-id="5491e9fb-fbdb-42aa-b41f-8d3eabd66096" ac:name="report-info" ac:schema-version="1">
                  <ac:parameter ac:name="">content:title</ac:parameter>
                </ac:structured-macro>
              </p>
            </ac:rich-text-body>
          </ac:structured-macro>
          <ac:structured-macro ac:macro-id="16fcd047-c174-494d-934d-81f978b5f6df" ac:name="report-column" ac:schema-version="1">
            <ac:parameter ac:name="injected">true</ac:parameter>
            <ac:parameter ac:name="title">Budget Deficit %</ac:parameter>
            <ac:rich-text-body>
              <p>
                <ac:structured-macro ac:macro-id="53885bcc-a1c8-4b4c-b3b9-a365c4500983" ac:name="report-eval" ac:schema-version="1">
                  <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
                  <ac:parameter ac:name="">Percentage Deficit</ac:parameter>
                  <ac:plain-text-body><![CDATA[(%data:Budget Total% - %data:Expenditure Total%) / %data:Budget Total% * 100]]></ac:plain-text-body>
                </ac:structured-macro>
              </p>
            </ac:rich-text-body>
          </ac:structured-macro>
        </ac:rich-text-body>
      </ac:structured-macro>
    </ac:rich-text-body>
  </ac:structured-macro>
</div>

Chart: Subproject Budget Allocation

This table is built using the columns Project Name and Budget Total.

Insert a Chart macro and make the following settings:

Type: Pie
Display orientation: Vertical
Chart title: Subproject Budget Allocation
(Check) Show Legend
Columns: Project Name, Budget Total
Content Orientation: Vertical

For the macro structure, see Chart Macro Structure.

Subproject Budget Allocation chart

<div class="tablesorter-header-inner">
  <ac:structured-macro ac:macro-id="371ec71d-180e-4ab3-a627-92f69dba6d6a" ac:name="chart" ac:schema-version="1">
    <ac:parameter ac:name="orientation">vertical</ac:parameter>
    <ac:parameter ac:name="columns">Project Name, Budget Total</ac:parameter>
    <ac:parameter ac:name="width">500</ac:parameter>
    <ac:parameter ac:name="dataOrientation">vertical</ac:parameter>
    <ac:parameter ac:name="title">Subproject Budget Allocation</ac:parameter>
    <ac:parameter ac:name="height">500</ac:parameter>
    <ac:rich-text-body>
      <ac:structured-macro ac:macro-id="b307d24c-b13f-49fa-b883-bac6c9e10c45" ac:name="report-table" ac:schema-version="1">
        <ac:rich-text-body>
          <ac:structured-macro ac:macro-id="9fcb1f89-0509-467a-89b9-09aa0c81e5f7" ac:name="content-reporter" ac:schema-version="1">
            <ac:parameter ac:name="types">+pages, -attachment</ac:parameter>
            <ac:parameter ac:name="scope">Cafe Rockettoria Projects &gt; children</ac:parameter>
            <ac:parameter ac:name="labels">subproject</ac:parameter>
            <ac:rich-text-body>
              <p>
                <ac:structured-macro ac:macro-id="41f99c63-eb30-484c-90a3-5c1914880643" ac:name="text-sort" ac:schema-version="1">
                  <ac:parameter ac:name="">content:title</ac:parameter>
                </ac:structured-macro>
              </p>
            </ac:rich-text-body>
          </ac:structured-macro>
          <ac:structured-macro ac:macro-id="e5bde7db-710c-49a8-bf39-31ffe486055a" ac:name="report-column" ac:schema-version="1">
            <ac:parameter ac:name="title">Project Name</ac:parameter>
            <ac:rich-text-body>
              <p>
                <ac:structured-macro ac:macro-id="d70b6f4a-744f-4466-83e9-92b616d3aa76" ac:name="report-info" ac:schema-version="1">
                  <ac:parameter ac:name="">content:title</ac:parameter>
                </ac:structured-macro>
              </p>
            </ac:rich-text-body>
          </ac:structured-macro>
          <ac:structured-macro ac:macro-id="7fdb40ab-db1f-4f0b-9b8f-7aa49619f998" ac:name="report-column" ac:schema-version="1">
            <ac:parameter ac:name="title">Budget Total</ac:parameter>
            <ac:rich-text-body>
              <p>
                <ac:structured-macro ac:macro-id="f570aa19-20fb-48e9-b44e-2005331c2df7" ac:name="report-info" ac:schema-version="1">
                  <ac:parameter ac:name="format">$##,##0.00</ac:parameter>
                  <ac:parameter ac:name="">data:Budget Total</ac:parameter>
                </ac:structured-macro>
              </p>
            </ac:rich-text-body>
          </ac:structured-macro>
        </ac:rich-text-body>
      </ac:structured-macro>
    </ac:rich-text-body>
  </ac:structured-macro>
</div>

Chart: Subproject Budget Allocation

This table is built using the columns Project Name and Expenditure Total.

Insert a Chart macro and make the following settings:

Type: Pie
Display orientation: Vertical
Chart title: Subproject Expenditure
(Check) Show Legend
Columns: Project Name, Expenditure Total
Content Orientation: Vertical

Use Cases

Part 5 - Creating the project status and budget page

Overview

Suppliers used

When to use the Collection Supplier

Content and Structure

Report columns

Project Name

Project Status

Days left: if-else statements and parameter injection

Number of Milestones

Budget Total

Expenditure Total

Budget Deficit

Budget Deficit %

Chart: Subproject Budget Deficit

Chart: Subproject Budget Allocation

Chart: Subproject Budget Allocation

Macro structure

Chart Macro Structure

Full source

Next