Crystal Reports Top 5 and Bottom 5 separated by a date in 1 report

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Top 5 and Bottom 5 separated by a date in 1 report

Author	Message
carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Topic: Top 5 and Bottom 5 separated by a date in 1 report Posted: 30 Jan 2012 at 9:00am
	CR2008 I have a parameter ?SeparationDate I have grouped on Table1.ID I want to return the Top 5 Table1.ID where Table1.create_date is <= ?SeparationDate and Bottom 5 Table1.ID where Table1.create_date is > ?SeparationDate I can do this using a Main Report for Top 5 and a subreport for Bottom 5 but can it be done in a single report? BTW, when I add a SQL command to any crystal report it slows down refreshing to a point where it’s not worth using. Edited by carstowal - 30 Jan 2012 at 9:00am
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 30 Jan 2012 at 9:29am
	I think you would return all rows, just use a group sort (not sure what you are defining as a "top 5") and conditionally suppress the groups / sections using a Running Total vs a distinct count of the grouped on field. {#RTotal0} in 6 to (distinctcount({table.field})-6)
	IP Logged

carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Posted: 30 Jan 2012 at 9:34am
	If the SeparationDate is June 30 2010 then the the Top 5 are the last 5 Table1.ID with a Table1.create_date prior to 6/30/10 and the Bottom 5 are the first 5 Table1.ID with a Table1.create_date after 6/30/10 the 5 entries just prior to the SeparationDate and the 5 entries just after the SeparationDate
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 30 Jan 2012 at 10:30am
	i think it would be easiest to talk the customer into the another definition of top and bottom 5. Assuming that does not work... Keep your running total to count all rows sort by create_date create a formula with nothing in it called NULL. create another formula called "BeforeCount" if create_date<?seperation date then totext(createdate) else @NULL in the section expert conditionally suppress the details section as NOT({#RTotal0} in count({@BeforeCount})-4 to count({@BeforeCount})+5)
	IP Logged

carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Posted: 01 Feb 2012 at 4:06am
	I have tried modifying your formula any number of ways with no luck. My {#RTotal0} must count Distinct Table1.ID not all rows. The Top 5 are 5 Table1.ID(s) but it might contain any number of rows, Table1.ID is not a unique ROWID, it is a document ID, therefore I must return all the rows pertaining to the last 5 Table1.ID entered. Your "BeforeCount" is returning a date. It seems it should return a number: if create_date<?seperation date then totext(createdate) else @NULL should be if create_date<?seperation date then totext({#RTotal0}) else @NULL when I do this, all item prior to separation date are numbered same as {#RTotal0} all after are NULL, which seems to be the perfect setup for your conditional suppression formula (which I also had to change to DistinctCount.) The problem arises when applying the conditional suppression, {@BeforeCount} cannot be summarized. Since my “customer” is the new owner of my place of employment, and they’ve repeatedly requested this information, I can’t see them changing their needs any time soon. As previously stated, I have completed this by creating a Main Report for Top 5 and a subreport for Bottom 5, I was just looking for a slicker way to accomplish it! Thanks, as always!
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 01 Feb 2012 at 4:30am
	I assumed you top 5 as unique rows not a grouping. Assuming all rows that share a documentID have the same date this process should still work. if they don't you would have to group on the id and choose min or max date respectively. Group on id (you can suppress the header and footer if you want) insert a summary of the maximum date at group footer 1(ID). e.g. maximum(table.date,ID) Sort Group as all on this ascending the Before count was supposed to return a date (as a string) but in this case you could return the ID (or a NULL) since that is what you are trying to display. The reason for the NULL is to exclude those rows from the distinctcount. "" would still be counted as a value. //BeforeCount formula if create_date<=?seperation date then totext({table1.id},0,'') else @NULL Now do a distinct count of this distinctcount(@BeforeCount) this should give you the total number of IDs that are on or before your parameter change your running total to do a distinctcount of ID with a reset of never now in the detail suppression formula you still can use the NOT({#RTotal0} in distinctcount({@BeforeCount})-4 to distinctcount({@BeforeCount})+5) Edited by DBlank - 01 Feb 2012 at 4:33am
	IP Logged

carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Posted: 01 Feb 2012 at 8:43am
	works like a champ! looks so much better than with a subreport. THANKS A MILLION!
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 01 Feb 2012 at 8:55am
	glad you got it to work for you If you have performance hits because you are not filtering the data you could also consider applying select criteria using the param date. something like *datediff("m",{table.date},{?SeperationDate}) in -6 to 6* would limit the data to 6 months either side of your param. The risk is that the top 5 might fall outside that range but at least it is an option if you needed it.
	IP Logged

carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Posted: 01 Feb 2012 at 9:11am
	Anticipating the day will come when 5 before and after will become 10 or 15..... what I've done is, add a formula for every (5) items before & after to return, draw into report 30 days worth of data. So if they request 15 items before and after it will look back/forward 90 days. Considering 30 days should be sufficient for even 50 before and after, I think I've built in some overkill. Additionally, as my coworkers are far too dependent on me, in anticipation of being hit by the proverbial Mack truck, I'm trying to update all my reports so the end user can not only run them themselves but change the critera via parameters as necessary.
	IP Logged

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