Blog

Formatting numbers, currency and dates automatically in your report columns

Raw JQL or CQL results rarely look presentable straight away – a timestamp comes back as a long ISO string, a monetary field is a plain number with no currency symbol, and a ratio needs multiplying by 100 to read as a percentage. Rather than wrapping every Reporting Table Item’s body formula in a TEXT() call by hand, the column itself can be told how to format its own value.

Number and currency

Set Value format to Number and Decimal places to control how many fraction digits are shown – the column then uses the Table View’s own Decimal symbol/Digit grouping symbol, so every numeric column on the report is consistent even if it was built from several different data providers. Currency does the same thing plus prepends a Currency symbol such as $ or EUR .

Percent

Percent multiplies the underlying value by 100 and appends %, so a body formula returning a plain ratio like 0.42 displays as 42.00% without the formula itself needing a * 100.

Date

Date reformats the value using a Format pattern such as dd MMM yyyy, and accepts either an Excel-style date serial (what a date-producing formula naturally returns) or a handful of common date strings – useful directly on a field like CurrentItem.fields.duedate without a DATEVALUE/TEXT wrapper in the body formula itself.

Applying it to totals too

Whatever Value format is set on a column also applies to that column’s footer, so a Footer aggregate: Sum on a Currency-formatted column shows its total with the same currency symbol and decimal places as every value above it, with no separate formatting step for the footer.

When a value doesn’t parse

If a cell’s value cannot be read as a number (or, for Date, as a date), formatting is skipped and the original text passes through unchanged – a stray non-numeric value in an otherwise numeric column never breaks the rest of the report.

Turning a JSON export into a Confluence table with Reporting Json Data Provider

Not every report starts life in Jira or Confluence – a monitoring tool, an internal API, or a nightly export job often produces plain JSON, and getting that onto a status page usually means someone manually retyping numbers into a table. Reporting Json Data Provider reads that JSON directly, from either the macro body or a page attachment, and feeds it into the same Table View or List View macros used for Jira and Confluence data.

Small data: paste it into the macro body

With Data content type: Text only, paste the JSON array straight into the Json data parameter:

[{"service":"API","uptime":99.95},{"service":"Web","uptime":99.8},{"service":"Batch","uptime":97.2}]

and reference fields the same way as any other data provider: a Table Item with CurrentItem.service and another with CurrentItem.uptime builds a two-column table from it, with a third column formatted via Format type: Percent if the raw numbers are fractions rather than already-multiplied percentages.

Larger data: an attachment file

For anything bigger than a handful of rows, attach the JSON (or plain text) file to the page instead, and set Json data attachment page name and Json data attachment file name to point at it. This is the natural fit for a nightly export job that already writes a JSON file – attach the latest file to the page (by whatever automation already produces it) and the report rebuilds itself from the new attachment on the next view, with no manual retyping at all.

When the array is nested inside an object

A REST API response is often not a bare array but an object with the array nested inside, commonly under a key like results or values. Set Json data root to that key’s path so the data provider starts iterating from the right place instead of failing to find an array at the top level.

Filtering, sorting and formatting still apply

A JSON-backed report is a normal Reporting Table View underneath, so Filter, Sort/Distinct by/Limit, per-column formatting and conditional row highlighting all work exactly the same way as they do for Jira or Confluence data – the data provider is the only thing that changes.

Building a multi-level Jira issue tree with Reporting List View

A flat table is not always the right shape for Jira data – Epics with their Stories underneath, or Stories with their Sub-tasks, read far better as a tree than as one long list with a repeated Epic name in every row. Reporting List View builds exactly that, with one data provider and one Reporting List Item macro per level.

Two levels: Epics and their Stories

Inside a Reporting List View macro:

First Reporting Jira Data Provider: Query: project = APP and issuetype = Epic
First Reporting List Item: Body content: CurrentItem.fields.summary
Second Reporting Jira Data Provider: Query: "Epic Link" = CurrentParentItem.key – note the formula referencing the parent level’s current issue
Second Reporting List Item: Body content: CurrentItem.fields.summary

Because the second level’s query is itself a formula, it is recalculated for every Epic in turn, so each Epic in the resulting list gets its own correctly-scoped set of Stories underneath it – there is no need to know the Epic keys in advance.

Skipping empty branches

Set Skip items with no children on the Stories-level List Item to true so an Epic with no matching Stories is left out of the tree entirely, rather than showing up as an empty bullet with nothing underneath it.

Three levels: adding Sub-tasks

The same pattern extends one level further: a third data provider querying parent = CurrentParentItem.key (referencing the Story level this time) with a third List Item, giving a full Epic → Story → Sub-task tree. CurrentGrandParentItem is also available at the third level if a Sub-task’s row needs to reference the top-level Epic directly rather than its immediate Story parent.

This is a good deal more structure than a single flat report, and it is exactly the shape most stakeholders actually picture when they ask for “the Epics with their Stories”, rather than one long table they have to mentally regroup themselves.

Formatting several tables on one page with a single Conditional Rules Manager

A page with more than one table – one per team, one per quarter, one per region – does not need a separate Conditional Rules Manager for each. A single Conditional Rules Manager wrapping all of the tables, with the Applies to parameter on each rule pointing at specific tables, applies the same or different rules across all of them from one place.

Targeting one specific table

Applies to accepts a table-qualified range, for example Table2.C:C, so a rule can target only the second table on the page even when several tables share the Rules Manager body.

Targeting several ranges at once

Separate multiple ranges with ; to apply one rule everywhere it is needed without repeating the whole rule: Table1.C:C; Table2.C:C; Table3.C:C applies the same rule to the status column of three different tables in one go. This also works for a single table with non-adjacent columns, for example Table1.A:B; Table1.D:D to skip column C.

Different rules for different tables

Nothing stops each Conditional Rule macro inside the same manager from targeting a different table with a different condition – one rule with Applies to: Table1.C:C and a status-based condition, another with Applies to: Table2.B:B and a numeric threshold condition, both inside the same Conditional Rules Manager wrapping both tables. This keeps a page’s worth of conditional formatting in one predictable place instead of scattered across several separate macro instances, which also makes it much easier to see and adjust the whole set of rules later.

See the Conditional Rule reference for the exact Applies to syntax, including row ranges and combining a table qualifier with a row range.

Comparing two columns instead of a fixed value with formula-based rules

Most Conditional Rule conditions compare a cell against a fixed value you type into Value 1. Sometimes the comparison that actually matters is between two cells in the same row – is the actual spend over the budget for that row, is the delivered quantity less than what was ordered. The Formula result value is true condition covers this: Value 1 becomes a full CelesteCS Math formula instead of a plain value, evaluated once per row, and the row is formatted whenever it evaluates to true.

Example: actual over budget

With Applies to pointing at the row (for example A:D, so the whole row is checked and formatted) and a budget column B and an actual-spend column C:

Value 1: C1 > B1

References inside the formula are relative to the row being checked, the same way a spreadsheet formula is – there is no need to write a separate rule per row.

Example: delivered less than ordered

Value 1: D1 < C1

Combining several columns

Because the formula is a normal CelesteCS Math expression, it can combine as many cells and functions as the check needs: Value 1: AND(C1 > B1, D1 = "Approved") only highlights rows that are both over budget and already approved, using the same AND/OR/NOT logical functions available anywhere else in the plug-in.

Referencing a fixed cell instead of the row

Prefix a reference with $ to keep it fixed while the rest of the formula walks down the rows, exactly like an absolute reference in a spreadsheet – C1 > $B$1 compares every row’s column C against the single value in B1, useful for comparing a whole column against one shared threshold cell that a reader can edit without touching the rule itself.