2428861 - Unable to sort a group on a percentage formula in Crystal Reports

Unable to sort a group on a percentage.
Percentage formula not listed in the fields to sort a group by.
Why can't we sort a group on a percentage formula in Crystal Reports?
How to sort a group on a percentage formula in Crystal Reports?
Note: Images and data in this SAP Knowledge Base Article are from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

In Crystal Reports designer, create a report off any data source.
Insert a group on the report.
Insert a sum of a value, like the sales amount for the group.
Insert a second sum on another value, like the sales target for the group.
Create a formula that calculates the percentage for the group based on the above 2 summaries, like:

     ( Sum (<Sales>,<Group>) / Sum (<Sales Target>,<Group>) ) * 100
Under the menu "Report", select "Group Sort Expert"
In the "Group Sort Expert" window, the formula that calculates the percentage for the group does not appear in the list of summaries to sort the group by.





How to sort a group based on a percentage summary formula?

It is not possible to summarize a formula that already uses a summary due to the evaluation time.
To generate a report, Crystal Reports executes multiple tasks in a specific order, which are divided in 3 pass. Below is a very general summary of each pass.

Pass 1 : Evaluate "constant" formulas, generate the SQL Query, retreive the report data, sort, group, calculates summaries,...
Pass 2 : Format Reports Pages: Group Selection Formulas, Evaluates Running Totals, Chart, cross-tabs, evaluates Print Time formula, Subreports,...
Pass 3 : Calculates the Total Page Count.

And since the sort of the groups is performed in the first pass, but the percentage formula is evaulated in the second pass, the report won't have the result of the percentage formulas before the group are sorted, therefore this is why it is not possible to sort a group on percentage formulas.

To workaround the fact that percentage formula are evaluated later than the group are sorted, use a subreport. The main report will be used to calculate the percentages, and the values will be pass to the subreport, which therefore will be available to use before the subreport group are performed, which will give the possibility to sort the group by the percentage values.
Below are the general steps on how to apply this advanced reporting technique to a report to be able to sort a group on percentage formulas:

Save the current report under a different name.

Note: We will use the current copy of the report to calculate the percentage value for each group and store the values in variables.
Create a formula that will store the percentage calculated value in a variable, as well as the group name it is associated to like:

WhilePrintingRecords;

Local StringVar currentPercentage := ToText({@Sale Percentage},2,"");
Local StringVar currentGroupName := {<Group Field Name>};

StringVar allPercentage;
StringVar allGroupName;
NumberVar counter;

counter := counter + 1;

If counter <= 1000 Then
(
If OnFirstRecord Then
(
   allPercentage := currentPercentage;
    allGroupName := currentGroupName;
)
Else
(
    allPercentage := allPercentage + " ^ " +currentPercentage;
    allGroupName := allGroupName + " ^ " + currentGroupName;
)
)
Insert the formula in the Group Header section.
Create 2 formulas that will simply output the result of the 2 variables that concatenates the percentages and the group name. Each formula will look like:

WhilePrintingRecords;
StringVar allPercentage;

Note: Those formulas will be used to pass the values to the subreport.
Suppress all the section of the report except the following:

Insert the original report as a subreport in the Report Footer section of the main report.

Note: The subreport will be the one displaying the information.
Create 2 parameters in the subreport to store the percentage values from the main report, as well as te group names.
Create a formula to extract the percentage values for each group like:
WhileReadingRecords;

Local StringVar currentGroupName := {Customer.Customer Name};

StringVar Array aPercentage := Split({?My Percentage},"^");
StringVar Array aGroupName := Split({?My Group Name},"^");
Local NumberVar x := 0;
Local BooleanVar Found := False;

If currentGroupName in aGroupName Then
(
   While Found = False or x >= uBound(aGroupName) Do
   (
       x := x + 1;
       If aGroupName[x] = currentGroupName Then Found := True
   )
);

If Found Then ToNumber(aPercentage[x])
Insert the formula in the Details section of the subreport.
Insert a summary Maximum on the formula.
Select "Group Sort Expert" under the menu "Report", then sort all on the Maximum summary value of the formula. ( This will actually be the percentage value )
Back to the main report, link main report to the subreport on the following:

Formula that contains the variable with all the percentage value concatenated in one string, to the subreport parameter for the percentage.
Formula that contains the variable with all the group name concatenated in one string that are associated with each percentage, to the subreport parameter for the group name.

Limitation
The workaround is limited to 1,000 values, because it uses arrays in the subreport, and an array is limited to 1,000 values. If you expect the group to have more than 1,000 values on your report, then store the values in multiple strings of 1,000 values each, and link each of those strings to different subreport parameters, as demonstrated in the above technique.
Sample report demonstrating the above technique, is available in the Attachments section below. ( Sort Group On Percentage Formula.rpt )

CR, WPR formula sorting , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020

Sort Group On Percentage Formula.rpt