Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Supress Detail Records Based on Grouping Post Reply Post New Topic
Author Message
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Topic: Supress Detail Records Based on Grouping
    Posted: 28 Apr 2021 at 3:38am
Have a report where I have grouped the report based on a transaction number.

So each transaction number could be 1 line, 5 lines, 10 lines whatever.

For each detail line in the transaction I have created a formula based on another field to return either a '1' or a '0'.

The majority of the detail lines in the groups will return a zero and hence the group total is zero. Some of the groups will return a number greater than zero since some of the detail lines in that group return a 1. For those groupings that return a number greater than zero I want to throw away all detail lines even though the detail lines within that grouping could contain 0's or 1's.

For example:

Trans #       Return Value

12345        0
12345        0
12345        1

Group Total = 1

In the above example I want to throw away all three detail lines since one of them returns a value greater than zero.

Any suggestions?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 28 Apr 2021 at 6:03am
looking at it another way, you only want to keep groups where all the lines are 1, right?

You could compare your sum to the count of lines for the group, and suppress based on that.

Am I on the right track?
HTH
IP IP Logged
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Posted: 28 Apr 2021 at 6:18am
Thanks lockwelle for your reply. No just the opposite. I only want to keep the 0's. So if any line in any group contains a '1' I want to throw them all out even if the other lines are zero.

So in my case all lines have to be '0' otherwise I don't want those group of lines.

Hope this makes sense.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 28 Apr 2021 at 11:33am
seems simple, then, suppress all rows where the sum > 0...

your function works on a row by row. I hate doing this, as it can drag down a report, and the first solution that comes to mind is to create a subreport. The purpose of the subreport is to go through each row in the group, determine the value and sum it up. The subreport would set a shared variable, and the main report can use that to suppress the grouping. Place the subreport in the group header of the section to suppress, probably as GHa. Then GHb can use it to suppress itself.

I hope that this makes sense.

In a nutshell, have a subreport (or some other method) sum up the 1's and 0's, and only display the rows if the sum = 0.
IP IP Logged
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Posted: 28 Apr 2021 at 3:04pm
Thanks lockwelle will give that a shot didn't think of going that route.

Will keep you posted.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Apr 2021 at 11:43am
also depending on the way you are getting your 0/1 value you could suppress the details based on a group sum.
in the detail row use a conditional suppress formula
Sum ({@Formula}, {Table.transactionNumber})>0
IP IP Logged
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Posted: 02 May 2021 at 10:00am
Thanks DBlank and lockwelle for your input. I must admit I feel like an idiot since the solution was staring me straight in the face the whole time.

I created a formula as mentioned to add a number to the groups and if > than 0 I wanted it eliminated.

So I "simply" used the Selection Formulas but instead of using the "Record" option used the "Group" option instead and indicated I only wanted Groups that equaled zero and problem solved.

Thanks again for your input.
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.