SQL Query in Crystal Report Developer 9.0: Group by feature

Why do you need to see the group by in the SQL statement.

Open the report
Click INSERT --> INSERT GROUP
Insert all the groups you want.

mlmcc

In order for the Crystal generated query to contain a GROUP BY statement, there must be at least one group section in the report, the database or report option for grouping on the server must be checked, and the detail sections of the report must be suppressed. There may be other requirements as well, but these are the basics. In short, you must ensure that no detail level data is required or displayed and then Crystal will generate a GROUP BY clause for the lowest level of grouping in the report.

You could, of course, write the query however you want it in a SQL Command.

SELECT sum(field1), ....
   FROM ...
GROUP BY groupfield1, ...

The detail sections don't need to be suppressed.  I am not sure why sometimes it appears and at others it doesn't appear.  It seems to appear when I group by the primary key unless it is the only group.

mlmcc

mlmcc,

I am suprised that you state that the detail sections don't have to be suppressed. When a SQL query has a GROUP BY clause, no detail data will be returned - no row level data - only summaries at the group level. Hence, if the detail sections are NOT suppressed, Crystal MUST return the detail level rows in order to display them, and cannot use a GROUP BY clause. I believe the detail sections must be suppressed, and this is my experience as well as the logical conclusion.

If you drill down in a report that has detail suppressed and which is backed by a query with a GROUP BY clause, Crystal will generate a second query to return the detail level data for only that group. I have verified this by checking the queries from the Oracle side.

Annette

I just built one in CR7 that didn't need the detail section suppressed.  I also have many reports at work with group by clauses that I use and display details from each record.  I am using CR7 and CR8, is it possibly a new "feature" of CR9?

mlmcc

This is an excerpt from the Crystal Reports Help file.
"GROUP BY
The GROUP BY clause retrieves a set of summary data. Instead of retrieving the data itself, GROUP BY groups the data and summarizes each group with an SQL aggregate function. The server returns only the summarization information for each group to Crystal Reports."

A SQL SELECT statement that contains a GROUP BY clause does not return 'the data itself' meaning the detail level data, so a query containing a GROUP BY cannot be displaying any detail data. It's not a new feature of CR9. It's a property of a GROUP BY query.

I am very interested in how this could be happening for you. I don't mean to belabor a point, but I'd really like to get this figured out.

Let's take an example. If I create a report based on the Scott.EMP table from the Oracle sample database that displays empno, ename, job, and mgr, and group on mgr with a count of empno for the group, and DO NOT suppress the detail, the following query (with no GROUP BY clause) results and Crystal itself is computing the count by manager.
 SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."JOB", "EMP"."MGR"
 FROM   "SCOTT"."EMP" "EMP"
 ORDER BY "EMP"."MGR"

If I then suppress or hide the detail section and grouping on the server is checked, the following query results with a GROUP BY clause.
 SELECT "EMP"."MGR",  COUNT("EMP"."EMPNO")
 FROM   "SCOTT"."EMP" "EMP"
 GROUP BY "EMP"."MGR"
 ORDER BY "EMP"."MGR"

Now with this query, no employee level data has been returned, only the manager and the count for that manager. How could Crystal possibly display say empno when it was not even returned by this query?

Perhaps there is some kind of symantic misunderstanding here or something, because I am mystified as to how any GROUP BY query could be used to display row level data.

What if I wanted to show information on each employee and group them under their manager. I would use something like

 SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."JOB", "EMP"."MGR"
 FROM   "SCOTT"."EMP" "EMP"
 GROUP BY "EMP"."MGR"

This would then allow me to have a group header and footer for the manager's information and the detail section for each employee.  I need no summary data but I do need the details.  I do this in numerous reports.

Is it possible we are talking about 2 different types of GROUP BY clauses since you refer to a GROUP BY query?

mlmcc

It doesn't work that way with Access or MS SQL.

mlmcc

Again, I apologize, but your pronouncements are (rightfully) well respected here and I don't want to even inadvertently be the cause of misinformation.

It does work exactly the same way with Access and SQL Server. It is a standard SQL language sort of thing.

To demonstrate, I created a report against the sample Northwind database installed with SQL Server, using the Employees database, displaying EmployeeID, LastName, FirstName, and ReportsTo, and grouping on Reports to with a summary field containing the count of EmployeeID. The first listing below is the Show SQL that results when the detail section is not hidden or suppressed. The second listing is the Show SQL that results when the detail section is hidden.

 SELECT "Employees"."EmployeeID", "Employees"."LastName", "Employees"."FirstName",
             "Employees"."ReportsTo"
 FROM   "Northwind"."dbo"."Employees" "Employees"
 ORDER BY "Employees"."ReportsTo"

 SELECT "Employees"."ReportsTo",  SUM("Employees"."EmployeeID")
 FROM   "Northwind"."dbo"."Employees" "Employees"
 GROUP BY "Employees"."ReportsTo"
 ORDER BY "Employees"."ReportsTo"

And the following two listings are the similar thing for the Xtreme sample Access database supplied with Crystal Reports.

 SELECT `Employee`.`Employee ID`, `Employee`.`Supervisor ID`, `Employee`.`Last Name`, `Employee`.`First Name`
 FROM   `Employee` `Employee`
 ORDER BY `Employee`.`Supervisor ID`

 SELECT `Employee`.`Supervisor ID`,  SUM(`Employee`.`Employee ID`)
 FROM   `Employee` `Employee`
 GROUP BY `Employee`.`Supervisor ID`
 ORDER BY `Employee`.`Supervisor ID`

As you can see, for both SQL Server and Access, the behavior is the same as it is for Oracle. The GROUP BY is added if the detail sections are suppressed or hidden.

In addition, I tested queries similar to the one that you suggested earlier. The Access query would be like this...

SELECT `Employee`.`Employee ID`, `Employee`.`Supervisor ID`, `Employee`.`Last Name`, `Employee`.`First Name`
 FROM   `Employee` `Employee`
GROUP BY `Employee`.`Supervisor ID`

And results in this error message when used in a SQL Command from Crystal Reports.

"Failed to open a rowset.
 Details 42000:[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Employee ID' as part of an aggregate function."

For SQL Server, the query would be..

 SELECT "Employees"."EmployeeID", "Employees"."LastName", "Employees"."FirstName",
             "Employees"."ReportsTo"
 FROM   "Northwind"."dbo"."Employees" "Employees"
 GROUP BY "Employees"."ReportsTo"

And the resultant error is this.

"Failed to open rowset."
"Query Engine Error:42000[Microsoft][ODBC SQL Server Driver][SQL Server] Column 'Employees.EmployeeID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

When a GROUP BY is used in a SELECT statement, all the expressions in the select list must be either the group by fields themselves or aggregates for any other fields.