Skip to Content

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Oct 15, 2015 at 01:32 PM

    Abhilash - The formula uses shared number vars (3): (A - B - C) / A. The "B" value is from a subreport. I could use running totals for "A" and "C" as it turns out. Looks like I might need the "busy" workaround you mentioned.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

      -Abhilash

  • Posted on Oct 15, 2015 at 08:48 AM

    Hi Brian,

    Unfortunately, summaries can't be inserted off of shared variables.

    Could you post the formula here and we can see if that can be converted to use global variables (with no print time functions). Of course, all this if you're not using shared variables to return data form the subreport.

    If you're using shared variables to return data from a subreport, then you have a very 'busy' workaround to implement!

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.