Print Page | Close Window

Random Selection of Cases

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5739
Printed Date: 03 May 2025 at 8:29pm


Topic: Random Selection of Cases
Posted By: KSAnalyst
Subject: Random Selection of Cases
Date Posted: 10 Mar 2009 at 1:49pm
Hello Forum Members,
 
I am attempting to have CRXI select 35 random cases out of thousands of cases.  I saw the Rand() function but I didn't see were the user can select the number of cases to randomize.  Could someone point me in the right direction??
 
Thank you,
 
KS


Replies:
Posted By: DBlank
Date Posted: 11 Mar 2009 at 7:54am
In a similar case we created a Stored Procedure to get 5 random samples from several thousand records then used that to link to the table in the report so we always get a different data set when it is run.
The Rnd() function in crystal just gives you a random number between 0 and 1 and not a random sampling of a particular field like you want.
To see what it does just create a formula field as Random and with the code of "rnd" and place it on a detail row to see your results.
 
If you had a way to use the random number to match somehow and grouped then you could use the TOPN option in the Group Sort Expert but I don't think this is exactly what you are looking for.

Posted By: KSAnalyst
Date Posted: 17 Mar 2009 at 2:37pm
Thank you DBlank,
 
I appreciate your help, however it isn't what I need.
 
K

Posted By: DBlank
Date Posted: 17 Mar 2009 at 3:04pm

Again, we did this as a stored proc but just messing around here is another psossible process assuming you are OK pulling in all the data and suppressing all the records greater than 35 (to show your random data).

Create a formula as "RandomNumber":
Int(rnd()*3000)
 
You can change the multiplyer to whatever works for you.
Don't group anything (although it might work there to?).
Place this on a detail section and sort by it.
Create a running total and suppress everything that is >35.
This should randomly assign a # to each record between 0-3000, then sort these randomly assigned # so you will randomly display 35 records each time.
An idea to play with anyway...

Posted By: jeffwest1
Date Posted: 30 Mar 2009 at 3:44am

I have tried this, the running total is fine, except how do you then just choose the top 10 based on this, i cannot find anything that you can either filter this column by or use select expert to choose all between 1 and 10



-------------
C-3PO: Excuse me sir, but might I inquire as to what's going on?
Han Solo: Why not?
C-3PO: Impossible man.

Posted By: DBlank
Date Posted: 30 Mar 2009 at 6:41am

The above suggestion won't let you filter by a # of records but rather display a desired number of random records.

In order for crystal to "randomize" it must get all the data first then assign the random number and then sort by that. You can limit the display to a # by using a running total or record count and then conditionally suppressing the records that are > the number of records you want to show.

Someone else may be able to figure out a way to exclude the data from the report alltogether but I have not seen a way to do that yet while still randomly getting the number of records directly in crystal rather than an outside source.

Posted By: jeffwest1
Date Posted: 30 Mar 2009 at 7:23am
Having a dumb day, how would i set CR XI to only show me the top 10 based on the running total being a count of 1 - 1500

-------------
C-3PO: Excuse me sir, but might I inquire as to what's going on?
Han Solo: Why not?
C-3PO: Impossible man.

Posted By: DBlank
Date Posted: 30 Mar 2009 at 7:34am
Create a Running Total field as "Count"
Field to Summarize = a field that appears on every row - usually a unique IDnumber field
Type as COUNT
Evaluate as "For each Record"
Reset as NEVER
 
Place the "Count" running total field into the details row to validate that each row is incrementally adding 1 to the number. you can remove it or suppress it so it doesn't clutter your record display but at least you know it is working.
Go into Section Expert and select the Details section and click on the formula editor next to "Suppress (no Drill Down)" and add the formula:
{#Count}>10
This should suppress every detail row after the first 10.

Posted By: jeffwest1
Date Posted: 30 Mar 2009 at 7:51am
The first bit i had, the second was causing the issue, thanks for that.

-------------
C-3PO: Excuse me sir, but might I inquire as to what's going on?
Han Solo: Why not?
C-3PO: Impossible man.

Posted By: jeffwest1
Date Posted: 31 Mar 2009 at 1:57am
Thats working now, thanks for the help.

-------------
C-3PO: Excuse me sir, but might I inquire as to what's going on?
Han Solo: Why not?
C-3PO: Impossible man.


Print Page | Close Window