cancel
Showing results for 
Search instead for 
Did you mean: 

Sorting in a group by a formula

Former Member
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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.

abhilash_kumar
Active Contributor
0 Kudos

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

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

You can,   however, use a running total in a formula and then use the results of the formula as the basis for sorting.

Try that as a work-around.

Ted

abhilash_kumar
Active Contributor
0 Kudos

A Running total is a 'print time' object. To be able to sort groups, you need a summary inserted off of a field or a formula (with a summary function).

I don't think you can sort off of a formula that has a RT - the group sort expert would still be grayed out.

-Abhilash