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.
我正在编写一个SQL报表服务报告(在VS.Net 2005中)，该报告在一个矩阵中显示不同数据的计数。这些专栏对某一组客户的数量进行计数，所以我有几个专栏，比如“1个雇主”、“2-9个雇主”、“10-19个雇主”等等。
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".
我遇到的问题是SQL报表服务按字母顺序对矩阵中的列进行排序。因此，我在“1个雇主”之后，而在“2-9”之前，写了“10-19”一栏。
Is there any way to fix this? Or maybe a trick to change the labels of the columns to that they sort right?
有办法解决这个问题吗?或者把列的标签改成它们的排序?
Thanks.
谢谢。
7 个解决方案
#1
18
We do a lot of SSRS Reports and this was always an issue with mdx. Here is one way we do it:
我们做了很多SSRS报告这一直是mdx的问题。我们有一个方法:
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.
“zzz”可以是一个数字或者你需要的任何东西来帮助排序，然后根据这个表达式选择升序或降序方向。
#2
17
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.
这也可以通过在查询中添加一个新的列来完成排序。然后从矩阵中右键单击列组并选择组属性。在左边选择排序。从这里，您可以指定另一个要排序的列，而不是列标头。不需要自定义公式。
#3
4
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.
这可能有点复杂，但我们也遇到了类似的问题，无法更改SQL。我们所做的是在数据集中创建一个计算字段，该字段为每个列返回一个数字，并对该数字上的组进行排序。
#4
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.
我遇到过类似的问题，但没有及时找到使用矩阵的解决方案——因此我使用了一个表——并将所需的矩阵逻辑放在我的sql语句中。这不是最好的方法，但它确实有效——它足够快，写起来也不需要那么长时间。
#5
0
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.
我进入了矩阵，>性质，>组。然后，我“移动”了我想要它在列表中排序的组。我去预览了，效果很好。它甚至让我的专栏保持不变。
#6
0
Go to Edit Group - Sorting - Change the direction to descending for the expression.
转到编辑组排序—更改表达式的降序方向。
#7
0
For distinct numeric order of a Tablix use following line of code in expression.
对于不同的数字顺序，Tablix使用下面一行代码表示。
=RunningValue(CountDistinct("YourTableName"),Count,"YourTableName")
= RunningValue(CountDistinct(“就让种子”),统计,“就让种子”)
#1
18
We do a lot of SSRS Reports and this was always an issue with mdx. Here is one way we do it:
我们做了很多SSRS报告这一直是mdx的问题。我们有一个方法:
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.
“zzz”可以是一个数字或者你需要的任何东西来帮助排序，然后根据这个表达式选择升序或降序方向。
#2
17
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.
这也可以通过在查询中添加一个新的列来完成排序。然后从矩阵中右键单击列组并选择组属性。在左边选择排序。从这里，您可以指定另一个要排序的列，而不是列标头。不需要自定义公式。
#3
4
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.
这可能有点复杂，但我们也遇到了类似的问题，无法更改SQL。我们所做的是在数据集中创建一个计算字段，该字段为每个列返回一个数字，并对该数字上的组进行排序。
#4
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.
我遇到过类似的问题，但没有及时找到使用矩阵的解决方案——因此我使用了一个表——并将所需的矩阵逻辑放在我的sql语句中。这不是最好的方法，但它确实有效——它足够快，写起来也不需要那么长时间。
#5
0
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.
我进入了矩阵，>性质，>组。然后，我“移动”了我想要它在列表中排序的组。我去预览了，效果很好。它甚至让我的专栏保持不变。
#6
0
Go to Edit Group - Sorting - Change the direction to descending for the expression.
转到编辑组排序—更改表达式的降序方向。
#7
0
For distinct numeric order of a Tablix use following line of code in expression.
对于不同的数字顺序，Tablix使用下面一行代码表示。
=RunningValue(CountDistinct("YourTableName"),Count,"YourTableName")
= RunningValue(CountDistinct(“就让种子”),统计,“就让种子”)