Return 5% of records

[ecugrad]

I have a table with 1000's of loan records. How do I write a formula that will only pull 5% of each employees loan records.

Thanks in advance..

Mike

 

[dgillz]

The first 5% of the total? or first 5% of each employee? or a random 5% of the total? or a random 5% of the employee? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[ecugrad]

Looking for the Random 5% for each employee

Thanks..

 

[kenhamady]

You can't have Crystal return only 5%, but you can have it display only 5%.

The easiest way is to sort the records by something that will put them in a random order, create a running count, and then only print every 20th line (suppressing the others). Also, if you are doing totals of the 5% you also need to make sure that they are the only ones counted in the totals.

To sort in random order you could create a formula that takes the 4th digit of the loan number, or the 5th character of the loan description, and sorts on that. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

This is more easily accomplished in a Stored Procedure, but in Crystal, follow Ken's suggestion of using an index to provide a random sort, perhaps adding the following to increase the feel of random behavior:

Formula to be used for sorting:

rnd(rnd({YourTable.YourNumericField})*10)

(or parse out a number from a string field)

The rnd uses a seed to generate a random number.

suppress criteria for the detail:

recordnumber/20 <> int(recordnumber/20)

This takes every 20th record.

This will simulate a random pull pretty well.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ecugrad]

Thanks for the help on the above. Question I have followed the advice given and set-up a running count and can print every 20th record ,etc. But, I still can't quite figure out how to print just 5%. I understand, from Ken that I can't just return 5%, I have to code just to display 5%. How do I modify the recordnumber/20 <> intrecordnumber/20. I have a Employee group summary count.

Thanks

 

[kenhamady]

Write a formula that uses the opposite condition as your suppression formula, something like:

If Remainder ({runCount},20) = 0
then 1
else 0

A sum of this formula would give you a count of the 5%.
If you need a sum of a field, put that in place of the number 1 in the formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[kenhamady]

The suppress formula for my example would be:

Remainder ({runCount},20) <> 0

Run count is your running total count, reset for each employee.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[ecugrad]

Thanks Ken, what do I need to do with your above formula to pull just the 5%. If employee 1 has 100 records the above pulls 5. I need to pull 20, 5%..

Thanks again in advance...

 

[kenhamady]

Do you want 5% or 1/5?

If you take 5% of 100 you get 5 records not 20.
If you want 20 records out of 100 that is 20%, or 1/5.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

Place the formula in the suppress section of the detail band (Ken's is a cleaner way of doing the same thing):

Right click the grayed area next to the detail band, select format section, click on the X-2 next to the Suppres No Drill Down.

As Ken states, what you seek is 20%, so adjust the 20 to a 5.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ecugrad]

Sorry Ken, you are right.. Not thinking..