Question about "Group By" 2

[MrBillSC]

CR Version 8.5
SQL database
For performance reasons, I have been checking the "Group on Server" option for reports that only summarize data at the group level. When I show the SQL query, the "Group By" statement is never there.
I have tried starting with a skeleton report of two tables - Vendor and PurchOrders, linked by VendId. When I add the group "VendorCode" and check the SQL query, I see the "Group By Vendor."VendorCode" statement.
As soon as I add any fields to the report, even if it is just a running total field, the "Group By" statement disappears from the SQL query.
Can anyone shed some light on how you get the grouping to be done on the server?

Thanks in advance,
MrBill

 

[synapsevampire]

I'll guess that SQL database means MS SQL Server.

It works similarly to a SQL statement.

Any column in the select must either have a group by clause or have an aggregate.

If you have the following layout:

gh1 Customer
details: value
gf1 RT of Customer

You have several problems:

A Running Total is computed within Crystal, so use conventional summaries, not RT's. If you right click the value field in the details and select insert summary below and state that it's for group1, then suppress the details, you will get a summary in the group footer and appropriate sql. This is the way sql works as well.

If you are trying to display values which are either not grouped, nor are they aggregated fields, it is not a proper group by sql statement.

Hope this helps to describe how Crystal works, and how sql itself works.

-k

 

[MrBillSC]

Thank you synapsevampire.
You got me on the right track. I have tested the various summary options for text and numeric fields. Most of them cause the "Group by" clause to disappear from the SQL query.
If anyone else is interested, the summary options that do not cause the "Group by" clause to disappear from the SQL query are:
maximum
minimum
count
sum
All of the others summary options cause the "Group by" clause to disappear from the SQL query.
MrBill

 

[nitroedge]

You need to enable drill down on a section to really benefit from doing grouping on server.

What this means is when certain conditions exist, the system will break the query into multiple parts, and only send a query to the db for the fields in the currently visible groups. When you drilldown on a group, another query is issued for only the data at that next level.

The help explains this in more detail - but in short - you need to suppress at least the details section with drilldown enabled to benefit from this.

Thanks, Blair