Need an advice on how to transform a date

Grafana Dashboards
1

I use Grafana v11.0.0

I want to display metrics in a table where:

  • rows = location

  • columns = probe

  • values = aggregated metric (AVG of selected metric)

Example of desired output:

location     | probe_1 | probe_2
-------------|---------|--------
location_a   |    10.5 |    12.3
location_b   |     8.2 |     9.1

Additionally, each metric value should have a data link pointing to a probe-specific URL.

I use PostgreSQL and return data in long format:

location_a   |  probe_1  |  10.5  | probe_ref_url_a 
location_a   |  probe_2  |  12.3  | probe_ref_url_b
location_b   |  probe_1  |   8.2  | probe_ref_url_c
location_b   |  probe_2  |   9.1  | probe_ref_url_d

The pivot (matrix) works correctly and I see in Table panel:

  • rows and columns are as expected

  • values are correct

However, the probe_ref_url field disappears after the transformation, so I cannot use it for data links for numeric values.

ALthough, I expected to still be able to attach a data link per cell, using the corresponding probe_ref_url.

Is there a way to:

  1. Keep dynamic columns (probes)

  2. Use pivot (matrix-like view)

  3. AND still have cell-level data links based on row-specific URLs?

  4. Or is there a recommended pattern/workaround for this use case?

2

Please try this , i am implement your problem in localhost postgresq and try to solve using grafana transformation, hope it will work for you.
thanks

image
image1366×687 83.1 KB

image
image1366×680 76.1 KB

3

I would do this purely in postgres not using any grafana transform then you have more control

Or use sql expression to take the pivoted data and rejoin it to original table to get the url

4

@infofcc3 your solution does not seem to solve the issue as it does not include the url needed

5

Doing the pivot purely in PostgreSQL does indeed give more control, especially for keeping additional fields like URLs. The main limitation I see there is that probes are dynamic, so it would require generating SQL (e.g. via variables or dynamic query construction), which is a bit less straightforward but probably the most robust approach.

The second suggestion (pivot → then rejoin with original data) is interesting. I was thinking about it, but the challenge is that after pivoting, each cell no longer has a direct mapping to a specific row (location + probe), so attaching the correct URL per cell becomes tricky.

1 Like
6

how would you like the final result to look like with the url given this pivoted data

location     | probe_1 | probe_2
-------------|---------|--------
location_a   |    10.5 |    12.3
location_b   |     8.2 |     9.1
7

In the final result I expect each cell value to have its own data link.

So for example:

  • location_a / probe_1 (10.5) → should link to probe_ref_url_a

  • location_a / probe_2 (12.3) → should link to probe_ref_url_b

So visually the table stays the same:

location     | probe_1 | probe_2
-------------|---------|--------
location_a   | 10.5 🔗 | 12.3 🔗
location_b   |  8.2 🔗 |  9.1 🔗

But each individual cell has its own URL behind it.

1 Like
8

So data wise, not visual wise, what would the final query result look like

9

Data-wise, I would expect each cell to still carry both the value and its corresponding URL.

Conceptually something like:

location     | probe_1_value | probe_1_url   | probe_2_value | probe_2_url
-------------|---------------|---------------|---------------|--------------
location_a   |     10.5      | url_a         |     12.3      | url_b
location_b   |      8.2      | url_c         |      9.1      | url_d

So instead of a single value per cell, each (location, probe) pair would have:

  • metric value

  • corresponding URL

1 Like
10

this is going to have to be done in postresql through some fancy dynamic query. you can’t do this in grafana via transforms.