Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Random Selections in Crystal

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.


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.


0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question