Tuesday, April 27, 2010

Selecting a Random Set of Records

Why on earth would anyone want to select a random set of records?
Typically this is done for periodic "spot-checking". It's also super-cool and will allow you to demonstrate your formidable crystal skills!

The first thing we need is a random number. Fortunately, crystal has a handy dandy RND() function, which returns a random number.

So, create a formula called {@Random_Number} and insert the RND() function into it.

Now, drag the {@Random_Number} field onto the report (into the detail section). Now you need to sort the report on {@Random_Number}. This will start returning records "willy nilly", randomizing them each time the report is refreshed.

Now, within your Detail Section Expert, specify the following Suppression Code:
RecordNumber > {?Show_Number_of_Records}. ({?Show_Number_of_Records} is another parameter field, one that specifies the number of random records to display).

This will Suppress any record whose RecordNumber (an automagical crystal function) is greater than the desired number of records to display.

So in effect you now have a randomized set based on a specific number. Try it!

No comments: