| Author |
Message |
KSAnalyst
Newbie
Joined: 04 Mar 2008
Location: United States
Online Status: Offline
Posts: 29
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
KSAnalyst
Newbie
Joined: 04 Mar 2008
Location: United States
Online Status: Offline
Posts: 29
|
Posted: 17 Mar 2009 at 2:37pm |
Thank you DBlank,
I appreciate your help, however it isn't what I need.
K
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jeffwest1
Newbie
Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jeffwest1
Newbie
Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jeffwest1
Newbie
Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
|
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 Logged |
|
jeffwest1
Newbie
Joined: 25 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 13
|
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 Logged |
|
|
|
