CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
Sabr786
Sabr786

Reputation: 57

Getting Months to Display in the Correct Order in SSRS Chart

Alright so what I am trying to do is retrieve data from two tables from two different databases on the same server and create a chart in Reporting Services. I am currently on ssrs r2 2008. For the chart, I am required to sort by year and month since I have different years as well, and count the amount of names that are classified as Active per month. I am able to order the information by year, but the months are always ordered alphabetically as opposed to chronologically. I have tried to delete the default sorting in ssrs but it still returns the same result, so I believe that the sorting must be done in my query. My query is as follows:

SELECT COUNT(c.name) AS name,
       DATENAME(mm, c.expireson) AS Month,
       DATENAME(yyyy, c.expireson) AS Year

FROM Database1.Table1 AS c

WHERE c.name = 'Active' 

GROUP BY DATENAME(mm, c.expireson), DATENAME(yyyy, c.expireson)

UNION ALL

SELECT COUNT(d.name) AS name,
         DATENAME(mm, d.expireson) AS Month,
         DATENAME(yyyy, d.expireson) AS Year

FROM Database2.Table2 AS d

WHERE d.name = 'Active' 

GROUP BY DATENAME(mm, d.expireson), DATENAME(yyyy, d.expireson)

Any help as to how I can change this to order the months correctly would be greatly appreciated. Thanks in advance!

Upvotes: 2

Views: 5784

Answers (2)

Peace
Peace

Reputation: 1

I had the same problem.

It turned out that in the Category Group Properties the Sorting Tab had a different field sorting data than the one I needed.

Hope this helps someone in the future...

Upvotes: 0

radar
radar

Reputation: 13425

As mentioned by you if you sort by month name it will sort data alphabitcally

so sort by month number using datepart

SELECT COUNT(c.name) AS name,
       DATENAME(mm, c.expireson) AS Month,
       DATENAME(yyyy, c.expireson) AS Year,
       DATEPART(yyyy,c.expireson) as YearNum,
       DATEPART(m, c.expireson) as MonthNum

FROM Database1.Table1 AS c

WHERE c.name = 'Active' 

GROUP BY DATENAME(mm, c.expireson), DATENAME(yyyy, c.expireson),DATEPART(yyyy,c.expireson), DATEPART(m, c.expireson)

UNION ALL

SELECT COUNT(d.name) AS name,
         DATENAME(mm, d.expireson) AS Month,
         DATENAME(yyyy, d.expireson) AS Year,
         DATEPART(yyyy,d.expireson) as YearNum,
         DATEPART(m, d.expireson) as MonthNum
FROM Database2.Table2 AS d

WHERE d.name = 'Active' 

GROUP BY DATENAME(mm, d.expireson), DATENAME(yyyy, d.expireson),DATEPART(yyyy,d.expireson), DATEPART(m, d.expireson)
order by YearNum, MonthNum

Upvotes: 3

Related Questions