Report Design
 Crystal Reports Forum : Crystal Reports .NET 2003 : Report Design
Message Icon Topic: How can I limit Records in a Group Post Reply Post New Topic
Author Message
awear01
Newbie
Newbie
Avatar

Joined: 09 Nov 2009
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote awear01 Replybullet Topic: How can I limit Records in a Group
    Posted: 07 May 2010 at 12:15am
I thought this was easy when I was first asked to do it...

I have a group with mutliple records but I only want to return the last 4 records of each group. Does anyone know a way of doing this?

It's driving me mad Angry

Cheers
Andy
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 07 May 2010 at 3:37am
well...if I was using a stored proc, I work to limit there (probably much simpler) otherwise I would try something like:
 
in the group header set a variable to the number of items in the group and set a counter to 0:
shared numbervar tCount:=count({table.field}, group);
shared numbevar tIndex:=0;
""//to hide the formula
 
in the section expert for the detail for suppression I would have:
shared numbervar tCount;
shared numbervar tIndex := tIndex + 1;
tCount - tIndex > 3;
 
 
Why 3? if you have 5 items, the first time through, 5-1=4, but you don't want all the records, the next time 5-2=3
 
HTH
IP IP Logged
awear01
Newbie
Newbie
Avatar

Joined: 09 Nov 2009
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote awear01 Replybullet Posted: 07 May 2010 at 5:15am
That worked great, thanks (and very useful for future referenceBig%20smile)...but unfortuately I still have a problem with the data. The results are only suppressed from view. I would like to remove the suppressed records from the calculations. Is that possible?

Basically I want to show the last 4 records of each group if the value of one field falls between a set range for all 4 records.

Example:-

Range between 2.0 and 3.0

Group Name 1

Name          Value
Andy           2.1       ----|
Peter          2.3             |____ Show
John           2.5             |
Dave          2.3        ----|

Group Name 2

Name          Value
Andrew       1.8      -----|
Carl            2.3            |____ Dont Show
Paul            2.5            |
Robert         2.3     -----|

I hope that makes sense.

Cheers
Andy
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 07 May 2010 at 5:44am
for the calculations you would either need to code runningTotals (which I don't understand, they are DBlank's forte) or use the formula to create your own running totals via shared variables.
 
Unfortunately, unless you resort to subreports, you won't be able to do the next step of suppression as CR doesn't know the values of the last 4 records until it 'reads' them (ie prints them on the report).  The furthest ahead the CR can read is 1 record.  A subreport would be able to set a shared variable that the main report could read, but it would basically mean running your report twice (actually more as there would be more hits to the database, but you get the idea, it will be slower).
 
The subreport could set the variable because CR reads all the records for the subreport and then returns to the main report (to in this case, do it all again), but you would be able to conditionally suppress the groups rows based on the results from the subreport.
 
HTH
IP IP Logged
awear01
Newbie
Newbie
Avatar

Joined: 09 Nov 2009
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote awear01 Replybullet Posted: 07 May 2010 at 5:54am
Understood, thanks for your help...


Andy
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.031 seconds.