Please provide more details about what you are trying to achieve. I don't understand what you mean.

So I have to pull say 10 percent of names randomly from a number of housing units.

Ok, that makes more sense. I will need to test any approach I suggest but am moving house today. Will get back to you when I can - someone else might jump in in the meantime.

My approach would probably be along the lines of generating a random number for each record, sorting by that random number, then only returning the number of records required. There might be a better approach - will give it some thought.

Maybe this will get you heading in the right direction - let us know if you work it out in the meantime.

Hope this helps.

Cheers, Pete,

I think this should work for you.

Create the following Formula and place it in the Details section (can be suppressed):

Sort the records on the new formula.

In the Section Expert for the Details section add the following Suppression formula:

This should return 10% of a randomised set of data.

However, it is not a very efficient approach as all records will be returned to the report from the database and 90% Suppressed. If it is a large data set it may be slow.

If so, and you can tell be what the database is (ie, SQL Server, Oracle etc) I might be able to help you with a better solution.

Let me know how it goes.

Regards
Peter

Hi Peter :)

It worked. Thank you very much!!

Is there a way that when I refresh the report that it would pull a new set of 10 percent. It is currently not changing the selection upon refreshing the report.

That's interesting. Whenever I refresh my test report it returns different results so not sure why it isn't for you.

What happens if you close the report and re-open it - does it still return the same records? What version of Crystal are you running? Are you running the report natively in Crystal or through another application?

Cheers, Pete.

I closed and reopened and it pulls the same data. I have SAP Crystal Reports 2016

I am using CR 2016 (SP4 - v14.2.4.2410) and it definitely changes on each refresh. I have played around with a few settings that I thought might impact it, but nothing changed.

Hopefully someone else has seen this issue before and knows how to fix it.

Sorry I can't be more help.

Cheers
Pete

Could some of this has an effect on the way its pulling? It is pulling but is now pulling all from one unit and still not changing after each refresh.

{OFFENDER_BOOKINGS.ACTIVE_FLAG} = "Y" and
not ({OFFENDER_EXTERNAL_MOVEMENTS.MOVEMENT_REASON_CODE} in ["INT", "RAB", "RECA"]) and
{OFFENDER_EXTERNAL_MOVEMENTS.MOVEMENT_TYPE} = "ADM" and
{LIVING_UNITS.DESCRIPTION} startswith ["NDSP-BIU-GPE", "NDSP-EU", "NDSP-MTU", "NDSP-NU", "NDSP-OU", "NDSP-SU", "NDSP-WU"] and
not ({OFFENDER_EXTERNAL_MOVEMENTS.TO_AGY_LOC_ID} in ["DEFP", "NTAD"])

I had considered that the Record Selection Formula could be the cause, but my testing seemed to rule it out.

One way to test it would be to remove it and see what happens. If that fixes it, you could re-write the report using a Command so as to avoid the need for a Record Selection Formula - a bit of messing around but it is perhaps worth a try.

The only other suggestion is to check to see that you have implemented this exactly as I explained.

Also, do you have any sort order applied other than the sort on the formula containing RND(100), or other conditional suppression formulas in the report. Either of these are likely to impact the way it works.

If you post a copy of the report file I can take a look.

Cheers
Pete

Hi - thank you!
I took a further look at it and it is pulling the first 100 records every time and not randomly.

RecordNumber > Count({OFFENDERS.ROOT_OFFENDER_ID})*10/100 (this is the formula I have entered in the Section Expert Details section)

Did you apply the Sort Order as suggested?