Changing Sort Order @ Runtime 4

[carynbo]

According to thread149-790093 one can give the user a choice of how to sort fields in a report by giving the user a parameter from which to select. A formula is based on the parameter, and then the formula is used for the sort order.

Is there a way I can modify the formula below to sort CompanyName in Ascending or Descending order, based on the parameter chosen by the user at runtime?

if {?GroupBy} = 'A'
then {table.companyname}
else if {?GroupBy} = 'D'
then {table.companyname};

Thanks, Caryn.

 

[lbass]

Create two formulas:

if {?GroupBy} = 'A'
then {table.companyname}

if {?GroupBy} = 'D'
then {table.companyname}

Add both formulas to the record sort area, choosing Ascending for the first, and Descending for the second.

-LB

 

[carynbo]

Thanks LB. Good solution, and you forced me to better formulate my question.

My reports display aggregate data by Company Name and Category; therefore Company Name and Category are groups. Groups specify Ascending or Descending order. Therefore, even if the Record Sort Expert sorts records based on a formula that contains a user-selected parameter, the hard-coded group order (Ascending or Desc) takes over.

Using this reporting structure - grouping by Company Name and Category, is it possible to give the user the following choices:

* Sort by Sales Amount, in ascending or descending order, or
* Sort by Company Name within Company Category, in ascending or descending order.

Here's an example of how a report currently appears:

Sales Amt
GF2 CompanyA 100
GF2 CompanyB 99
GF2 CompanyC 125
GF1 Co. Category ABC 324

GF2 Company D 60
GF2 Company E 30
GF2 Co. Category DE 90

RFooter Total ABCDE 414

Thank you for your thoughts.

caryn.

 

[synapsevampire]

* Sort by Sales Amount, in ascending or descending order, or

LB's solution addressed this (given that you have hardcoded groups which it won't override), Create a formula such as:

If {?MyParm" = Sales"
then
{Table.sales}

* Sort by Company Name within Company Category, in ascending or descending order.

You've ALREADY grouped by Company, so the will already be in order.

I suggest you further think about your requirements and post them because you're not making sense, or have a limited understanding of databases, grouping and sorting.

You can dynamically change the GROUPING using a method akin to what LB suggested, I suspect that's what you want.

In lieu of using the table.field to do the grouping, dynamically create the groups by creating a formula which changes based on the parameter.

-k

 

[lbass]

First, make sure you have your company group sorted by name in ascending order. Then create a parameter {?sort} with options: "Name-Asc","Name-Desc","Sales-Asc","Sales-Desc".

Then create two formulas:

//{@sortsales}:
if {?sort} = "Sales-Asc" then {table.sales} else
if {?sort} = "Sales-Desc" then -{table.sales} else 0

//{@sortname}:
if {?sort} = "Sales-Desc" then {table.companyname} else ""

Add these to your detail section. Insert a sum on {@sortsales} and insert a maximum on {@sortname}. Then go to report->group sort and select:

"sum of {@sortsales}" - set it for ascending
"maximum of {@sortname" - set if for descending

-LB

 

[carynbo]

Perfect; thank you.

Caryn.