Group Value not recognized - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Group Value not recognized

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-02-03 : 18:22:12

I have a SQL query that is grouping correctly. Example:

location truck#
dallas 100
dallas 110
dallas 125
houston 150
houston 160

When this is brought in to SSRS and run without any group yet defined it looks like above. When I insert a group on value 'location' it breaks up as follows (this is the only group in the report):

dallas 100
dallas 110

houston 150
houston 160

dallas 125

It is the same data type and the value is the same. Why would SSRS break up this group like that? Any help would be appreciated. Thanks.

Notoook
Starting Member

18 Posts

Posted - 2009-02-04 : 03:37:55

Try to group on Truck #.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 08:47:54

can you post your group expression? have you included any other columns?

osupratt
Posting Yak Master

238 Posts

Posted - 2009-02-04 : 09:30:30

Ok. I checked my SQL query by doing a row_number() over partition, etc. and it came out correct.

dallas 1
dallas 2
dallas 3
houston 1
houston 2

I then took out all columns except the location. I then pulled this into a test report in SSRS. Grouped on Location (only column) with page break at end. I have three pages.

Page 1
dallas
dallas

Page 2
dallas

Page 3
houston
houston

I don't know what is going on?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:45:47

can you just run this query and see what it returns from query mgmnt studio?


SELECT location
FROM Table
GROUP BY location

osupratt
Posting Yak Master

238 Posts

Posted - 2009-02-04 : 12:12:22

here is what i get:

HENDERSON
ALEDO
GAINESVILLE
MINDEN
TOLAR
JACKSBORO
CLEBURNE
STONY

it groups correctly. when put in the report side of things the location 'HENDERSON' splits. all other locations are fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:19:25

in report are you taking location from table directly or are you using some conversion or some other ways to derive the field value?

osupratt
Posting Yak Master

238 Posts

Posted - 2009-02-04 : 16:26:12

from the table directly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:44:55

are you joining to any other table in report dataset query?