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 bkrike 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
- Thread starter DSenger
- Start date
- Thread starter
- #3
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,
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):
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
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/100This 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
- Thread starter
- #6
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.
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.
- Thread starter
- #8
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
Hopefully someone else has seen this issue before and knows how to fix it.
Sorry I can't be more help.
Cheers
Pete
- Thread starter
- #10
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"])
{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.
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
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
- Thread starter
- #13
- Status
Similar threads
- Locked
- Question
- Locked
- Question