Newbie Struggling to Use Database Data as Opposed to Performance Data

I’m trying to produce a graph type dashboard that presents me details of the number of products sold in each product group on a daily basis.

The datasource I’m using is SQL Server, as my database is held there, and that all connects fine. Unfortunately though, whenever I google search “example Grafana SQL query dashboards” (or something similar), it only seems to return queries that help establish the performance of the SQL Server, or up-time etc. :frowning:

Below is an example of the query I’m using…

SELECT
ProdGroup as ‘Product Group’, TransDate as ‘Order Date’, SUM(Quantity) as ‘Unit Count’
FROM OrdersTable
WHERE STR_TO_DATE(TransDate,’%Y-%m-%d %H:%i:%s’) BETWEEN
DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AND
DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)
GROUP BY ProdGroup, TransDate
ORDER BY 1, 2

I’ve got the format set as a “time series” which I believe is correct however, when I attempt to preview or refresh the top pane in the panel edit screen and run this, I get an error in the preview that states “Data does not have a time field”

Found a few bits that suggest I need toCAST my date, and to use STR_TO_DATE function with ‘%Y-%m-%d %H:%I:%s’ but that doesn’t seem to do anything either.

Further to this though, the data I’m looking to present is going to be in daily groups (and on another summary I needs to show Monthly - YTD - and even a YTD from last year as a comparison) whereas, the stuff I’ve found, and looking in the Grafana application, it seems to be more about reporting at seconds and minutes levels.

It could be that I’m simply way off the mark and looking in the wrong place to achieve this stuff. If someone can point me in the right direction, I’d be most grateful! Thanks in advance!

J

Hi @JueBob,

I think the most useful thing for the community would be if you share your unformatted data, as it appears in the inspect drawer. If you can share that, or even a screenshot of your query response in table view with the format set to table would also help.