0

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!

RMu
  • 817
  • 2
  • 17
  • 41

1 Answers1

1

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.

Chanom First
  • 1,136
  • 1
  • 11
  • 25
  • I have ordered like you said in the TSQL. Now the dates are ordered but i get some extra values after the full 30 days comparision. the extra values are from the month of July(7 in this case) which are of some random dates like 9,10,12,26,27 and so on. It's not continuos though. I dont understand why these values pop up! I have updated the graph to the question to show you. – RMu Aug 08 '16 at 23:32
  • I think the date, times which do not have comparison points comes separately. i have 625 data point for June and 675 Points for July. Will that be an issue when I categorise group by day and time and serius group by month? Please help me eliminate the values that are not having matching data points – RMu Aug 09 '16 at 01:51
  • I have a large result set. How can I share it with you? I'm not sure if I have option here to upload – RMu Aug 09 '16 at 03:23
  • Can you email to me ? if you can here my email chanon.kitkumthorn@gmail.com – Chanom First Aug 09 '16 at 03:28
  • Just sent you the result set to your email. Thanks! – RMu Aug 09 '16 at 03:41
  • I sent Email and solution to you if can solve you problem please notify me. – Chanom First Aug 09 '16 at 04:59
  • You're awesome! Worked like a charm!! – RMu Aug 09 '16 at 17:09