none
Charting multiple years over a 12 month axis, starting at a custom defined month

    Question

  • 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?

    Friday, September 30, 2011 11:58 PM

Answers

  • 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.

    • Marked as answer by FourierSeries Friday, October 14, 2011 2:46 PM
    Friday, October 14, 2011 2:46 PM

All replies

  • 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
    Monday, October 03, 2011 8:27 AM
  • 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
    Monday, October 03, 2011 11:22 AM
  • 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.

    Monday, October 03, 2011 4:09 PM
  • 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.

    • Marked as answer by FourierSeries Friday, October 14, 2011 2:46 PM
    Friday, October 14, 2011 2:46 PM