Drilldown grafana

Grafana Dashboards
1

So Currently this is my query and i also have a transformation GROUP BY subaccount and it allows me to get (look at the screenshoot).
As you can see in the query i have to manually look for each month that i desire
how can i improve my query (or something else) to obtain each avalaible month
and to preserve my global structure (drilldown subaccount/service)

WITH LastCostPerServicePerMonth AS (
SELECT
subaccount,
service,
measure_value::double AS Cost,
date_format(time, ‘%Y-%m’) AS month,
ROW_NUMBER() OVER (
PARTITION BY subaccount, service, date_format(time, ‘%Y-%m’)
ORDER BY time DESC
) AS rn
FROM $__database.$__table
WHERE $__timeFilter
)
SELECT
subaccount,
service,
SUM(CASE WHEN month = ‘2025-02’ THEN Cost ELSE 0 END) AS “feb 2025”
FROM LastCostPerServicePerMonth
WHERE rn = 1
GROUP BY subaccount, service
ORDER BY subaccount, service

Capture d'écran 2025-04-02 150314
Capture d'écran 2025-04-02 1503141478×375 11.6 KB

2

Hello there!

To avoid manually specifying each month, you can pivot the data using a structure that keeps your original logic, but aggregates costs per month using GROUP BY and date_format.

Try a query like this:

WITH LastCostPerServicePerMonth AS (
  SELECT
    subaccount,
    service,
    measure_value::double AS Cost,
    date_format(time, '%Y-%m') AS month,
    ROW_NUMBER() OVER (
      PARTITION BY subaccount, service, date_format(time, '%Y-%m')
      ORDER BY time DESC
    ) AS rn
  FROM $__database.$__table
  WHERE $__timeFilter
)
SELECT
  subaccount,
  service,
  month,
  SUM(Cost) AS monthly_cost
FROM LastCostPerServicePerMonth
WHERE rn = 1
GROUP BY subaccount, service, month
ORDER BY subaccount, service, month

This gives you one row per subaccount/service/month with the corresponding cost.

To pivot months into columns in Grafana, use:

  1. Add transformation → Group by
  • Fields: subaccount, service
  • Aggregation: monthly_cost → sum or last
  1. Add transformation → Labels to fields
  • Key field: month
  • Value field: monthly_cost
    Use Organize fields or Field overrides to clean up the display.
1 Like
3

image
image870×542 38.6 KB

No key field or value field option present in Lables to fields.

4

Hy momo123
this is your requirement??

image
image568×140 6.47 KB

steps to achive this:
Step 1:Create table and insert data in it
image
image506×469 25.9 KB

Step 2:Select table visualization and use this query to disply table in grafana panel.
image
image583×272 15.6 KB

5

What will happen to this query in 2026 or for 2024?

6

hardcoded timestamps ('2025-02-15', '2025-02-18', etc.) into the time column of the aps_costs table.
In 2026 or 2024, the hardcoded 2025 dates will fall outside Grafana’s default time range filters, showing no data.
Use NOW() - INTERVAL instead of fixed dates to keep data within the current time window.

7

Hard coding those values into the table breaks all kinds of db design rules and standards

On simple example, What iif this db is used in a different country where the date format is different

MM-DD-YYYY

or something altogether different.

8

Yes, you’re absolutely right!
Using dynamic values like NOW() makes the database work better across countries and avoids date format problems.
Thanks for pointing it out!