Last X number of records

[adam7]

Hi there, probabley a simple one but I can't for the life of me figure it out. I using SQL 7 CR8.5. I'm trying to show the last 300 records logged onto our system based on the creationdate of the record.

I'm having problems limiting them to 300 with counts etc... as I think I'm getting in a mess with whileprinting/whilereading records. I need to take these 300 records and then put them into their own groups based on a datediff formula which isn't a problem. I currently have the formula....

WhilereadingRecords;
numbervar counter:=counter+1

against each record, and then in the record selection I limit it to less than 300. The problem is it doesn't sort them into date order before printing the records out. If I change the whilereadingrecords to whileprintingrecords then I can't say <300 in the record selection??

Any ideas?

Thanks

 

[dgillz]

Do you ave a date/time stamp to use to determine these records?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[adam7]

Yes, it's the createdate field that I'm using. I just need it to display the last 300 records based on this date field.

 

[dgillz]

Sort the report so that the 300 records come up first. Then conditionally suppress the remaining records with a formula:

RecordNumber>300

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[adam7]

Hi, supressing the fields doesn't really help, as I also have a graph and cross-tab on there that's still looking at all of the records, not just the ones supressed. I really want the whole report to only look at the last 300 records before it starts doing any of the grouping etc...

Is this possible, and if so how?

 

[dgillz]

I didn't say anything about suppressing fields, I want you to supress the entire details section of the report with this formula. This way you only see the 300 records in question.

In terms of doing this before grouping happens, I do not believe this is possible. What are you trying to accomplish here?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[adam7]

Ok, the report I need shows the last 300 records logged. I want these grouped together by how long it took them to be closed. In group one I'm using a straight forward createdate sorted. In group two I have a formula that splits these into time it took to close the record (datediff formula between a create date and end date).

So it looks like...

<24

record blah
record blah

24-48 hours

Record blah
record blah

When the report runs I want it to only pick out the last 300 records based on the create date. I then have a graph that plots these records by the 2nd group. I then have a quick simple cross-tab that just gives a quick snap shot.

I've inserted a counter on each record...

WhileprintingRecords;
numbervar counter:=counter+1

which I've then used to say counter<300 in the record select formula. This doesn't work as when you look at the records it doesn't sort the records into order before assigning the record number to them, so I have record...

103
123
156
178

etc.... So limiting it to less than 300 gives me inacurate figures.

Does this help to explain my situation? If I used dates then no problem, but I just want to say "Show me the last 300 records logged by date, and then let me group them".

 

[dgillz]

Again, you cannot group at this point as grouping resorts the records.

Why are you grouping? What are you trying to find out about these records? There is most likely a way to get a simple count of <24, 24 to 48, etc. without grouping, and still have it drive your graph.

Try the following:
1) dump the grouping entirely, along with the variable counter. Use my initial instructions to get the last 300 records.

2) write a simple formula:
If <<insert <24 condition here>> then 1 else 0.
Be sure to include in this condition the recordcount<=300 statement, otherwise all other data, including the suppressed data, will be taken into account in this formula

3) Insert a grand total sum of this field for the count.

4) Write similar formula and insert grand totals for all other categories on your chart.

5) Change your chart to work off of these grand totals.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz