crystal reports - random samples

[charlieintegral]

Hi,

I have a sample of 85 work numbers which I need to select 5% at random. I'm new to rnd command and the help on crystal reports V9 isn't idiot proof, could anyone help? I'm not sure what to put in and where!!

Greatly appreciated.

 

[Turkbear]

Hi,
What database is the source of the work numbers?
The randomnness is set by the record selection formula, not by using the RND() function ( at least not directly), so the version/capabilities of your database is important..

If you only want to Display/Print a random subset of all those numbers after returning all of them from the datasource, you could use a supression formula that only allows 1 out of every 20 ( 5% ) to show.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[bdreed35]

IdoMillet wrote a FAQ that demonstrates how to do this:
faq767-3260

~Brian

 

[charlieintegral]

the database is oracle9,

the number of work orders could change every month - this is to pick 5% of work orders per month for audit, so has to be completely random.

 

[Turkbear]

Hi,
Since it is Oracle , you can use one of its dbms.utility funtions ( either in a view or by using a command object) to generate part of the selection criteria,like:

and dbms_utility.get_hash_value(dump(rowid),0,128)<10;

By adjusting the < # you can control the # of records and therefore determine what is needed to get your 5%..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[charlieintegral]

thanks for the info, although still having problems,

I have grouped sites and done a distinct count of work orders per site.
record sorted them by ascending using the rnd(work_order)in the details section.
In section expert details, I have ticked suppress and input the following formula:-
recordnumber > (5/100)*DistinctCount ({WORK_ORDER}, {site}).
I would expect to see in the preview tab the 5% of work orders per group, but this does not work, only if you drill down. - What am I doing wrong?

 

[lbass]

Insert a running total {#cntwingrp} that does a count of a recurring field, evaluates for each record, and resets on change of group. Then after sorting by the rnd() function, use a suppression formula on the detail section:

{#cntwingrp} > .05 * count({table.field},{table.group})

You should elminate duplicate IDs before implementing this. Try using database->select distinct.

-LB

 

[charlieintegral]

Thanks lbass, this worked briliiantly, just one more question, how do I make the 5% round up to the next whole number, e.g if there is only 1 record in that group, I want it to return that one record, if there is 25 records in that group, I want it to return 2 records etc etc.

your help is much appreciated!!

 

[IdoMillet]

Use the RoundUp() function:

{#cntwingrp} > RoundUp(.05 * count({table.field},{table.group}))

hth,
- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[charlieintegral]

it doesn't recognise "RoundUp" as a function! I'm using CR9 if that helps

 

[IdoMillet]

Yes, the version does make a difference.

Try the following:

{#cntwingrp} > Int(0.999 + .05 * count({table.field},{table.group}))

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[charlieintegral]

you absolute star! thanks so much, this has taken me so long to work out!!

cheers

charlie