March 21, 2012 at 5:34 am
Hi
I am trying to create a report using the below data:
Report_Date Base_Name
2010-12-15 00:00:00.000 Main
2011-01-27 00:00:00.000 Outer
2011-06-27 00:00:00.000 Main
2011-06-28 00:00:00.000 Main
2011-07-18 00:00:00.000 Inner
2011-09-21 00:00:00.000 Inner
2011-10-11 00:00:00.000 Outer
2011-10-04 00:00:00.000 Outer
2011-10-14 00:00:00.000 Central
2012-01-18 00:00:00.000 Inner
I want to create a line chart showing the bases, split by 6 month periods, on the X axis and a count on the Y. I have tried but am well and truly stuck!
Any help would be great.
Thanks.
March 21, 2012 at 7:39 pm
I would make the query return the proper groupings instead of trying to do it inside your report.
Sample query to break your dates into half-years:
Select halfyear, base_name, COUNT(base_name) as 'Count' from (
Select Case DATEPART(q, Report_date)
When 1 then Cast (DATEPART(yy, Report_Date) as nvarchar) + '-1'
When 2 then Cast (DATEPART(yy, Report_Date) as nvarchar) + '-1'
When 3 Then Cast (DATEPART(yy, Report_Date) as nvarchar) + '-2'
When 4 then Cast (DATEPART(yy, Report_Date) as nvarchar) + '-2'
End as 'halfyear',
base_name
from Report_Date) as report_dateHalf
group by halfyear, base_name
Order by halfyear
Sample output:
halfyearbase_nameCount
2010-2Main1
2011-1Main2
2011-1Outer1
2011-2Central1
2011-2Inner2
2011-2Outer2
2012-1Inner1
The sample query returns your three columns (half-year, Base_Name and Count), and now you just need to add the half-year and base_name to your Category groups and Count to your values for your line chart.
Cheers
Lars
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply