SSRS Graph Axis Label Issues

Question

So I've made a graph in SSRS 2008 R2, and for whatever reason, the axis labels refuse to show up as date values and instead show up as the number of the month they're representing (for example, January shows up as "1", February as "2", etc.). I've messed around with the various properties of the graph, from the "Axis Options" to the "Number" tab. Unfortunately, nothing has worked so far. Only one other change was made to the graph, and that was the sorting in the Category Group so that the graph may pull data from different years. This is the code that gets me my data for the graph:

SELECT  Year = DATEPART(YEAR, date1),
date1 = DATEPART(MONTH, date1),
sum(qty) as TotalQTY 
FROM    TableMain
WHERE   Tablemain.date1 between @startdate and @enddate and Tablemain.fillerparam in(@fillerparam) and Tablemain.filler2 in (@filler2)
GROUP BY DATEPART(MONTH, date1), DATEPART(YEAR, date1)

Thank you.

score 0 · Accepted Answer · edited May 23 '17 at 10:25

Your query can be changed to include the name of the month. You can use the name rather than the number when binding to the chart so that the axis can display the month name rather than the month number.

Here is an example of how to get the name of the month:

SELECT  Year = DATEPART(YEAR, date1),
date1 = DATEPART(MONTH, date1),
SUM(qty) as TotalQTY,
CASE -- Case statement translates the integer of the month into the name of the month
    WHEN DATEPART(MONTH, date1) = 1 THEN 'January'
    WHEN DATEPART(MONTH, date1) = 2 THEN 'February'
    WHEN DATEPART(MONTH, date1) = 3 THEN 'March'
    WHEN DATEPART(MONTH, date1) = 4 THEN 'April'
    WHEN DATEPART(MONTH, date1) = 5 THEN 'May'
    WHEN DATEPART(MONTH, date1) = 6 THEN 'June'
    WHEN DATEPART(MONTH, date1) = 7 THEN 'July'
    WHEN DATEPART(MONTH, date1) = 8 THEN 'August'
    WHEN DATEPART(MONTH, date1) = 9 THEN 'September'
    WHEN DATEPART(MONTH, date1) = 10 THEN 'October'
    WHEN DATEPART(MONTH, date1) = 11 THEN 'November'
    WHEN DATEPART(MONTH, date1) = 12 THEN 'December'
    ELSE ''
END AS 'name_of_month'
FROM    TableMain
WHERE   Tablemain.date1 between @startdate and @enddate and Tablemain.fillerparam in(@fillerparam) and Tablemain.filler2 in (@filler2)
GROUP BY DATEPART(MONTH, date1), DATEPART(YEAR, date1),
CASE  -- Add the case statement to the GroupBy so that it still groups as expected
    WHEN DATEPART(MONTH, date1) = 1 THEN 'January'
    WHEN DATEPART(MONTH, date1) = 2 THEN 'February'
    WHEN DATEPART(MONTH, date1) = 3 THEN 'March'
    WHEN DATEPART(MONTH, date1) = 4 THEN 'April'
    WHEN DATEPART(MONTH, date1) = 5 THEN 'May'
    WHEN DATEPART(MONTH, date1) = 6 THEN 'June'
    WHEN DATEPART(MONTH, date1) = 7 THEN 'July'
    WHEN DATEPART(MONTH, date1) = 8 THEN 'August'
    WHEN DATEPART(MONTH, date1) = 9 THEN 'September'
    WHEN DATEPART(MONTH, date1) = 10 THEN 'October'
    WHEN DATEPART(MONTH, date1) = 11 THEN 'November'
    WHEN DATEPART(MONTH, date1) = 12 THEN 'December'
    ELSE ''
END
    ORDER BY date1 -- Added order by to make sure that records are sorted in the report by date and not by alphabetic name of month

You can also check out this post for a way to get the month name without using a case statement: Convert Month Number to Month Name Function in SQL

Thank you! I hadn't thought of using case. The only part that gave an issue was the `AS 'name_of_month'` after the second `CASE`, but aside from that, it works flawlessly. — PrincessTrevor, Oct 08 '14 at 18:45
The example has been updated to remove the `AS 'name_of_month'` from the second `CASE`. — Jesse Potter, Oct 08 '14 at 19:43

SSRS Graph Axis Label Issues

1 Answers1