How can I limit Records in a Group
Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9936
Printed Date: 18 May 2024 at 11:47am
Topic: How can I limit Records in a Group
Posted By: awear01
Subject: How can I limit Records in a Group
Date 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  Cheers ------------- Andy |
Replies:
Posted By: lockwelle
Date 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
|
Posted By: awear01
Date Posted: 07 May 2010 at 5:15am
That worked great, thanks (and very useful for future reference )...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 |
Posted By: lockwelle
Date 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
|
Posted By: awear01
Date Posted: 07 May 2010 at 5:54am
|
Understood, thanks for your help... ------------- Andy |