Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Group Exclusion/Suppression Post Reply Post New Topic
Author Message
sirlansa
Newbie
Newbie
Avatar

Joined: 20 Dec 2006
Location: United States
Online Status: Offline
Posts: 37
Quote sirlansa Replybullet Topic: Group Exclusion/Suppression
     Posted: 05 Dec 2007 at 9:11am
I have a simple report that should show ONLY groups that have at least 2 detail records. In MS Access I would use a HAVING COUNT(*) > 1 clause in the underlying SELECT query.
 
Is there any way I can get the same effect in Crystal, since I can't directly modify the underlying SQL query. I tried using the 'Section Expert' conditional suppression on the Group Header, Detail and Footer, but this seemed to have no effect. My suppress formula is:
       DistinctCount ({table.fldname1}) < 2
 
Any ideas??
Sir Lansa
IP IP Logged
sirlansa
Newbie
Newbie
Avatar

Joined: 20 Dec 2006
Location: United States
Online Status: Offline
Posts: 37
Quote sirlansa Replybullet Posted: 05 Dec 2007 at 12:31pm
I saw the suggestion about using a Command object (with the SQL command embedded in it) to modify the results, so I applied that to the problem.
 
Although it looked like it should work (and the Database Expert treated the Command as a table and automatically linked up the result field of the Command to the other tables in the database, using INNER JOIN), I have seen no change in report results. The groups with only one detail row persist.
 
Surprisingly, the underlying 'Show Database Query' is also unchanged! Has anyone else had success with Command objects? I really thought this was a way to modify the Database Query.
Sir Lansa
IP IP Logged
sirlansa
Newbie
Newbie
Avatar

Joined: 20 Dec 2006
Location: United States
Online Status: Offline
Posts: 37
Quote sirlansa Replybullet Posted: 05 Dec 2007 at 2:27pm
Further information on above:
 
I found that after re-entering the Command Object SQL several times, the exact SQL query I entered did in fact appear in the 'Show SQL Query' result at the bottom of the window and TOTALLY DETACHED from the rest of the SQL above it.
 
When I do a report Preview, after a long time, I get an error message: Not Supported.
 
I recollect that After creating the Command Object in Database Expert, I received a message: "More than one datasource or Stored Proc has been used in this report...", but I ignored it, since the link was present doing a LEFT JOIN of the pre-existing table to the Command Obj, and appeared correct. This may well be my problem -- the link appears to be IGNORED. I'm hoping someone has experience with Command Objects and will have some insight into what to do.
Sir Lansa
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 07 Dec 2007 at 7:19am
I think that simply entering the command, as you noticed, is not actually overwriting the datasource being used.  You probably need to use Database > Set Datasource Location to update your datasource.

If you don't mind bringing the data into the report, and just want to suppress the display, you can do this.  In the Group Footer, put a Count (or DistinctCount) of the records.  In the Section Expert for the Group Header, Group Footer, and any sections between them, suppress the section if the count = 1.  (You don't actually have to put the Count on the report as a field.  I just do it because it makes it easier to enter it into the formula.)
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.