does grouping on a field imply sorting by that field? -- sql server reporting services

sql server reporting services : does grouping on a field imply sorting by that field?

Lee
4/23/2005 9:26:35 AM

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.

Brian Welcker
4/23/2005 10:13:34 AM

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.

[quoted text, click to view]

Lee
4/25/2005 8:02:38 AM

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?

Brian Welcker [MSFT]
4/26/2005 11:35:00 PM

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.

[quoted text, click to view]

Lee
4/27/2005 6:44:19 AM

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?

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn