How to specify an order for the columns in a matrix?

Question

I'm working on a SQL Reporting Services report (in VS.Net 2005) which displays a count of different data in a matrix. The columns have a count on the amount of customers in a certain set. So I have several columns like these: "1 employer", "2-9 employers", "10-19 employers" and so on.

The problem I have is that SQL Reporting Services sorts the columns in the matrix alphabetically. So I end up having the "10-19" column after the "1 employer" column but before the "2-9".

Is there any way to fix this? Or maybe a trick to change the labels of the columns to that they sort right?

Thanks.

Rat

Thanks for asking this question a few months before I needed to ask the very same question! – Joejoeann

Answer 1

We do a lot of SSRS Reports and this was always an issue with mdx. Here is one way we do it:

Set Sorting in the Grouping and Sorting Properties to sort by this expression:

=iif(Fields!DataSetField.Value = "ColumnName", "zzz", Fields!DataSetField.Value)

Where "zzz" could be a number or whatever you need it to be to help the sort and then select Direction as either Ascending or Descending base on this expression.

Compilation

Answer 2

This can also be done by adding a new column to the query for the sort order. Then right click the column group from the matrix and select group properties. On the left hand side select sorting. From here you can specify another column to sort by instead of the column header. No custom formula needed.

Whiggism

This is the best solution. I wish I could accept it so it would go to the top of the list here. – Metonym

This is the best useful solution. – Myriapod

For this purpose, you can use this SQL to get the new column : SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS SLNO – Wideangle

Answer 3

This may be a bit convoluted, but we had a similar problem and no means to change the SQL. What we did was create a calculated field in the dataset that returns a number for each column and sorted the group on that number.

Mcglone

Answer 4

I had a similar problem but did not find a solution using a matrix in time - so I used a table - and put the needed logic of the matrix in my SQL-statements. It's not the best thing to do but it works - it's fast enough and it don't takes that long to write.

Sivie

Answer 5

I went into MATRIX --> PROPERTIES --> GROUPS. Then, I "moved" the GROUP I wanted it to sort by UP in the list. I went to PREVIEW, and it worked perfectly. It even kept my column order the same.

Adina

Why do you think this will solve the issue of the poster? His problem was that sorting works alphabetically. – Maraca

Answer 6

Go to Edit Group - Sorting - Change the direction to descending for the expression.

Golgotha

Why do you think this will solve the issue of the poster? His problem was that sorting works alphabetically. – Maraca

Answer 7

For distinct numeric order of a Tablix use following line of code in expression.

=RunningValue(CountDistinct("YourTableName"),Count,"YourTableName")

Inaccessible

Answer 8

Simply Go to tablix/Matrix Column Group and Right Click on the Group Properties then go Sorting and Delete the auto added record from Sorting.. Now your sorting will based on the Data you have added in Data set.

Bergschrund