Does RS use ORDER BY for grouping?

It seems like you don't need to specify SQL's ORDER BY to match Page and Group breaks in RS. I.e., RS does
this handling by itself, with the added work and memory requirements to do it.
My question is whether RS will understand that I do have ORDER BY in the query that matches my Page and Group
breaks, and just read off the input stream without doing the sorting etc by itself. If it doesn't, I wouldn't
want to burden the DBMS with the sorting as it would be redundant...
I tried a report, which was indeed alphabetically grouped by RS without a SQL ORDER BY. Even when adding ORDER
BY NEWID() (which returns rows in a different "random ordering for each execution), the report was still
grouped alphabetically.
(I am not referring to the ordering of the details info, I understand that ORDER BY is needed for that.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/You don't ever need to use ORDER BY in your query, even to order the
details. RS supports many different types of data sources (and you can
easily add your own by writing a custom data processing extension), not just
SQL, and some these data sources may not have the ability to sort. Also, RS
does not examine your query and cannot detect that you have an ORDER BY.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPN1N6ifEHA.1644@.tk2msftngp13.phx.gbl...
> It seems like you don't need to specify SQL's ORDER BY to match Page and
> Group breaks in RS. I.e., RS does
> this handling by itself, with the added work and memory requirements to do
> it.
> My question is whether RS will understand that I do have ORDER BY in the
> query that matches my Page and Group
> breaks, and just read off the input stream without doing the sorting etc
> by itself. If it doesn't, I wouldn't
> want to burden the DBMS with the sorting as it would be redundant...
> I tried a report, which was indeed alphabetically grouped by RS without a
> SQL ORDER BY. Even when adding ORDER
> BY NEWID() (which returns rows in a different "random ordering for each
> execution), the report was still
> grouped alphabetically.
> (I am not referring to the ordering of the details info, I understand that
> ORDER BY is needed for that.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>

does grouping on a field imply sorting by that field?

If I group on a field, will the report be sorted by that field as well?
I have a table whose parent is the report body. I'm grouping the
table on a string field that contains values like "SC2", "SC4", "SC5",
and "SC6". These values are all 3 characters long (i.e., no white
space) and all uppercase. When I preview the report, the groups appear
in this order: "SC2", "SC6", "SC5", and "SC4" - which is the order
these values appear in the grid on the Data tab - so it appears that no
sort is being applied to the dataset (which, BTW, is based on a stored
procedure). I have tried sorting by this field (in addition to
grouping by it) but that does not change the order displayed in the
report. Any help would be appreciated.Where are you applying the sort? You need to sort the groups, not the
details (or dataset). In the Group Properties dialog, select he sort tab and
you should get what you want.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <leemsrs@.hotmail.com> wrote in message
news:1114273595.800769.209160@.l41g2000cwc.googlegroups.com...
> If I group on a field, will the report be sorted by that field as well?
> I have a table whose parent is the report body. I'm grouping the
> table on a string field that contains values like "SC2", "SC4", "SC5",
> and "SC6". These values are all 3 characters long (i.e., no white
> space) and all uppercase. When I preview the report, the groups appear
> in this order: "SC2", "SC6", "SC5", and "SC4" - which is the order
> these values appear in the grid on the Data tab - so it appears that no
> sort is being applied to the dataset (which, BTW, is based on a stored
> procedure). I have tried sorting by this field (in addition to
> grouping by it) but that does not change the order displayed in the
> report. Any help would be appreciated.
>|||Knowing where to apply the sort is the confusing part for me. There is
a Sorting tab on the Table Properties dialog (my report consists of a
single table whose parent is the report body). There is also a Sorting
tab on the Grouping and Sorting Properties dialog (which can be
accessed for each group). What are these two different Sorting tabs
used for? Tell me if I got it right in the following example: the
report my users want should be grouped by Field A, and then within each
Field A group it should be grouped by Field B. Finally, within each
Field B group it should be sorted (not grouped) by Field C. On the
Grouping and Sorting Properties dialog for the Field A group, I went to
the Sorting tab and chose Fields!FieldA.value. Next, on the Grouping
and Sorting Properties dialog for the Field B group, I went to the
Sorting tab and chose Fields!FieldB.value. Finally, on the Table
Properties dialog, I went to the Sorting tab and chose
Fields!FieldC.value as the only sort field. This seems to produce the
grouping and sorting that my users want, but because I find the process
counter-intuitive (my 4 years of previous reporting experience have
been with Cognos Impromptu), I'm wondering if what I did is correct.
Can you tell me?|||You are correct. The sorting on the grouping dialog is used to sort the
groups themselves. For example, you might group by city but then sort the
cities by the population. The table sort is used to sort the details. Since
the sort key might be composite, we have to support multiple fields in the
dialog.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <leemsrs@.hotmail.com> wrote in message
news:1114441358.314151.237870@.f14g2000cwb.googlegroups.com...
> Knowing where to apply the sort is the confusing part for me. There is
> a Sorting tab on the Table Properties dialog (my report consists of a
> single table whose parent is the report body). There is also a Sorting
> tab on the Grouping and Sorting Properties dialog (which can be
> accessed for each group). What are these two different Sorting tabs
> used for? Tell me if I got it right in the following example: the
> report my users want should be grouped by Field A, and then within each
> Field A group it should be grouped by Field B. Finally, within each
> Field B group it should be sorted (not grouped) by Field C. On the
> Grouping and Sorting Properties dialog for the Field A group, I went to
> the Sorting tab and chose Fields!FieldA.value. Next, on the Grouping
> and Sorting Properties dialog for the Field B group, I went to the
> Sorting tab and chose Fields!FieldB.value. Finally, on the Table
> Properties dialog, I went to the Sorting tab and chose
> Fields!FieldC.value as the only sort field. This seems to produce the
> grouping and sorting that my users want, but because I find the process
> counter-intuitive (my 4 years of previous reporting experience have
> been with Cognos Impromptu), I'm wondering if what I did is correct.
> Can you tell me?
>|||In your example, if we grouped by city but didn't sort by it, what
would happen? I would hope that whatever city was encountered first in
the dataset would appear first in the report, and all rows for that
city (no matter where they were scattered throughout the dataset) would
appear together on the report (i.e., no other cities in between them).
For example, consider a dataset with 10 rows. If there were 5 rows for
Boston at the beginning of the dataset, followed by 3 rows for Atlanta,
followed by another 2 rows for Boston, then the report would show the
Boston group first with all 7 rows followed by the Atlanta group with 3
rows. Is this correct?