I have a report with two groups, and only one footer section shows; all other sections are hidden. I would like to sort the section that does show by a calculated field, but the only choices I have in the Group Sort Export are to sort by various sums and averages.
My report shows sales transaction information, in the following sections:
GH1 - Salesperson - hidden
GH2 - Sales Transaction Header - hidden
Details - Sales Line Detail - hidden
GF2 - Sales Transaction totals - hidden
GF1 - Salesperson totals - SHOWS
I am using simple summary formulas to show each salesperson's total Revenue and total Gross Profit. Then I use a formula in GF1 to show each salesperson's Profit Margin, based on total GP & Revenue for each person, which is:
I would like to sort the report output by this calculated field, but I can't figure out how to accomplish this.
I tried putting a formula in the detail section and then inserting a summary into GF1, but the math doesn't work out correctly. I also tried changing the group to use a formula as the Group Sort Order, but the fields I need aren't available there, either.
Can anyone help?
My report shows sales transaction information, in the following sections:
GH1 - Salesperson - hidden
GH2 - Sales Transaction Header - hidden
Details - Sales Line Detail - hidden
GF2 - Sales Transaction totals - hidden
GF1 - Salesperson totals - SHOWS
I am using simple summary formulas to show each salesperson's total Revenue and total Gross Profit. Then I use a formula in GF1 to show each salesperson's Profit Margin, based on total GP & Revenue for each person, which is:
If sum({Revenue}, {Salesperson})=0 then 0 else sum({GP}, {Salesperson}) / Sum ({Revenue}, {Salesperson}) * 100
I would like to sort the report output by this calculated field, but I can't figure out how to accomplish this.
I tried putting a formula in the detail section and then inserting a summary into GF1, but the math doesn't work out correctly. I also tried changing the group to use a formula as the Group Sort Order, but the fields I need aren't available there, either.
Can anyone help?