Crystal Reports: Select the field value nearest to the 90th percentile value

[former_member548403]

I have a report that needs to display the 90th percentile from a list of values. I am able to obtain the 90th percentile value, using a summary option, but the value that I get isn't the exact value from the list; it is a calculated value by the summary option. So for example, the report will give me something like 150, but I know that the actual 90th percentile value from the list is 155. It's a little off as it is not an exact value from the list (again).

Is it possible to use this calculated number to select the actual 90th percentile value from the list, or the value that is close to it?

[DellSC · Accepted solution]

This is not necessarily straight-forward as you are trying to get a specific value. I assume you're logic gets 90% of the maximum value. Here's the logic I might use instead:

1. Order the list based on the values.

2. Count the number of values.

3. Divide the count by 10. You don't have to multiply by 9 to get 90% because you really need just the count that's the top 10%.

4. Since the value from step 3 may not be an integer, you'll need to decide how to round it:
a. Up or down using standard rounding based on the value to the right of the decimal point.
b. Always up
c. Always down

5. If you need the exact value at 90% and not the top 10%, you'll do this in a subreport in the report header with a shared variable passing the record number you calculated back up to a variable in the main report. In this case, make the subreport as small as you can, turn off the borders, and turn off "Can Grow" for the subreport. This way it won't actually appear in the main report, but you'll still get the record number value that you need.

If you'll let me know how you want to display this, I'll provide some options for how to do that.

-Dell

[former_member548403 · Accepted solution]

Thanks, Dell!

So, taking your suggestion, I was able to obtain the top 10% number and using the running total records, I was able to locate the value from the list that the report needs to show.

Now my question is, how can I bring this value to the group header? If the value is not the number from the top of the list, the field will show blank when I move the field to the group header. Any suggestion?

[former_member548403 · Accepted solution]

I did some inserting subreports and was able to get this thing to work! Thanks for the suggestion on how to get the percentile value!

[DellSC]

Actually, looking up more about how to do this, you won't use what I explained above. Instead, create the summary for your value. Then open the "Group Sort Expert" (which won't be enabled until after the summary is created) and use the following values:

For this group sort: Top Percentage

based on: the summary on the report

Where Percentage is: 10

From here you may need to play with it because I'm not sure whether it will sort ascending or descending. If it's ascending, put the data in the group header and suppress the details. If it's descending, put the data in the group footer and suppress the details.

-Dell

[former_member548403]

That is similar to what I did originally and I'm afraid it will not give me what I need...

[former_member548403]

What a dilemma. I finally got the number, but I can't move it to the group header.

[DellSC]

Try getting the number in a subreport that runs prior to the group header. Use a Global Variable to pass the value back to the main report and put a formula that contains the variable value in the group header. It will take a couple of formulas to handle this:

1. Create a formula in the main report to initialize the variable value. It will look something like this:

WhilePrintingRecords;
Global NumberVar value := 0;
""  <-- This line ensures that the formula doesn't actually show anything.

2. Put this formula in the report header (even if the report header is suppressed) and in the group footer so that the value is initialized at the beginning of the report and between each group.

3. In the subreport, create a formula that will set the value for the variable. Put this in whatever section of the subreport contains the value that you need. It will look something like this:

WhilePrintingRecords;
Global NumberVar value := {MyTable.MyField};
""

4. In the main report, create another formula to display the value from the subreport. Put this formula in the group header section where you want to display it.

WhilePrintingRecords;
Global NumberVar value

-Dell