Formatting Day Time X Axis in SSRS Charts

0
votes

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. Data Comparision

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!

enter image description here Thank you!

1
sqltsqlreporting-servicesssrs-2008ssrs-2008-r2
I suggest you to sort from SQL server and then delete Chart sort. or can you post your query ? – Chanom First
@ChanomFirst Added my query to the question – RMu
@RMu how did you set up the X axis to get it to look like that with the time? – YelizavetaYR

1 Answers

1
votes

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.