Selecting random records in Crystal

dfor · Feb 23, 2010

I read the threads copied below. I created a formula RND() and put it into the details section of my report and it returns a decimals starting at 0.00259 to 0.99625. How can I format the field to produce whole numbers for a select statement of Random <= 25?
Thanks Dorrie
___________________________________________
Myv (TechnicalUser) 27 Jan 04 8:45
Hello
I've created a Crystal report which returns about 200,000 records. I now need to select a random sample of 10,000 records from that. Does anyone have any suggestions? I've tried using the TopN function but that was unsuccessful.
Can anyone help?

Thanks

Myv

Thank Myv
for this valuable post!

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

lbass (TechnicalUser) 27 Jan 04 9:04
Place the following formula (@random} in your detail section:

rnd()

Then use {@random} to sort your report ascending.

Finally, go to format->section->details->suppress and enter:

recordnumber > 10000

-LB

Thank lbass
for this valuable post!

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

Myv (TechnicalUser) 27 Jan 04 9:33
Thanks LB for your help but I'm sorry - I don't follow it all.
I've gone into the select expert and the chosen "show formula" and then "formula editor".
My formula at the moment is as follows:
{CBCONHPF.THCRST} = "A" and
{CBBSECPF.BSBSDS} = "Residential" and
{CBCONHPF.THOWTY} <> 3.00 and
{CBCONHPF.THCOMP} = "01"

Where do I put the {@random} command?

I understand your second instruction about format, section, details and suppress.

Finally, where do I enter: recordnumber > 10000?

Thanks in advance

Myv

Thank Myv
for this valuable post!

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

lbass (TechnicalUser) 27 Jan 04 10:45
You do not do this in the select statement.

1-Create a formula using the random function by going to insert->formula field->new->name it "random" and enter:

Rnd()

2-Place the formula {@random} on the report in the details section.

3-Then go to report->sort records and add {@random} as the sort field.

4-Then go to format->section->details->suppress (you don't need to check it)->x+2 button to the right (this is the formula area for section suppression) and enter:

recordnumber > 10000

-LB

lbass · Feb 23, 2010

I don't know how I can be more explicit. You can't use the random function in your select statement. Add the random formula to the detail section, add it to the sort records area as the sort field, and then in the section expert->details->x+2 enter:

recordnumber > 25

If you want to identify your rows by number, drag the recordnumber field from field explorer->special fields onto the report.

PS You are in a very old forum that is rarely used. Try forum767 or forum149 in the future. Also note that you could have just referenced the threadnumber instead of copying the entire old thread into the current thread. You would type "thread" with the threadnumber immediately following (no space) to create a link to the old thread.

-LB

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Selecting random records in Crystal

dfor

Technical User

lbass

Technical User

Similar threads

Part and Inventory Search

Sponsor