Temperature monitoring report

Monthly cold chain heat map, excursion log and QA sign-off

Generate temperature monitoring reports from your IoT platform

Pharmaceutical and food distribution warehouses keep their temperature records and review them at regular intervals: the EU Guidelines on Good Distribution Practice ask for monitored storage areas (section 3.2), calibrated monitoring equipment (section 3.3) and investigated temperature excursions. Cold chain monitoring platforms collect a reading every few minutes from each probe, and once a month they send the period to the Carbone API as JSON to produce the report that the quality team reviews and signs.

In this example, Calvera Sense, a fictional monitoring platform, builds the October report of a fictional pharmaceutical distribution centre: eight assets (cold rooms, freezers, ambient zones and a reefer trailer), 71,424 readings and three excursions.

In this example, you will:

  • Build a heat map whose rows (assets) and columns (days) both come from the data
  • Color each cell on a diverging scale relative to the asset's own acceptance range
  • Compute the KPIs, the monthly minimum and maximum, and the time in range with aggregators
  • List the excursions with their impact assessment, CAPA and status

How to build a temperature heat map in Word

1. Lay out the page and the KPI strip

  • Set the page to A4 Landscape so that 31 day columns fit next to the asset names.
  • Write the header with {d.platform.name}, {d.site.company}, {d.report.number}, {d.report.periodLabel} and {d.report.generated}.
  • In a one-row table, compute the KPIs with aggregators: {d.assets[].id:aggCount} assets, {d.assets[].readings:aggSum:formatN(0)} readings and {d.excursions[].ref:aggCount} excursions.
  • Count the open excursions and pick the highest MKT of the +2 to +8 °C assets with array filters: {d.excursions[status=Open].ref:aggCount} and {d.assets[low=2].mkt:aggMax:formatN(1)}.

2. Compute the time in range

Store the two totals with :set, in the cell of the label so that they leave no empty line, then compute the percentage:

{d.assets[].readings:aggSum:set(c.readings)}{d.assets[].readingsOut:aggSum:set(c.readingsOut)}
{c.readings:sub(c.readingsOut):div(c.readings):mul(100):formatN(2)} %

3. Build the heat map with a bi-directional loop

Insert a table with a header row, a data row and an end row, then repeat the assets vertically and the days horizontally with a bi-directional loop:

| Asset              | {d.assets[0].days[i].day}           | {d.assets[0].days[i+1].day}           | Min | Max | MKT | In range |
|--------------------|-------------------------------------|---------------------------------------|-----|-----|-----|----------|
| {d.assets[i].name} | {d.assets[i].days[i].max:formatN(1)} | {d.assets[i].days[i+1].max:formatN(1)} | ... | ... | ... | ...      |
|                    | {d.assets[i].days[i].min:formatN(1)} |                                        |     |     |     |          |
| {d.assets[i+1]}    |                                     |                                       |     |     |     |          |
  • The header reads the days from the first asset with [0], and a second line prints the weekday with {d.assets[0].days[i].dow}.
  • Each day cell holds two paragraphs: the daily maximum on top, the daily minimum below.
  • The end row {d.assets[i+1]} prints nothing and disappears from the generated document.
  • Give the table the full page width: Carbone shares it between the generated columns in proportion to the widths of the template cells.

4. Color each cell on a scale relative to its asset

First, in a cell that already holds text, add three tags that print nothing and enrich the JSON with :set:

{d.assets[].high:sub(.low):set(.span)}
{d.assets[].high:add(.low):set(.lowHigh)}
{d.assets[].days[].max:add(.min):ifGTE(..lowHigh):show(.max):elseShow(.min):sub(..low):div(..span):set(.pos)}
  • span is the width of the acceptance range of each asset, lowHigh the sum of its two limits.
  • For each day, :show(.max):elseShow(.min) keeps the value farthest from mid range, and pos stores its position in the range: 0 at the low limit, 0.5 at mid range, 1 at the high limit. The limits come from the parent asset with a relative path (..low).

Then, after the maximum in the day cell, a switch-case condition picks one of 8 colors and :color applies it to the cell background:

{d.assets[i].days[i].pos
  :ifLT(0):show(#1F4E8C)
  :ifLT(0.2):show(#7DA7D9):ifLT(0.4):show(#C6DAF0):ifLT(0.6):show(#F3F1EA)
  :ifLT(0.75):show(#FBE7A1):ifLT(0.9):show(#F6B85C):ifLTE(1):show(#EC8441)
  :elseShow(#B42318)
  :color(cell,background)}

Cold rooms, freezers and ambient zones read on the same scale: defrost spikes turn amber, a cooling ambient zone turns blue, and an excursion takes the deep blue or deep red. A second tag prints the excursion values in white:

{d.assets[i].days[i].pos:ifLT(0):show(#FFFFFF):ifGT(1):show(#FFFFFF):elseShow(#1F2430):color(cell,text)}

The [i+1] cell only needs the values: Carbone repeats the color tags of the [i] cell for each day.

5. Add the monthly aggregates of each asset

At the end of the data row, compute the monthly values of the current asset with empty brackets:

  • Minimum: {d.assets[i].days[].min:aggMin:formatN(1)}
  • Maximum: {d.assets[i].days[].max:aggMax:formatN(1)}
  • Time in range: {d.assets[i].readings:sub(.readingsOut):div(.readings):mul(100):formatN(2)}, followed by {d.assets[i].readingsOut:ifGT(0):show(#B42318):elseShow(#1F2430):color(cell,text)} to print it in red when the asset had an excursion.

The mean kinetic temperature {d.assets[i].mkt} is supplied by the platform, which computes it from every reading.

6. List the excursions and the sign-off

  • Repeat the excursions with {d.excursions[i].ref} and {d.excursions[i+1]}: probe, start, duration, peak, limit, product impact assessment and CAPA reference.
  • Color the status with {d.excursions[i].status:ifEQ(Open):show(#B45309):elseShow(#2F7D4F):color(p)}.
  • List the probes and their calibration due dates with a simple loop on d.assets, then add the review and approval block with {d.signoff.reviewedBy} and {d.signoff.approvedBy}.

Trusted by 800+ paid customers in 40+ countries

Finance
Healthcare
Insurance
Legal
Public
Retail
Energy
Software
Telecom
Dior
Thales
Younited
Danfoss
CMA CGM
Illumina
KparK
Scalingo
French Ministry of the Armed Forces
BMW
Telus
Bestseller
Flagship Biosciences
Insly
Ninox
Sodexo