Group By of SQL Varchar Field Yielding Unexpected Results

[Auguy]

I have an SQL table with a State field (Char(2)) and a City field (Varchar(20)). In my report, when I do a Group By on State and City I am getting two (or more) rows for some state and city combos because some of the city fields have trailing spaces in the SQL table. If I run the same grouping in SQL, I only get one row. The data source for the report is an SQL store procedure. Is there a way around this?

Auguy
Northwest Ohio

 

[Auguy]

OK, I got it to work by adding an Rtrim to the City in the group by definition. Still seems strange that the Reporting Services grouped differently than in SQL Analyzer. I know SQL ignores the trailing spaces. I am looking into changing the table to trim trailing blanks for the varchar fields. Any other input would be welcome.

Auguy
Northwest Ohio