Last updated on August 12th, 2022 at 09:24 am
As I write this blog, I’m working on a chart that has labels on it. I almost never add chart labels, instead I used an attached table with matching colors to show which values matches which line within the chart. But for this chart, it made sense to add labels to it. Now the problem is when I look at the chart I expect that the labels will sort alphabetically. But my SQL Server Reporting Services (SSRS) charts sorting is descending (z-a) and my table is ascending (a-z) this of course bugs me. So how to I fix my SSRS chart sorting? Like my other SSRS tips Four Little Reporting Tips with a Big Impact, this tip can have a big impact on you dashboards. This blog will give you step by step on how to do that. But first lets look at the problem.
Problem
Notice that within the left chart the Unknown is listed first where as within the table it is listed last. This is repeated for each item in BOTH charts and tables. I would expect the both the chart labels and the table values to be sorted a-z, this means the unknown software update status should be at the bottom for BOTH the chart and table. Don’t you agree?
If you are link me, the firs thing that you will do is check your SQL query to make sure that you added the Order by clause and that it is right, It was.
Why does this happen? The answer is simple, since SSRS doesn’t know how many items are within the chart when it first renders it, it takes the first row and puts that closest to the x axis then add the next row above it until all rows are added to the chart. So, the chart is ascending (a-z) from the X axis. But we (most people) read from top to bottom and left to right. This is where the expectation that Compliant would be first label on the chart and not last.
How to fix the SSRS chart sorting
Once you know why it is happening the solution is easy. For the chart change the sort order for descending (z-a) and everything is fixed.
I will give the steps for Visual Studio, but the steps are basically the same for Report Builder too.
Select your chart, expose the Chart Data pop out. Within the Category Groups area, select your group and right-click it. Then select Category Group Properties… node.
Select the Sorting Node. Then click the Order drop down and change to Z to A option. Click OK to save the option.
Next preview the report. Notice that the left chart’s labels are now showing ascending and match the table, as I would expect.
I know it is the little thing that can be annoying but fixing SSRS Chart sorting is a simple task once you know why it happens. If you have any questions, please feel free to touch base @Garthmj.