Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Percentage Formula for Crystal reports

Status
Not open for further replies.

DSenger

Vendor
Oct 29, 2019
10
US
I am needing to pull only a percentage of names. Needing a formula for Crystal Reports. Thanks for any help with this!!
 
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.[highlight #FCE94F][/highlight]

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):
Code:
RND(100)

Sort the records on the new formula.

In the Section Expert for the Details section add the following Suppression formula:
Code:
RecordNumber > Count({Table.CustID})*10/100

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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top