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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Hope this helps some.

Answer 4

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.