I have run into this issues before and my solution was to use a barchart! Anyway, There must be a way to accomplish this most basic of tasks!
I have a table with a DateTime, and some other numbers, in this case, I have hourly data, I want to average this data for an entire day, and have multiple days (any selection range) on the report. Casting the date to group on the entire day returns a date format of yyyy-mmm-dd, but the timeseries report does not like it! I thought it was the dashes instead of slashes, or that there was no time, so I tried FORMAT after casting, which returns dates formatted yyyy/mm/dd 00:00:00, but still does not work…after some digging I found that FORMAT returns a varchar type, so I tried Convert to put the varchar back as a date, still no worky! What I mean by it doesnt work is, the preview shows al of the data and it appears proper, but chart shows all of the data point aligned vertically with an improper date.
Here is the basic query
SELECT CAST([DateTime] AS DATE) as Date, AVG([float]) as floatname, etc…
FROM tblName
WHERE DateTime BETWEEN ? AND ?
GROUP BY CAST([DateTime] AS DATE)
This runs and works in the preview (the date is correct in the xml preview), but the chart shows all data in one vertical row with a date of 19:00:02.2019
Second try was adding the time back to the date…
SELECT FORMAT(CAST([DateTime] AS DATE), ‘MM/dd/yy hh:mm:ss’) as Date, AVG([float]) as floatname, etc…
FROM tblName
WHERE DateTime BETWEEN ? AND ?
GROUP BY CAST([DateTime] AS DATE)
This runs fine and the xml preview works and shows the date in the proper format, but still the cart show one column of data and the date for the x is 19:00:00.007
I must be overlooking something simple as I would think aggregating data would be a common function to chart on a timeseries chart!
Thanks
Leslie