-
What Grafana version and what operating system are you using?
Linux arm and Grafana 11.5 and Timestream DB -
What are you trying to achieve?
We try to create a panel that represent a table tree view.
-
How are you trying to achieve it?
I start to used the table view with the group nested table transformation but we don’t get the exact needed result
-
What happened?
We don’t succeed to get one column per month and line group by subaccount -
What did you expect to happen?
We need exactly what is on the first picture. Each line represent a subaccount that we can click on it to make appear the service and associated cost. Column are each month. -
Can you copy/paste the configuration(s) that you are having problems with?
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 8,
"links": [],
"panels": [
{
"collapsed": false,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
"y": 0
},
"id": 4,
"panels": [],
"title": "Global costs",
"type": "row"
},
{
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 2,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"unit": "currencyEUR"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "Total"
},
"properties": [
{
"id": "custom.hideFrom",
"value": {
"legend": true,
"tooltip": false,
"viz": true
}
}
]
}
]
},
"gridPos": {
"h": 9,
"w": 17,
"x": 0,
"y": 1
},
"id": 2,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"fullHighlight": false,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "right",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "normal",
"text": {},
"tooltip": {
"hideZeros": true,
"mode": "multi",
"sort": "asc"
},
"xField": "month",
"xTickLabelRotation": 0,
"xTickLabelSpacing": 0
},
"pluginVersion": "11.5.1",
"targets": [
{
"database": "\"awsp-sap-btp\"",
"format": 0,
"measure": "cost",
"rawQuery": "WITH LastCostPerServicePerMonth AS (\r\n SELECT \r\n globalAccount, \r\n subaccount, \r\n service, \r\n measure_value::double as cost,\r\n date_format(time, '%Y-%m') AS month, -- Extract the month concerned by the cost (PostgreSQL)\r\n ROW_NUMBER() OVER (\r\n PARTITION BY globalAccount, subaccount, service, date_format(time, '%Y-%m') \r\n ORDER BY time DESC\r\n ) AS rn\r\n FROM $__database.$__table\r\n WHERE $__timeFilter\r\n)\r\nSELECT \r\n globalAccount, \r\n month, \r\n SUM(cost) as Cost\r\nFROM LastCostPerServicePerMonth\r\nWHERE rn = 1\r\nGROUP BY globalAccount, month\r\nORDER BY globalAccount, month\r\n",
"refId": "A",
"table": "\"awsp-cost\""
}
],
"title": "Global account costs",
"transformations": [
{
"id": "groupingToMatrix",
"options": {
"columnField": "globalAccount",
"rowField": "month",
"valueField": "Cost"
}
},
{
"id": "calculateField",
"options": {
"mode": "reduceRow",
"reduce": {
"include": [],
"reducer": "sum"
}
}
}
],
"type": "barchart"
},
{
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"decimals": 2,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"unit": "currencyEUR"
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 7,
"x": 17,
"y": 1
},
"id": 6,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "11.5.1",
"targets": [
{
"database": "\"awsp-sap-btp\"",
"format": 0,
"measure": "cost",
"rawQuery": "WITH LastCostPerServicePerMonth AS (\r\n SELECT \r\n globalAccount, \r\n subaccount, \r\n service, \r\n measure_value::double as cost,\r\n date_format(time, '%Y-%m') AS month, -- Extract the month concerned by the cost (PostgreSQL)\r\n ROW_NUMBER() OVER (\r\n PARTITION BY globalAccount, subaccount, service, date_format(time, '%Y-%m') \r\n ORDER BY time DESC\r\n ) AS rn\r\n FROM $__database.$__table\r\n WHERE $__timeFilter\r\n)\r\nSELECT \r\n globalAccount, \r\n month, \r\n SUM(cost) as Cost\r\nFROM LastCostPerServicePerMonth\r\nWHERE rn = 1\r\nGROUP BY globalAccount, month\r\nORDER BY globalAccount, month\r\n",
"refId": "A",
"table": "\"awsp-cost\""
}
],
"title": "Total cost",
"transformations": [
{
"id": "groupingToMatrix",
"options": {
"columnField": "globalAccount",
"rowField": "month",
"valueField": "Cost"
}
},
{
"id": "calculateField",
"options": {
"mode": "reduceRow",
"reduce": {
"include": [],
"reducer": "sum"
},
"replaceFields": true
}
},
{
"id": "reduce",
"options": {
"reducers": [
"sum"
]
}
}
],
"type": "stat"
},
{
"collapsed": false,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
"y": 10
},
"id": 5,
"panels": [],
"title": "TGITS - APS costs",
"type": "row"
},
{
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 0,
"scaleDistribution": {
"type": "linear"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 2,
"mappings": [],
"noValue": "0",
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"unit": "currencyEUR"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "Total"
},
"properties": [
{
"id": "custom.hideFrom",
"value": {
"legend": true,
"tooltip": false,
"viz": true
}
}
]
}
]
},
"gridPos": {
"h": 11,
"w": 12,
"x": 0,
"y": 11
},
"id": 3,
"links": [
{
"title": "",
"url": "https://grafana.controltoolscenterp.alzp.tgscloud.net/goto/XPKMH2THg?orgId=1"
}
],
"options": {
"barRadius": 0,
"barWidth": 0.9,
"fullHighlight": false,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "right",
"showLegend": true
},
"orientation": "auto",
"showValue": "never",
"stacking": "normal",
"text": {
"valueSize": 14
},
"tooltip": {
"hideZeros": true,
"mode": "multi",
"sort": "asc"
},
"xField": "time_interval",
"xTickLabelRotation": 0,
"xTickLabelSpacing": 100
},
"pluginVersion": "11.5.1",
"targets": [
{
"database": "\"awsp-sap-btp\"",
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"format": 0,
"measure": "cost",
"rawQuery": "WITH LastCostPerServicePerMonth AS (\r\n SELECT \r\n globalAccount, \r\n subaccount, \r\n service, \r\n measure_value::double as cost,\r\n date_format(time, '%Y-%m') AS month,\r\n ROW_NUMBER() OVER (\r\n PARTITION BY globalAccount, subaccount, service, date_format(time, '%Y-%m') \r\n ORDER BY time DESC\r\n ) AS rn\r\n FROM $__database.$__table\r\n WHERE $__timeFilter\r\n)\r\nSELECT \r\n subaccount, \r\n month, \r\n SUM(cost) as Cost\r\nFROM LastCostPerServicePerMonth\r\nWHERE rn = 1 AND globalAccount = 'TGITS - APS'\r\nGROUP BY subaccount, month\r\nORDER BY subaccount, month",
"refId": "A",
"table": "\"awsp-cost\""
}
],
"title": "TGITS - APS costs per subaccount",
"transformations": [
{
"id": "groupingToMatrix",
"options": {
"columnField": "subaccount",
"emptyValue": "zero",
"rowField": "month",
"valueField": "Cost"
}
},
{
"id": "calculateField",
"options": {
"alias": "Total",
"binary": {
"left": {
"fixed": ""
},
"right": {
"fixed": ""
}
},
"cumulative": {
"field": "month\\subaccount",
"reducer": "sum"
},
"mode": "reduceRow",
"reduce": {
"include": [],
"reducer": "sum"
},
"replaceFields": false,
"window": {
"reducer": "mean",
"windowAlignment": "trailing",
"windowSize": 0.1,
"windowSizeMode": "percentage"
}
}
}
],
"type": "barchart"
},
{
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 2,
"mappings": [],
"noValue": "0",
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"unit": "currencyEUR"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "Total"
},
"properties": [
{
"id": "custom.hideFrom",
"value": {
"legend": true,
"tooltip": false,
"viz": true
}
}
]
}
]
},
"gridPos": {
"h": 11,
"w": 12,
"x": 12,
"y": 11
},
"id": 1,
"links": [
{
"title": "",
"url": "https://grafana.controltoolscenterp.alzp.tgscloud.net/goto/XPKMH2THg?orgId=1"
}
],
"options": {
"barRadius": 0,
"barWidth": 0.97,
"fullHighlight": false,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "right",
"showLegend": true
},
"orientation": "auto",
"showValue": "never",
"stacking": "normal",
"text": {
"valueSize": 14
},
"tooltip": {
"hideZeros": true,
"mode": "multi",
"sort": "asc"
},
"xField": "time_interval",
"xTickLabelRotation": 0,
"xTickLabelSpacing": 100
},
"pluginVersion": "11.5.1",
"targets": [
{
"database": "\"awsp-sap-btp\"",
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"format": 0,
"measure": "cost",
"rawQuery": "WITH LastCostPerServicePerMonth AS (\r\n SELECT \r\n globalAccount, \r\n subaccount, \r\n service, \r\n measure_value::double as cost,\r\n date_format(time, '%Y-%m') AS month,\r\n ROW_NUMBER() OVER (\r\n PARTITION BY globalAccount, subaccount, service, date_format(time, '%Y-%m') \r\n ORDER BY time DESC\r\n ) AS rn\r\n FROM $__database.$__table\r\n WHERE $__timeFilter\r\n)\r\nSELECT \r\n service, \r\n month, \r\n SUM(cost) as Cost\r\nFROM LastCostPerServicePerMonth\r\nWHERE rn = 1 AND globalAccount = 'TGITS - APS'\r\nGROUP BY service, month\r\nORDER BY service, month\r\n",
"refId": "A",
"table": "\"awsp-cost\""
}
],
"title": "TGITS - APS costs per service",
"transformations": [
{
"id": "groupingToMatrix",
"options": {
"columnField": "service",
"emptyValue": "zero",
"rowField": "month",
"valueField": "Cost"
}
},
{
"id": "calculateField",
"options": {
"mode": "reduceRow",
"reduce": {
"include": [
"API Management",
"Application Logging Service",
"Business Application Studio",
"Cloud Foundry Runtime",
"Cloud Integration",
"Cloud Portal Service",
"Cloud Transport Management",
"Conversational AI",
"Document Information Extraction",
"Event Mesh",
"Integration Suite",
"Job Scheduling Service",
"Mobile Services",
"SAP Build Work Zone, standard edition",
"SAP Datasphere",
"SAP HANA Cloud",
"Web IDE Full-Stack",
"Data Quality Services"
],
"reducer": "sum"
}
}
}
],
"type": "barchart"
},
{
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "auto",
"cellOptions": {
"type": "auto"
},
"inspect": false
},
"decimals": 2,
"mappings": [],
"noValue": "0",
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"unit": "currencyEUR"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "Total"
},
"properties": []
}
]
},
"gridPos": {
"h": 12,
"w": 24,
"x": 0,
"y": 22
},
"id": 7,
"options": {
"cellHeight": "sm",
"footer": {
"countRows": false,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "11.5.1",
"targets": [
{
"database": "\"awsp-sap-btp\"",
"datasource": {
"type": "grafana-timestream-datasource",
"uid": "bef53cr7lfj7kb"
},
"format": 0,
"measure": "cost",
"rawQuery": "WITH LastCostPerServicePerMonth AS (\r\n SELECT \r\n globalAccount, \r\n subaccount, \r\n service, \r\n measure_value::double as cost,\r\n date_format(time, '%Y-%m') AS month,\r\n ROW_NUMBER() OVER (\r\n PARTITION BY globalAccount, subaccount, service, date_format(time, '%Y-%m') \r\n ORDER BY time DESC\r\n ) AS rn\r\n FROM $__database.$__table\r\n WHERE $__timeFilter\r\n)\r\nSELECT \r\n subaccount,\r\n service,\r\n month, \r\n SUM(cost) as Cost\r\nFROM LastCostPerServicePerMonth\r\nWHERE rn = 1 AND globalAccount = 'TGITS - APS'\r\nGROUP BY subaccount, service, month\r\nORDER BY subaccount, service, month\r\n",
"refId": "A",
"table": "\"awsp-cost\""
}
],
"title": "TGITS - APS costs drilldown",
"transformations": [
{
"id": "calculateField",
"options": {
"mode": "reduceRow",
"reduce": {
"include": [],
"reducer": "sum"
}
}
},
{
"id": "groupToNestedTable",
"options": {
"fields": {
"Cost": {
"aggregations": [
"sum"
]
},
"Total": {
"aggregations": [
"sum"
]
},
"month": {
"aggregations": []
},
"service": {
"aggregations": []
},
"subaccount": {
"aggregations": [],
"operation": "groupby"
}
},
"showSubframeHeaders": true
}
}
],
"type": "table"
}
],
"preload": false,
"refresh": "",
"schemaVersion": 40,
"tags": [
"sap btp",
"cost"
],
"templating": {
"list": []
},
"time": {
"from": "now-6M",
"to": "now"
},
"timepicker": {},
"timezone": "browser",
"title": "SAP BTP Costs",
"uid": "eegipcaxvpfy8d",
"version": 46,
"weekStart": ""
}
-
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
No error message -
Did you follow any online instructions? If so, what is the URL?
Nothing
Thank you !