Randomly display n% of a group 1

[a75537]

I need to create a report that displays random records totaling n% of the total records for each group, for a specified time period.

The report is grouped by employee first, and then work queue. The detail displays the items worked for each queue within the selected date range. However, I only want to display n% of the items worked - chosen randomly.

For example, employee1 completed 100 work items in queue1 between date1 and date2. I only want to display a random selection of work items totaling 5% of the total work items. Therefore, I want to display 5 work items in the detail section chosen randomly within date1 and date2.

Thanks.....

 

[Reebo99]

There is a function within crystal rnd() which acts like the same function in VB. I don't know how you would use this but it might put you on the right track. Reebo
Scotland (Going mad in the mist!)

 

[Reebo99]

I've just had a little play and you can create a formula like (basic syntax):

whilereadingrecords
formula = round(rnd(),1)

then in the selection formula put soething like :

{formula} = 0.10

This will give a random selection of records. In the tests I did it ranged from 4% to 11% of the total number of records.
Reebo
Scotland (Going mad in the mist!)

 

[IdoMillet]

Try the following:

Step 1: Place the following formula (@Random_Number)
in the detail section:
---------------
Rnd()
---------------

Step 2: Sort the records by @Random_Number (ascending).

Step 3: In the suppress attribute of the detail section enter the following expression:
----------------------------------------
100 * @Random_Number > {?Show_X_Percent}
----------------------------------------
where {?Show_X_Percent} is a parameter allowing the user to
specify a number between 0 and 100.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[a75537]

Thanks IdoMillet! I think I'm getting really close to a solution thanks to your suggestion.

There is still a problem with the number of records that are displayed - it is not consistent. For example, if the group contains 102 records and I enter 6 as the percentage of records I want displayed, the report should display 6 records-> (102 * 0.06 = 6.12). However, the first time I run the report it might display 4 records, the the second time I run the report it might display 2 records, and then the third time it might display 6 records. It seems the amount of records displayed is usually close to the percentage I want displayed - probably within 2 to 4 records.

Any ideas?

 

[IdoMillet]

Rand() would provide exact proportions only when the number of records is large.

If you know the number of records you want, simply change the suppress attribure expression to show the first N records and suppress the others.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Actually, here's a cool way to do this precisely without knowing in advance the number of records. Chage Step 3 above to:

Step 3: In the suppress attribute of the detail section enter the following expression:
----------------------------------------
RecordNumber > ({?Show_Random_X_Percent}/100) * Count({some_field})
----------------------------------------
where {?Show_X_Percent} is a parameter allowing the user to
specify a number between 0 and 100.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Here is a "clean" version of the whole procedure.
I think I'll turn this into an FAQ:
-----------------------------------------------------
Step 1: Place the following formula (@Random_Number)
in the detail section:
---------------
Rnd()
---------------

Step 2: Sort the records by @Random_Number (ascending).

Step 3: In the suppress attribute of the detail section enter the following expression:
----------------------------------------
RecordNumber > ({?Show_X_Percent}/100) * Count({some_field})
----------------------------------------
where {?Show_X_Percent} is a parameter allowing the user to
specify a number between 0 and 100.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[a75537]

I don't think I can use Rand(), the number of records can range from 1 to 500.

I know the number of records I want by taking the:
RecordNumber * percentage or
Count of group records * percentage.

I've tried entering a loop in the suppress attribute experession to only display the top n records.

Ex.
For x = 1 to Round((Count of group records) * percentage) Do
100 * @Random_Number > {?Show_X_Percent}

I keep getting an error...
"The Summary/Running total field could not be created."

Is there another way I can change the suppress attribure expression to show the first N records and suppress the others?

 

[a75537]

I've tried the code in step 3, however I'm still getting the error - "The Summary/Running total field could not be created."

This would be a great FAQ to post!

 

[IdoMillet]

See my answer above. I tested this and it works... )

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Make sure what you are counting in step 3 is not a summary formula. It should simply be one of the simple fields.

If you can't see where the problem is, post the expression you are using...

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[a75537]

OK. I'm no longer getting the error message, but when I run the report all the records are now displayed.

Here is the expression I'm using:
RecordNumber > ({?Percent}/100) * Count ({QUEUELOG.ACCOUNTNO})

Maybe I'm not entering the expression in the right location...
Under Format on the menubar I click on Section..., then click on Details, then select the button with the X-2 on it beside Suppress (No Drill-Down) on the Common Tab, and enter the formula here.

 

[IdoMillet]

You can e-mail me (ixm7@psu.edu) your report with saved data.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com