Forum Discussion

Pmeems's avatar
Pmeems
Copper Contributor
Sep 21, 2022

How to show montly costs in time chart

I'm using the below JSON Body to get a grid with months and costs.

Very usefull. But I would like to visualize it as a time chart.

But when I select the time chart option in the visualization dropdown in my workbook, I get this error:

`Could not find appropriate columns for Time chart.`

 

This is my Azure Manager Resource url: /subscriptions/{Subscription:id}/resourceGroups/{ResourceGroup}/providers/Microsoft.CostManagement/query

And this is my JSON body:

 

{
	"type": "ActualCost",
	"dataSet": {
		"granularity": "Monthly",
		"sorting": [
			{
				"direction": "descending",
				"name": "BillingMonth"
			}			
		],
		"aggregation": {
			"totalCost": {
				"name": "Cost",
				"function": "Sum"
			}
		}
	},
	"timeframe": "YearToDate"
}

 

 

The result looks like:

Cost
BillingMonth
€41.81
September 2022
€61.30
August 2022
€37.59
July 2022

 

What do I need to do to make a nice looking time chart of the data, showing my per month the costs.

  • Theo_Kostelijk's avatar
    Theo_Kostelijk
    Oct 16, 2022

    Pmeems 

    Select Visualization: Bar Chart (Categorical)

    Then open Advanced Editor and add

        "chartSettings": {
          "xAxis": "BillingMonth",
          "yAxis": [
            "Cost"
          ],
          "group": null,
          "createOtherGroup": 0,
          "showLegend": true
        }

     

  • Theo_Kostelijk's avatar
    Theo_Kostelijk
    Icon for Microsoft rankMicrosoft
    Oct 16, 2022

    Pmeems 

    Select Visualization: Bar Chart (Categorical)

    Then open Advanced Editor and add

        "chartSettings": {
          "xAxis": "BillingMonth",
          "yAxis": [
            "Cost"
          ],
          "group": null,
          "createOtherGroup": 0,
          "showLegend": true
        }

     

    • Pmeems's avatar
      Pmeems
      Copper Contributor
      Sep 22, 2022

      Thanks Clive_Watson for you response.

      I removed the formatting of the Billing column in the 'Column Setting' popup.

      Resulting in these date values:
      2022-09-01T00:00:00
      2022-08-01T00:00:00
      2022-07-01T00:00:00

       

      But I still can't create any chart. This is my json result:

      {
    "id": "subscriptions/**/resourcegroups/**/providers/Microsoft.CostManagement/query/**",
    "name": "**",
    "type": "Microsoft.CostManagement/query",
    "location": null,
    "sku": null,
    "eTag": null,
    "properties": {
        "nextLink": null,
        "columns": [
            {
                "name": "Cost",
                "type": "Number"
            },
            {
                "name": "BillingMonth",
                "type": "Datetime"
            },
            {
                "name": "Currency",
                "type": "String"
            }
        ],
        "rows": [
            [
                43.19129521752406,
                "2022-09-01T00:00:00",
                "EUR"
            ],
            [
                61.30075222086911,
                "2022-08-01T00:00:00",
                "EUR"
            ],
            [
                37.593943340208604,
                "2022-07-01T00:00:00",
                "EUR"
            ]
        ]
    }
}

       

      In this block I get the data:

       

      I probably need to do some additional formatting, but I'm not sure how and where.

       

       

      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor
        Sep 22, 2022

        Pmeems 


        I see the same error with "Time Chart" and other chart controls.  As a test, I used the export parameter and then read in the selected row (you can see the ARG query), and you can build a chart from the results - so the data is good.

        The data shows as the right type as well, its a puzzle, I will have a think on this 

         

Resources