How to first sort by color & then in alphabetical order in ssrs

sortingssrs-2016

I'm still new to ssrs. In my query the color is determined by location. In my query I did
ORDER BY [Location], Underwriter. Now this works perfectly fine in my query, but when I run it in SSRS it doesn't sort like in the query. I get this

what I would like to do is first group the colors together and & set the underwriters in alphabetical order i.e. Balluka, Bill(light blue)
Matanane, Brian(green)
Walsh, Daniel(green)
Davidson, Christine (pink)
Hamilton, Marianne (pink)
Karger, Tierney (pink)
Marroquin, Melanie (pink)
Sylvester, Todd (pink)
Hopp, Robert (white)
Lohr, Jeff (white)
Raskovic, Hanna (white)

Now what I tried doing in ssrs was in the design I right clicked underwriter> interactive sorting: tried sorting by underwriter & location but nothing happens when I run it. Please help

Best Answer

The order by clause in your SQL query should have worked.

Since it isn't working that means that something else is interfering with the sort order.

The next place to look is in your Tablix Properties under sorting.

Then look at any Group Properties. Make sure you check each row.

If you still can't find it, right click on the report in Visual studio and view code. You can then Ctr-F and search for "Sort" and find any sort expressions in the .rdl.

If you find an object you want to look at, you can then use search properties to select the object by name.

Related Solutions

Mysql – How to tell if an index is being used to sort in MySQL

You should append EXPLAIN EXTENDED before query and see result yourself.

It should have an entry for

possible_keys

If this column is NULL, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing.

and

Keys

The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

For more information you can refer this Explain output and Explain join_types

Mysql – ORDER BY on aggregate field on a closure table giving unexpected results

Your sortpath isn't technically being "changed" by the ORDER BY. The correct values are all there, just not coming up in the order you anticipated. In SQL, if you don't explicitly sort a set of data, the order in which it comes back to you is undefined... and the fact that it originally appeared to be in the anticipated order was more along the lines of coincidental.

I tweaked your fiddle (the "sorted" query):

GROUP_CONCAT(s.SortOrder ORDER BY a.PathLength DESC separator '') AS SortPath

Now, it looks like the results are being explicitly sorted in the order you expect, which is the order in which they were implicitly sorted before.

So why did that order change? If you do an EXPLAIN SELECT on the "sorted" query with and without the ORDER BY clause, you'll notice that the ordering of the tables changes in the output of EXPLAIN depending on whether you specify ORDER BY on the entire query. This is because the query optimizer re-orders the join-order of the tables to what it thinks is the most cost-effective sequence.

With the ORDER BY, the tables are joined from left to right, n-d-a-s. Without the ORDER BY, the tables are joined from left to right, d-n-a-s. Both are logically valid, but this causes the rows to be read from the tables in a different order, and your unsorted GROUP CONCAT's output order changes as a result of the change to the query plan.

Best Answer

Related Solutions

Mysql – How to tell if an index is being used to sort in MySQL

Mysql – ORDER BY on aggregate field on a closure table giving unexpected results

Related Question