Sorting in a group by a formula
I'm sure this has been asked and answered many times - did a search but no joy. Have a group footer with a number of shared variables being displayed. One is a margin %. I would like to sort the groups descending by this margin %. I can't seem to insert a summary based on it so can't use the group sort expert. Any suggestions?
Abhilash Kumar replied
The better workaround is to move the calculation from the shared variables to the database. In short, you'd need to bring that calculation as a field on the report.
If that's not possible for some reason, have a look at the attached report. Download the file > extract its contents > Change the extracted file's extension from .txt to .rpt.
Here are the steps to assist you in completing the report you're working on and you can compare it against the one attached here.
1) Insert the Main Report as a Subreport in the Report Footer
2) Create a formula in the Main Report and concatenates the Group Name and it's percentage calculation.
3) Send this formula field as an unlinked prompt into the Subreport you inserted in Step 1.
(right-click the subreport > change subreport links > move the formula field to the pane on the right > uncheck the option 'Select data in subreport based on field' > click OK)
4) Suppress all sections on the Main Report except the sections that hold the subreports. The subreport that was just inserted would display all the data on the report whereas the Main report is just used to accumulate the values for further processing.
5) Double click the subreport > create a formula that looks for the group name in the string that is passed into this subreport and extracts the value associated with that group
6) Insert a summary (using the Maximum function) off of this formula field and place it on the group header/footer.
7) Go to the Group Sort expert and you should see that you're able to sort groups based on this summary.