Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: Random Selection of Cases Post Reply Post New Topic
Author Message
KSAnalyst
Newbie
Newbie
Avatar

Joined: 04 Mar 2008
Location: United States
Online Status: Offline
Posts: 29
Quote KSAnalyst Replybullet Topic: Random Selection of Cases
    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


Edited by KSAnalyst - 10 Mar 2009 at 1:49pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
KSAnalyst
Newbie
Newbie
Avatar

Joined: 04 Mar 2008
Location: United States
Online Status: Offline
Posts: 29
Quote KSAnalyst Replybullet Posted: 17 Mar 2009 at 2:37pm
Thank you DBlank,
 
I appreciate your help, however it isn't what I need.
 
K
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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...
IP IP Logged
jeffwest1
Newbie
Newbie
Avatar

Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
Quote jeffwest1 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.



Edited by DBlank - 30 Mar 2009 at 7:41am
IP IP Logged
jeffwest1
Newbie
Newbie
Avatar

Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
Quote jeffwest1 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 30 Mar 2009 at 7:35am
IP IP Logged
jeffwest1
Newbie
Newbie
Avatar

Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
Quote jeffwest1 Replybullet 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.
IP IP Logged
jeffwest1
Newbie
Newbie
Avatar

Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
Quote jeffwest1 Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version 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



This page was generated in 0.015 seconds.