Solved

SQL Query in Crystal Report Developer 9.0: Group by feature

Posted on 2003-11-16
10
2,610 Views
Last Modified: 2010-08-05
Hi to all,

How to add in group by feature in SQL query of Crystal Developer 9.0.

I using a Oracle 9.0 database accessing two inner joined tables.
I tried the sql command in Database Expert feature, but it's not what I required.
I need to see the group by in SQL statement in SQL Query feature.
Could anyone will kindly help me please?
Thank anyone in advance.
 
0
Comment
Question by:Luo_Zha_Zong_Zu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 9763396
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
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9763457
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, ...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 9771081
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
0
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9773339
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
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 9775687
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
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9778628
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 9784388
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
0
 
LVL 3

Accepted Solution

by:
AnnetteHarper earned 85 total points
ID: 9787019
The query that you display is not valid and would result in an error. Just to prove this, I ran it from SQL*Plus as shown below.

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Nov 20 07:37:48 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."JOB", "EMP"."MGR"
  2   FROM   "SCOTT"."EMP" "EMP"
  3   GROUP BY "EMP"."MGR"
  4  /
SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."JOB", "EMP"."MGR"
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

I also tried to use it in a SQL Command in Crystal reports which results in the following error message.
Query Engine Error: ORA-00979: not a GROUP BY expression

If the query contains a GROUP BY clause, then every item in the select list MUST be an aggregate such as SUM or COUNT which is what the message means by "a GROUP BY expression". This is standard ANSI SQL and will be true in any SQL database, not just Oracle.

However, that is not to say that you can't create a non-GROUP BY query and then have Crystal create any groups you want. In the situation that you mention, the underlying query will simply not have the GROUP BY clause added to it and will then return all detail level rows. The summarization will be done on the front-end by Crystal reports. Just look at the Show SQL in Crystal and I'm sure that you will see that there is no GROUP BY added to the query.

A GROUP BY query is only useful if you have no desire to see the employee level detail, but only summaries. In this case, getting Crystal to generate a GROUP BY is an optimization tactic since much less data needs to be returned, the summarization is done on the server, and therefore the report will run faster. It is even good if you initially hide the detail but expect your users to drill down on a limited number of the groups. In this case, Crystal will generate a second query to get the detail for only the group that has been drilled into (Again check the Show SQL). If you expect your users to drill into a large number of the groups, then just fetching all the detail initially is better.

In summary, adding a group to a Crystal report DOES NOT add a GROUP BY clause to the SELECT query that Crystal generates unless grouping on the server is on and the detail is hidden or suppressed. This is easy to verify by looking at the Show SQL. Crystal computes the aggregates in any report groups and does not rely on getting them from the server. There is no correspondence between groups in the report and GROUP BY clauses in the database query if the detail sections are displayed.

Even in the situation where the generated query does have a GROUP BY (detail is hidden), the database query can only return information at the lowest level of non-hidden report group. So if there was a grouping above manager for instance, Crystal would have to compute the group fields for the higher level groups.

I'm sorry if I've been overly ademate about this, but I think it is important to understand. It could be that you are seeing an ORDER BY being added to your queries, but that is a completely different thing than a GROUP BY. Crystal will always add an ORDER BY for any groups that are created in the report, but that only sorts the detail rows it does not aggregate them.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 9792740
It doesn't work that way with Access or MS SQL.

mlmcc
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9796840
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question