Random Sample of Records by Group

[beacon5]

Hi everybody,

I work for a hospital and Crystal Reports 8.5 and 2008 to write reports against an Oracle database, but the database is managed by a different group and because of potential confidentiality issues I don't have direct access to the database to create views, stored procedures, etc.

I've viewed faq767-3260, but I'm having difficulty getting it to work for the types of reports that I am trying to write. I've actually got the reports written, but now I'm attempting to rewrite them to add the random method.

The problem I'm having is that I'm unable to use the method to select only the records that I'm grouping by. For example, one of the reports shows a list of all the people that have accessed a patient's medical record in the database. I group by patient and have been asked to randomly pull 10% of the patients for the date range indicated.

The method in the FAQ shows that you add the @Random_Number field in the details section, but that pulls random records for each patient when my goal is to pull random patients and show all the records for those patients. I've tried adding the @Random_Number to the group header, but it doesn't appear to be working at all...it either suppresses all group headers or it doesn't suppress any group headers. Plus, and this is fairly obvious, it doesn't suppress any of the records in the details section.

Does anyone know of a way to modify the method to accomplish what I'm attempting to do or is there a way to create a separate function that will achieve this? Please keep in mind when making suggestions that I'm unable to create alternate views, stored procedures, etc.

I really appreciate the help,
beacon

 

[Madawc]

If there's an account number and you need 10% of the total, then add a test in your record selection to suppress those that don't match a particular value. E.g.

Mid({your.account}, 3, 1) = 9

This will give just those accounts where the third digit is 9.

If you need to select all records but just show some, the same technique can be applied to section suppression.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

First, use the date range in your record selection formula. Then create a formula {@rnd}:

rnd()

Place this in the detail section and insert a maximum on it at the patient group level. Then go to report->group sort->select topN: 10->select maximum of {@rnd} (ascending or descending).

-LB

 

[beacon5]

I'm not sure either response quite gives me what I'm looking for. Madawc, I'm looking for random records, so I don't see exactly how me specifying the third number of a patient's ID number will pull random patients for me, but I'm all ears if you can explain it.

lbass, I created the random formula and added it to my details section, but I'm not clear on the next part. If you're saying that I need to insert a maximum SUMMARY on it at the patient group level, then I tried that, but it only gives me the top 10 patients (there are 43017 total before doing anything, so I should return 4301 random patients).

I'm looking to return 10% of the total patients in the group level, which should be 4301 out of 43017, and have all associated records for the patient appear in the details section. I need to see all the details because it shows who has been accessing the patient's account, but I don't need to see all patients because we're just auditing for the 10%.

I really appreciate all of your help, but still could use a bit more. I apologize if anything I've written in this post was absent from the original post...hopefully this will clear things up a little.

Thanks,
beacon

 

[lbass]

Sorry, I missed the %. You can use the same technique, but instead of topN, choose "All" in the group sort. Then in the section expert use a suppression formula for the group header and footer and the detail sections:

groupnumber > 10

This will work as long as you only have one group.

-LB

 

[beacon5]

I'm still having issues with it.

Here's what I've done so far:

I created the @Random_Number formula with Rnd() as the only code. Then I added it to the details section

I removed RecordNumber > .1 * Count([MyField]) from my details section suppression formula and added the following to the group header, group footer, and details sections

GroupNumber > 10

I created a summary field (maximum on @Random_Number) and it inserted into the group footer. Then I went into TopN/Sort Group Expert and selected 'All' in the "For this group sort" dropdown and chose 'Max of @Random_Number' from the "Based on" dropdown.

It looks like the records are now random, but it's only showing me 10 records. Should the formula be

GroupNumber > .1 * Count([MyField])

instead of GroupNumber > 10??

Not to make this any more difficult than it already has been, but my report actually has more than one group level. Is it possible to make this work with more than one group or have I completely maxed out the complexity?

Thanks again,
beacon

 

[lbass]

Whoa, sorry! I still forgot the 10%! Your formula would work for suppression except the group number won't evaluate correctly with multiple groups.

Instead, insert a running total {#grpcnt} that does a distinctcount of the patient ID field, evaluate for every record or on change of group (doesn't matter), reset never. Then use a suppression formula of:

{#grpcnt} > .1 * count({anyrecurringfield})

-LB

 

[beacon5]

Ok, I think that worked, but I'm going to tell you what I did to make sure I put everything in the right place, if that's cool with you.

- I put the running total in the group section.
- I entered the suppression formula for the group header, group footer, and details section.
- I didn't change the maximum summary for the group
- I didn't change the TopN/Sort Group Expert stuff as 'All' and based on 'Max of @Random_Number

Sound right?

 

[lbass]

Yes, that all looks right. The running total could have been in the detail, group, or report footer section.

-LB

 

[beacon5]

Ok...cool.

This is a shot in the dark, but would I be able to take the modulus of the group number (say mod 2 if I had 2 groups, mod 3 for 3 groups, etc.) to get the group number to evaluate correctly (or something like that)?

 

[lbass]

It doesn't work quite like that, I don't think. But you could certainly test it--which is what I would do if I wanted to know definitively.

-LB

 

[beacon5]

Y'know...no sooner than I hit submit on my last post, I tried entering the GroupNumber > .1 * Count([MyField]) into a second group and it appeared to work. I returned the same number of records and they appeared to be random.

I'm still trying to wrap my head around how it works, but as long as it appears to be working I'm sure I can get away with it.

Thanks, as always, for all your help,
beacon