Charting multiple years over a 12 month axis, starting at a custom defined month
Hi, I'm creating a graph inside of SSRS and it's been very difficult so far. I have two seperate requirements that I cannot seem to implement without causing problems. -My graph has values as the Y-axis and dates as the X-axis. The X-axis labels should be months, so there would be 12 marks for each month. But the second requirement causes problems: -I'm graphing multiple groups over 365 days on my chart. Each group has points for every day, but it can start in, say June, and span say, 2008-2009. The next group will also start at June but spans 2009-2010 etc. The graphed lines should be over top of one another, as if they were the same year. So my graph should have an X-Axis that reads June - Jul - Aug - Sep - Oct - Nov - Dec - Jan - Feb - Mar - Apr - May The best I can do is get the X-axis to display 4 months of the 12 seperated into quarters. I feel the solution lies somewhere in the custom expressions in the Horizontal Axis fields. Has anyone come across this before?
September 30th, 2011 8:06pm

Hi FourierSeries, Please take the following thread as a reference, http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/4bba2c65-cbef-46d0-b559-91c219961bab Please feel free to let me know if my understanding is wrong. Thanks, Eileen
Free Windows Admin Tool Kit Click here and download it now
October 3rd, 2011 4:28am

Hi FourierSeries, Please take the following thread as a reference, http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/4bba2c65-cbef-46d0-b559-91c219961bab Please feel free to let me know if my understanding is wrong. Thanks, Eileen
October 3rd, 2011 4:28am

Hi FourierSeries, Please take the following thread as a reference, http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/4bba2c65-cbef-46d0-b559-91c219961bab Please feel free to let me know if my understanding is wrong. Thanks, Eileen
Free Windows Admin Tool Kit Click here and download it now
October 3rd, 2011 4:28am

Hi FourierSeries, You need to create a column for your each group/year, for example "[NameOfGroup]_2008", "[NameOfGroup]_2009". Do aggregation for each group, for example for each group on year - YEAR([DateCreated]) and month - MONTH([DateCreated]): SELECT SUM([InvoiceTotal]) AS Total , MONTH(DateCreated), [NameOfGroup] + '_' + CAST(YEAR([DateCreated]) as varchar) AS Legend FROM tableName GROUP BY YEAR(DateCreated), MONTH(DateCreated), [NameOfGroup] + '_' + CAST(YEAR([DateCreated]) as varchar)Sergei
October 3rd, 2011 7:23am

Hi FourierSeries, You need to create a column for your each group/year, for example "[NameOfGroup]_2008", "[NameOfGroup]_2009". Do aggregation for each group, for example for each group on year - YEAR([DateCreated]) and month - MONTH([DateCreated]): SELECT SUM([InvoiceTotal]) AS Total , MONTH(DateCreated), [NameOfGroup] + '_' + CAST(YEAR([DateCreated]) as varchar) AS Legend FROM tableName GROUP BY YEAR(DateCreated), MONTH(DateCreated), [NameOfGroup] + '_' + CAST(YEAR([DateCreated]) as varchar)Sergei
Free Windows Admin Tool Kit Click here and download it now
October 3rd, 2011 7:23am

Hi FourierSeries, You need to create a column for your each group/year, for example "[NameOfGroup]_2008", "[NameOfGroup]_2009". Do aggregation for each group, for example for each group on year - YEAR([DateCreated]) and month - MONTH([DateCreated]): SELECT SUM([InvoiceTotal]) AS Total , MONTH(DateCreated), [NameOfGroup] + '_' + CAST(YEAR([DateCreated]) as varchar) AS Legend FROM tableName GROUP BY YEAR(DateCreated), MONTH(DateCreated), [NameOfGroup] + '_' + CAST(YEAR([DateCreated]) as varchar)Sergei
October 3rd, 2011 7:23am

Thanks for the replies ^ I am not having trouble grouping the series together. The problem is the X-axis on the graph. I should be able to take just the month from each date point, and tell the X-axis marks to group based on the month. Since my query is ordered already the way I want it (starting in June, going to May) and not January to December, I think this is what is causing the issue.
Free Windows Admin Tool Kit Click here and download it now
October 3rd, 2011 12:10pm

I solved the problem by going into the X-axis properties > Axis Options and setting the Interval to 31 and the Interval Type to Auto. I also hid major and minor tick marks. Hope this helps someone out there.
October 14th, 2011 10:56am

I solved the problem by going into the X-axis properties > Axis Options and setting the Interval to 31 and the Interval Type to Auto. I also hid major and minor tick marks. Hope this helps someone out there.
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2011 10:56am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics