I have a report which I am designing in the pentaho ad-hoc web based reporting tool.

The report has several fields and is set to group on the region (in level 1). No other groupings are defined in the report. I have four regions: North, South, East, and West. I expected to see four groups when I run my report. Instead I get the regions repeating like North, South, North, East, North, South, etc....

I turned my debug level in the log4j.xml file up to DEBUG and found the SQL query used to generate the report. It does not use a SQL group by at all. The query has all of the fields from the select clause and the filters defined in the where clause, but no grouping clause.

How does pentaho group elements in a report? Why is my grouping not working? (Note: I initially thought that maybe there were typos in my groups like a 'North' and a 'North ', but I ran a select distinct on my database and verified that there are only four different groups with no spelling errors).