Solved

SQL Query in Crystal Report Developer 9.0: Group by feature

Posted on 2003-11-16
10
2,592 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
  • 5
  • 5
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 3

Expert Comment

by:AnnetteHarper
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:AnnetteHarper
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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 100

Expert Comment

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

mlmcc
0
 
LVL 3

Expert Comment

by:AnnetteHarper
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now