Group Sorting Based on Formula

[MJA-99]

If one has a group in a report based on a certain formula value, and wants to sort in a complex (Sort is not just ASC or DESC, it would change the order of fields sorted by and fields used in sort) way for one value of that grouping formula, and another complex sort for another value, what would be the best way to achieve this?

In other words, let's say you have a formula based group called Order Status that is sorted on Date, then Name when group is "Pending" yet when Order Status is "Open", you want it to sort by Order Number, then Amount. This is just an example, not real fields.

What would be the easiest way to do this? I am thinking this situation would require a Sub-Report for each different sort of Order Status group one wanted to do. Then, in parent report, all but one Sub-Report would be suppressed based on Order Status Group value (formula in parent report) in order to get the correct sort based on what current group was. I would link Sub-Report based on fields that would guarantee match for that status and parent groupings above Order Status.

Curious about another possible solution yet I think this would be the only way to make it work.

Thanks,

MJA

[Lisa Ennis]

I believe you can accomplish this without using a subreport, and instead use formula fields. You will then use the formula fields as groups to control the sorting.

Group 1 would be the order status field
Group 2 would use a formula field, for example, "Group2Sort" IF OrderStatus="Pending" THEN Date ELSE NULL
Group 3 would use a formula field, "Group3Sort" IF OrderStatus="Pending" THEN Name ELSE OrderNumber

Add group 2 and 3 to the report and suppress the group headers and footers for each of those groups Last step is to add Amount as a sort in the Record Sort Expert.

Hope this helps!