Random Selections in Crystal

[Former Member]

I am wondering if anyone has any tips on using Crystal to randomly select a certain number of records that fit within some designated parameters.

What I am wanting to do is create a report that randomly lists 10 incident numbers for every user that has incidents closed within a certain date range. The date range is 2 parameter fields.

[Former Member · Accepted solution]

You can use CR to display a random sample of records. Here's how I do it....

1) Create your report laying out the fields in the format you need, record selection parameters, etc. To display X records per user, group on your user field. You can supress your group header and footer and just display the details, if you want.

2) Create a formula field, I'll call it RandomNumber to illustrate. The contents of the formula should be: rnd()

3) Include the RandomNumber formula field in your detail level, you can hide the field so users don't see it.

4) In your Record Sort Expert, sort ascending on your @RandomNumber formula field

5) Add a Running Total Field (I'll call it RecordNumber) to your detail level. Field to summarize: @Random Number // Type of summary: count // Evaluate: For each record // Reset: On change of group - Group #1: Username. You can hide this field too.

6) In the Section Expert for the Detail section, create a conditional Supression formula - click the X-2 icon to the right of Supress (No Drill-Down). For the formula, enter {#RecordNumber}>X where X is the # of random records to display per user. If you want the users to select the # of random records to display, create a NumberOfRecords Parameter Field which prompts the user to enter the # of records to display and set your formula to {#RecordNumber}>{?NumberOfRecords}

That should give you what you're looking for. What this does is.... The @RandomNumber field will assign a randomly generated number to each row. You sort by this field to randomly order records. You count each row per user and hide all rows where the row number is greater than the number of random records you want to see.

Hope that helps and works for you.

Deb

[Former Member · Accepted solution]

Thank you Deb, that worked doing it that way. Not ideal but it works to do it that way.

[Former Member]

I am currently trying to set up a barcode to a randomly generated number. Can someone briefly explain how I do this? I am currently using:

BarcodeC39(RND()*100000) but this is not working. It is asking for a string?

Any workable suggestions would be appreicated.

Thanks

[Former Member]

I'm not familiar with the syntax for the BarcodeC39 function, but from the error, it sounds like it is looking for a string, not a number and the rnd function is passing in a number.

Have you tried barcodeC39(totext(rnd()100000))? That will convert the random # you have generated to a string. Note, the random # you generated may have a decimal. If that causes problems in the string, to format the # as a string without the decimal, try barcodeC39(totext(rnd()100000,"#"))

[Former Member]

Brilliant! Thank you

[Former Member]

I already knew this, but thanks though.

I don't want random numbers selected I need random "incidents" selected from within a specific set of parameters. Rnd does nothing for me here so this is just something that crystal is obviously incapable of doing.

[former_member292966]

Hi Anthony,

You are correct that Crystal cannot go out and grab records randomly. It's designed to read records based on the criteria or query you pass back to the database.

For something like what you want, a front end would have to be designed as another table that you've pre-populated with a random set of records so you can pull them into a parameter.

Good luck,

Brian

[Former Member]

would using an array be of any use?

pass an 'identifier' for the records to the array and then 'randomly' create a subset of the array......

this any help or am I barking up the wrong tree?!

Dom

[Former Member]

Crystal has a RND function that will give you a random number. You can experiment with that and see how you can apply it to your Incidents.

[Former Member]

Ya, the Rnd function returns a number from 0-1 so unfortunately I don't think it's really going to do too much for me in this situation.

[Former Member]

rnd returns a value greater than or equal to 0 and less than 1, but by playing around with this function, by multiplying it up and then rounding the resulting value you can get random values between 1 and 10

dom