How to Allow user to select sort by field 2

[Mikele]

Simple report, no groups, but I want allow the user to select the field to sort by i.e. by DueDate or by Responsible, possible using a parameter field ?

cheers

Mike

 

[EagerBeaver]

My suggestion to you in this case would be to send the user an Excel export version of your report. This way they can use the excel datasort option to sort by whichever column they choose.

K

 

[rhinok]

Step 1) Create Parameter Field called {?SortOrder}, Value Type = String, with Default Values equal to the column names by which you wish to sort (Example: UserName, UserId, etc...).

Step 2) Create a Formula Field called {@SortOrder}. Within this formula, create a formula similar to the following:

StringVar SortOrderVar;

If {?SortOrder} = "UserID" then SortOrderVar := {TableName.UserID} else
If {?SortOrder} = "UserName" then SortOrderVar := {TableName.UserName};

Insert {@SortOrder} into the report. This formula can be hidden if you choose.

Step 3) Sort by {@SortOrder}.

There are a few things to take into consideration here:

Since you declared a variable (stringvar in my example), the values need to be the same type as the string. If you use ToText on a numeric field, keep in mind that the records may not be sorted in the manner you expect (ex: 10, 2, 3, 4, 5, 6, 7, 8, 9 ).

Don't use the WhilePrintingRecords function because you can't sort by a formula that contains it due to the evaluation time.

Use Discrete Values and Single Values only.

Do not allow editing of default values...

I hope this helps, have fun and good luck!

 

[CInman]

This is interesting, however is it therefore possible to specify ASCENDING or DESCENDING too? This could potentially be a very powerful piece of functionality for me, so any thoughts would be much appreciated.

 

[Ngolem]

YES...create 2 groups with the same formula as described above... except each modified as follows:

formula for group 1

If {?SortOrder} = "UserID" and {?sortdir} = "ASC" then SortOrderVar1 := {TableName.UserID}
else If {?SortOrder} = "UserName" and {?sortdir} = "ASC" then SortOrderVar1 := {TableName.UserName}
else SortOrderVar1 = "1";

formula for group 2

If {?SortOrder} = "UserID" and {?sortdir} = "DES" then SortOrderVar2 := {TableName.UserID}
else If {?SortOrder} = "UserName" and {?sortdir} = "DES" then SortOrderVar2 := {TableName.UserName}
else SortOrderVar2 = "1";

in "the Change Group options" Group 1 would have the records sorted "Desending" whereas Group 2 would have the records sorted "Ascending".

Note: the only differences betwwen these formulas is that one group or the or is set to a constant (ie. the group has no effect) depending on the new parameter called "Sortdir"

if you have stuff you want displayed in the header or footers of these groups just make sure the fields are the same in both headers or footers and suppress the group you don't want to see....otherwise if there are not fields in these sections then jus suppress headers and footers for these groups

never tried this before but it should work....will keep it mind for myself...thanks for the idea

Jim

 

[FoxG]

Hi,

Ascending / Descending can also be easily done with a simple formula like...

numbervar s_no;
if (?sort_ordrer} = "Asc" then s_no = s_no + 1
else s_no = s_no - 1;
s_no

Hth,
Geoff