I am working on a SSRS report which compares data of 2 given months. I'm categorising the chart based on Day and time in the following format 1, 6:00 AM. I get this column from the T-SQL itself. But the axis does not come properly. It looks like below now which doesn't make sense. I want it to be in order from 1st to 30th with the time component.
I guess I need some kind of sorting on X-axis with respect to date time. Please help!
After deleting from sorts from chart I'm getting some extra repitive dates after comparing all 30 days of both months. Data from the query looks alright to me!
Ok. Large query
You X-axis are show value in format
date,hh mm tt Right ?
Then you want to sort them with day number 1 - 30.
From your query I suggest you add 1 field is like this
CAST(SampleCollected AS DATE) [orders] and use this field in Order in Query or Sort on SSRS (not recommend ) and if you use Order in Query must delete sort condition on chart sort.
But if result still not you want try to add
MONTH(SampleCollected) As MonthG to order again like this
ORDER BY MONTH(SampleCollected),CAST(SampleCollected AS DATE)
Hope it's Help.