Sorting column chart in Report Builder not working

[Anonymous]

I have a simple column chart I built in report builder, and need to change the sort order of the x axis, but nothing will make the change "stick". The chart shows sales by currency, with sales on the Y axis and the currencies on the left. The chart as designed looks like this:

However, we need the chart to sort by the values in the Y axis, so that it goes from the currency with the highest amount (AUD) left to right to the one with the lowest (EUR), so that these top 20 currencies are in order of their ranking, rather than alphabetical.

The documentation makes it appear that I need to change the sort column in the Category group properties, but whenever I change it, save the dialog box, and then go back in, it reverts back to the original field.

So if I select Category group on the right in design mode:

And then from there select "sort expressions"

And change the current field, which is SalesCCY

To SalesAmt - which is the value being rolled up by currency in the Y axis

It then reverts back to the original field once I click OK and get back out to the main report designer, and go back in:

Am I doing something wrong, or is this a bug? It seems like a very simple thing which I'd think would be a common request.

Thanks!

[JelaniC]

I resolved the issue (in a Table report, not a chart report) by downloading the Reporting Service report .rdl file and using a text editor (notepad) to find the Group Expressions.  In this image I changed the field in the SortExpressions node to be the field in my dataset I wanted to sort on.  Saved my changes, uploaded the .rdl file back into the Report Builder to continue editing, and never had the sorting issue again. 

[SalamRaed]

You will need to sort the row groups, if you have more than one then you will need to sort all of them based on the category you like:

You will click on each group then select group properties and add the sort field or change it, you will find that the reason why sorting from tablix is not working is because the higher group in row groups hierarchy is sorted on a different group which override your sorting, this happen when you work on a copied list or chart that sorted differently, hope that will help.

[lrobbins7663]

I agree that the Sort mechanism does not work.  I do not believe that anyone has answered this question.  Any ideas?

[v-alq-msft]

Hi, @Anonymous 

You may refer the following thread to see if it helps.

How do you custom sort column in a paginated report 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft Thank you for the suggestion, but the dataset has always been sorted in the order I need it. However, it appears that the sorting applied within the chart is overriding that sort, as shown below. The chart alwayts ends up being sorted by SalesCCY alphabetically no matter what I do. If I delete the row below and leave it blank, or add the value I really need, it comes back after I click ok and return to it. That seems to be the issue, and is why it seems like a bug. Any idea what could be causing that to happen, other than a bug?

thanks!

Alex