Random record selection

[ddrake]

Hi,
Working with Crystal 8.5 and pulling data from a MS2KSQL db (specifically Remedy 5.1 with the HD module)

Trying to find a way to display 10% of a particular search subset. The selection must be random. The search parameters are being put in through Remedy and the returned records will then be brought up in Crystal. Therefore, no selection criteria needed, just a way to suppress all but the requisite 10% of the dataset selected at random. Any ideas would be GREATLY appreciated! Thanks!!

 

[bdreed35]

I have used a function in an Access query called Rnd() (I am not familiar with Remedy, perhaps it has something similar). This number gives a different random number for each record every time I run the query. I then sort on the random number ascending, and this gives me the random order. I haven't had to only show a 10% sample, but I will play around with it. Perhaps the above is enough to get you going in the right direction.

~Brian

 

[bdreed35]

In the query you can do something like this to return only the top 10%: ( you can run this against the sample xtreme.mdb that comes with Crystal)

SELECT TOP 10 PERCENT Rnd([Customer ID]) AS RandomNumber, Customer.[Contact Last Name], Customer.[Customer ID]
FROM Customer
ORDER BY Rnd([Customer ID]);

This returns a 10% sample randomly as you wanted to do. All you need to do is to change the syntax and the fields so that it works with your Remedy db.

~Brian

 

[dgillz]

see faq149-3261

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

http://www.trianglepartners.com

 

[ddrake]

Awesome solutions! Thanks to all who were kind enough to reply! Here was another method that seemed to work pretty well.

First...created the random field with the RND () argument and put that in the details, then:

From Crystal Decisions Knowledge Base:
1. Go to the Section Expert and select the "Details" section. (Can be based on a Group Header or Group Footer section as well)

2. Click on the formula button for the "Suppress - No Drill Down" and add one of the following formulas.

//The number chosen for the second argument of the Remainder function can be any number.
//"33" returns every 33rd record or approximately 3% of the entire record set.
Remainder(RecordNumber,33) <> 0

//This returns records where the second letter of the name field is an &quot;a&quot; or an &quot;e&quot;.
{Table.NameField}[2] <> &quot;e&quot; and {Table.NameField}[2] <> &quot;a&quot;

//This returns records where the second letter of the name field is an &quot;a&quot; or an &quot;e&quot; and also excludes //duplicate records
Previous({Table.NameField}) = {Table.NameField} or
({Table.NameField}[2] <> &quot;e&quot; and {Table.NameField}[2] <> &quot;a&quot

Went a bit farther and had it select every 10th record (second argument in the first portion set to 10 instead of 33). This allowed for an approximate 10% selection rate. Since most datasets probably won't have a number of records selected that is easily divisible by 10, you end up with approximately 9.22% to 9.99% of your sample typically. (Reached by sample variation analysis (i.e., 111 records/divided by 10 to 119 records/divided by 10 (% varies slightly the more records you have in your sample)

Thanks again all!

 

[dgillz]

I was tasked to do a random 10% sample once, and the &quot;every 10th record&quot; solution did not qualify as random, at least according to the auditor who made such decisions, so I used Ido Millet's example cited in the faq above.

In reality, the auditor, while being a bit anal (what else is new) was correct - every 10th item is not the same as a 10% random sample.

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

http://www.trianglepartners.com

 

[ddrake]

The key here is to have the random number field added to the details and then sort by that field. Then every 10th record, or whatever number, should yield a random sample.