I created a dataset with a SQL query in Report Builder 3.0. I did NOT specify an order by in the query, but instead used union all to stack the results in the order I wanted. The first column in this dataset is called Product.
我在Report Builder 3.0中使用SQL查询创建了一个数据集。我没有在查询中指定顺序,而是使用union all按照我想要的顺序堆叠结果。此数据集中的第一列称为Product。
When I create a table in Report Builder, referencing the Product column, it for whatever reason wants to default to alphabetical sorting of this column - which I do not want.
当我在报表生成器中创建一个表,引用“产品”列时,无论出于何种原因,它都希望默认按字母顺序对此列进行排序 - 这是我不想要的。
What can I do?? Report Builder 3.0 seems pretty primitive, so if there isn't anything I can do about this annoyance than so be it.
我能做什么?? Report Builder 3.0看起来很原始,所以如果没有什么我可以做的就是这个烦恼而不是它。
3 个解决方案
#1
1
@Catcall is absolutely right regarding the resultset being 'undefined'. However I think the reason you are seeing the results sorted by the first column is probably because you've used the Report Builder wizard to create the output table. This tends to create a bunch of nested row groups and is probably grouping first on product and thus sorting on product.
@Catcall对于结果集是“未定义”是绝对正确的。但是,我认为您看到按第一列排序的结果的原因可能是因为您已使用报表生成器向导来创建输出表。这往往会创建一堆嵌套的行组,并且可能首先在产品上进行分组,从而对产品进行排序。
If you create a new table and leave the row as 'details' rows (it will show as an equals sign rather than a bracket / brace) then generally Report Builder will output in the order returned by the query.
如果您创建一个新表并将该行保留为“详细信息”行(它将显示为等号而不是括号/括号),则通常报表生成器将按查询返回的顺序输出。
HOWEVER, it is best practice to explicitly define the sortorder, I would use RANK() or similar within each query to give you a numeric SortOrder column. Then use this within Report Builder as the Sort expression.
但是,最佳做法是明确定义排序顺序,我会在每个查询中使用RANK()或类似的方法来为您提供数字SortOrder列。然后在报表生成器中将其用作排序表达式。
#2
1
Try to remove the sorting options in Row Groups. (Group properties / Sorting).
尝试删除行组中的排序选项。 (组属性/排序)。
Next, add a new sorting in the tablix properties. https://msdn.microsoft.com/en-us/library/dd255193.aspx
接下来,在tablix属性中添加新排序。 https://msdn.microsoft.com/en-us/library/dd255193.aspx
#3
0
I did NOT specify an order by in the query, but instead used union all to stack the results in the order I wanted.
我没有在查询中指定顺序,而是使用union all按照我想要的顺序堆叠结果。
In the absence of an explicit ORDER BY clause, the order rows will be returned is indeterminate. It can--and often does--change from one run to another as the execution environment changes. If you don't specify an ORDER BY, the query optimizer is free to return rows in the order it decides is most efficient. You can't rely on it to make the same choices every time.
在没有显式ORDER BY子句的情况下,将返回的订单行是不确定的。随着执行环境的变化,它可以 - 并且经常会 - 从一次运行变为另一次运行。如果未指定ORDER BY,则查询优化器可以按其决定的最有效的顺序*返回行。你不能每次都依赖它来做出相同的选择。
Perhaps Report Builder will honor an explicit ORDER BY in the underlying query.
也许Report Builder将在基础查询中遵循显式ORDER BY。
#1
1
@Catcall is absolutely right regarding the resultset being 'undefined'. However I think the reason you are seeing the results sorted by the first column is probably because you've used the Report Builder wizard to create the output table. This tends to create a bunch of nested row groups and is probably grouping first on product and thus sorting on product.
@Catcall对于结果集是“未定义”是绝对正确的。但是,我认为您看到按第一列排序的结果的原因可能是因为您已使用报表生成器向导来创建输出表。这往往会创建一堆嵌套的行组,并且可能首先在产品上进行分组,从而对产品进行排序。
If you create a new table and leave the row as 'details' rows (it will show as an equals sign rather than a bracket / brace) then generally Report Builder will output in the order returned by the query.
如果您创建一个新表并将该行保留为“详细信息”行(它将显示为等号而不是括号/括号),则通常报表生成器将按查询返回的顺序输出。
HOWEVER, it is best practice to explicitly define the sortorder, I would use RANK() or similar within each query to give you a numeric SortOrder column. Then use this within Report Builder as the Sort expression.
但是,最佳做法是明确定义排序顺序,我会在每个查询中使用RANK()或类似的方法来为您提供数字SortOrder列。然后在报表生成器中将其用作排序表达式。
#2
1
Try to remove the sorting options in Row Groups. (Group properties / Sorting).
尝试删除行组中的排序选项。 (组属性/排序)。
Next, add a new sorting in the tablix properties. https://msdn.microsoft.com/en-us/library/dd255193.aspx
接下来,在tablix属性中添加新排序。 https://msdn.microsoft.com/en-us/library/dd255193.aspx
#3
0
I did NOT specify an order by in the query, but instead used union all to stack the results in the order I wanted.
我没有在查询中指定顺序,而是使用union all按照我想要的顺序堆叠结果。
In the absence of an explicit ORDER BY clause, the order rows will be returned is indeterminate. It can--and often does--change from one run to another as the execution environment changes. If you don't specify an ORDER BY, the query optimizer is free to return rows in the order it decides is most efficient. You can't rely on it to make the same choices every time.
在没有显式ORDER BY子句的情况下,将返回的订单行是不确定的。随着执行环境的变化,它可以 - 并且经常会 - 从一次运行变为另一次运行。如果未指定ORDER BY,则查询优化器可以按其决定的最有效的顺序*返回行。你不能每次都依赖它来做出相同的选择。
Perhaps Report Builder will honor an explicit ORDER BY in the underlying query.
也许Report Builder将在基础查询中遵循显式ORDER BY。