SSRS: Category groups randomly changing order

Question

I have a few column charts in a ssrs report. The x-axis (categories) are the past 6 months. There is no ordering on the chart - I do all that in the SQL query and just want them to display in the order of the query.

It usually does this, i.e. in order: Apr, May, Jun, Jul, Aug, Sep, Oct

However sometimes it will display: Oct, Apr, May, Sep, Jun, Jul, Aug

After a little while it will jump back to how it should be. Does anyone have any insight into why it would suddenly change?

The report is not filtered by anything.

Answer 1

You need to sort the category group by. In the chart data options select the dropdown for the category group and go to Category Group Properties.

On the left-hand side you'll see a section called "Sorting". Add your date column to the sorting and it should always be sorted correctly.

Alternatively you can use an ORDER BY in your query to ensure you get the rows in the correct order. However if you're grouping and aggregating rows in the chart I'm not 100% sure if the behaviour will hold up.

Even if you return Jan,Feb, etc you could use a SWITCH() statement to get the correct order but that would be painful. Better to return a column with a number or date in it to allow you to make sure you order the chart correctly.