Ranking: Sorting based on variables?

tagarw · August 8, 2014 3:41AM

Can we customise the sorting operation in Omniscope. For example: I have a table with two columns/fields [empname] and [salary] and a Variable allowing the user to select between these two column names. For example, if user selects 'empname' as Variable value, data in table should be sorted by 'empname', and if user selects 'salary' as Variable value, then data in table should be sorted by salary.

See attachment for reference.

Thanks..

tjbate · August 8, 2014 4:40AM

Jagran - This is done by adding a new RANK Formula field defined in terms of a user-selected Variable. Setting a permanent primary sort on this new Formula field means that whatever value the user chooses for the Variable in the RANK function, the data set in that view will be sorted according to the ranking calculated on the selected Variable value.

Syntax and examples for using the RANK function are here:

http://forums.visokio.com/discussion/2479/formulae-rank-syntax-ranking-on-aggregated-values-etc

tagarw · August 8, 2014 4:58AM

Thank you. I tried what u suggest. But there is error while using variable. I am able to write formula with field.

Error evaluating result for record 1: Argument 2 of function RANK, value "Sort_Options": "Sort_Options" is not a valid field name Usage: RANK(value, field, isAscending, includeNulls, dataSubset) Returns the rank of a value within the field specified.

tjbate · August 8, 2014 9:42AM

If you are using a Variable to allow users to select a [Field Name], you must use the REFVAL[Variable] function to convert the value of the Variable into a Field Name that the RANK function can accept and use to rank the values. You cannot use a Variable to represent a [Field Name]. You must convert it to a Field Name using REFVAL first.

There are also FIELDNAME and FIELDNUMBER functions that can be useful.

tagarw · August 11, 2014 3:19AM

Hi tjbate,
I tried the same. and have attached error. looks like it is mapping variable value with data inside the column name. but what needed is:

selected value in varibale should be mapped to a column name. And when selected any value from the variable the data in the table should be sorted on corresponding column.

Thanks..

tjbate · August 11, 2014 5:11PM

Jagran - The attached file illustrates how to do this. It is best to evaluate REFVAL in a separate field/column, then refer to this column in the RANK function.

tagarw · August 14, 2014 2:17AM

Thanks you for your great help with this.. :)

Welcome!

Categories

Ideas Parade

Tagged