1

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.

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.

Nate
  • 30,286
  • 23
  • 113
  • 184
Ray
  • 3,137
  • 8
  • 32
  • 59

3 Answers3

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

arpalhares
  • 11
  • 2
  • 1
    When providing an answer that is mostly from a link, please include the relevant text from the linked page in order to add value to your answer in case the link is not valid at some time in the future. – dub stylee Apr 07 '15 at 21:48
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.

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.

Joel Mansford
  • 1,306
  • 7
  • 13
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.

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.

Perhaps Report Builder will honor an explicit ORDER BY in the underlying query.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185