Randomly Select Records 1

[JTinVA]

I need to run a report that will provide the details for 3 randomly selected records. How do I do this? I've looked around and can't figure it out!

Thanks.

 

[dgillz]

I had to do something very similar once. Try this:

Formula 1 in the report header, this formula will also show the record numbers selected.

WhilePrintingRecords;
Numbervar Array array1;
Redim array1 [6];

Local NumberVar i;
For i := 1 to 3
Step 1
Do( array1 := 1+Truncate (RND()*Count({Customer.Customer ID}))) ;

Totext (array1 [1],0) + '-' +
Totext (array1 [2],0) + '-' +
Totext (array1 [3],0)

The conditional suppression formula to suppress those records not randomly selected:
WhilePrintingRecords;
NumberVar Array Array1;
not (RecordNumber = Array1)

I suppose you could take this farther and do more testing in case a number was randomly picked twice.

Let me know if you have any questions.

Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[chelseatech]

The easiest way I found to do this was
1) create a random formula
Rnd()

2) sort the data by that formula....

3) have a GROUP SELECTION formula of...
recordnumber <= 3 // if you only want the first 3 records

This only works if you do NOT have any groups in your report. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[JTinVA]

Thanks! These suggestions helped me to solve my problem.

 

[kkinnea]

How can one apply this to reports that are grouped? I tried a solution posted to the forums at Crystal Decisions, but it would not always select the number of records I indicated (going above or below my specified count, even when a group had more than the specified number of records).

Any help would be appreciated!

Kristen

 

[synapsevampire]

I think that if you make the rnd() formula the number 1 group of a report with other groupings, and add in the recordnumber <= X to the record selection group formula, this will randomly supply data for the number of rows specified by X.

Perhaps I misunderstand your requirement.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[chelseatech]

If you want a random selection for a lower level group, eg. want 5 customers selected at random for each region, use the random function as above as the sort inside the group and the three formaula technique.

Group Header
WhilePrintingRecords; numbervar found:=0

Details
WhilePrintingRecords; numbervar found:=found + 1

Conditional suppress of Details:
WhilePrintingRecords; numbervar found;
found > 5

All the records are processed, but you only see five for each group 1. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kkinnea]

I was not able to get the above described technique to work exactly as I need it to.

I have a report that is grouping first by a selection of companies, then by state. What I need is: for each company, a random selection of records, regardless of state. I think my state group is getting in the way.

I took the following steps:

1. Created a formula with Rnd() in it
2. Sorted by this formula (but did not place it on the report grid)
3. Created the Group Header formula described above and added it to the Company group header
4. Created the Details formula described above and added it to the Details B section (I wanted to confirm the number)
5. Added the conditional suppress logic into Details A

If I remove the State group, it works correctly until the end of a page occurs - then my random number starts over again on the top of the page! So for a company that has 26 records, I get all 26 - 22 on the first page and 4 on the second page.

Do I actually need to add another step or another formula somewhere?

Thanks,
Kristen