Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Suppress records Post Reply Post New Topic
Author Message
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Topic: Suppress records
    Posted: 14 Apr 2015 at 11:07am
CR 10, SQL 2008 db

Report displays things like account name, ticket no, date, part number. How can I setup the report to suppress all records if certain criteria are met? Specifically if the part no matches one of the following - MIS-Sales or Sales-Engineer

Sample data

Acct No    -   Ticket   -   Date   -   Part
1234           123          4-4-15     Widget A
1234           123          4-4-15     Widget B
1234           123          4-4-15     Sales-Engineer

2345           456          4-10-15    Widget A
2345           456          4-10-15    Widget B

In this example I'd like all of acct no 1234 records to be suppressed and all of 2345 to display as it does not match the criteria
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Apr 2015 at 11:14am
create a flag formula for your criteriaif criteria met then 1 else 0
sum this at teh gorup level (per Acct #)
use group select criteria to exclude a whole group
SUM(@flag,group)=0
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 14 Apr 2015 at 11:31am
Sorry but you lost me at create a flag formula...

Do you mean; create a new formula something like
1. If {tblpart.number} startswith 'MIS-Sales' or 'Sales-Engineer' else ""

2. Create a new summary for the formula

3. Lost me with the group select criteria info...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2015 at 4:28am
create a formula that will indicate (or flag) that row because it meets your suppression condition. You will want the flag to be a numeric result
//@flag (or whatever you want to name this formula field)
If {tblpart.number} IN ('MIS-Sales','Sales-Engineer') then 1 else 0
 
If you place this on the detail section you will see a 0 or 1, the 1 only being on a row that you want to use to suppress
 
Create a group on Acct No
insert a SUM of the @flag formula at the Acct No group level
SUM(@flag,table.Acctno)
Now you will see in the group footer that each Acct No group has either a sum of 0 (no suppression rows in the group) or >0 (at least one suppression row in that group).
You now have a group condition to either suppress or exclude the entire group.
To suppress the entire group you would need to add suppression criteria in the GH detail and GF sections.
To exclude the group you can
ooen the select expert
expand it (show formula)
select "group selection" button
add your group condition here
SUM(@flag,table.Acctno)>0
 
This will now exclude every group that has that group sum value >0
A few warnings on this (although not much different than suppressing).
1 -All groups still appear in the group tree.
2- All Summary functions run against all records (they run before the group select, hence the ability to use a group select)
3 - If you need totals/calculations that are excluding the removed groups you can use shared variables with conditions or at printtime
or use Crystal Running Totals (RTs). These execute after the group selection takes place.
 
 


Edited by DBlank - 15 Apr 2015 at 4:32am
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 15 Apr 2015 at 6:41am
Thank you for the help. Everything seemed to work as you described except its not suppressing the matching formula in the select expert, it still displays all records. The matching records from the flag are getting flagged properly 0 or 1 depending on match. The summary also is working properly.

Below is the formula I used in the group selection
Sum ({@FLAG}, {tblAccounts.AccountNumber}) > 0.00
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2015 at 6:47am
there are two types of selection criteria, Record and Group.
If you stuck the selection criteria in the Record portion it would have no impact. It needs to go into the Group.
click on the record selection
click on the show formula button to expand the window
there is a radial button selection option to toggle between either Record or Group selection.
make sure you have nothing in the REcord selection (unless you have other record lvel conditions to your report)
toggle the radial button to the Group option
insert your condition
Sum ({@FLAG}, {tblAccounts.AccountNumber}) > 0.00
select OK
 
 


Edited by DBlank - 15 Apr 2015 at 6:52am
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 15 Apr 2015 at 6:53am
Yep I added it to the group selection option. There were other entries in the record selection but I removed them as well so the only formula is the one you suggested.

Is this a full thought "remove the condition from"
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2015 at 7:01am

"remove the condition from" was a mistake and I edited to remove that.

Go ahead and undo the changes or add the record selection options you needed back in. These evaluate before the group condition and are needed to limit the records to what you want to fall intio the groups.
Next I think you might have reversed the group condition.
This is a select statement not a suppression statement.
This means if the group condition evaluates to TRUE include the group, FALSE exclude the group.
So I think you only want to include groups with a sum=0
change your group condition to
Sum ({@FLAG}, {tblAccounts.AccountNumber}) = 0.00
 
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 15 Apr 2015 at 7:08am
That seemed to do the trick, not sure how I got it backwards but thank you for your help!
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.016 seconds.