Bi-Directional Sorting in Webi
Sorting a table in ascending or descending order is easy and build in to the Webi tool, but what about changing the sort from Ascending to descending or vise
We can achieve this fairly easily by maintaining 2 hidden columns and 2 variables that are based on a common input control.
- Create a Variable named SortSelection that will capture the input control selection, default to empty string.
- Create an input control that consists of 2 options: Ascending and Descending
- Set the input control to operate on the variable you just created in the previous step
- Create a Variable named Ascending, set this variable to perform the following logical operation:
- =If [SortSelection] = “Ascending” Then [Insert column output dimension or measure here] Else “”
- Create a Variable named Descending, set this variable to perform the following logical operation:
- =If [SortSelection] = “Descending” Then [Insert column output dimension or measure here] Else “”
- In the Table you want to sort, insert 2 columns
- Apply an ascending Sort to the first column and have it report on the Ascending variable
- Apply an descending Sort to the second column and have it report on the Descending variable
- Hide the 2 sorting columns
That’s it! using the input control, you can dynamically change how the table is sorted.
NOTE: this works for numerical value well, you can have the sorting columns populate with any number of values as long as they are numbers
and not strings. Separate sorting column should be used for strings. If you try to use a string variable and number variable in the same column, the tool will
sort the number values by their character value instead of their numerical value
To Sort dynamically on more than one data type (strings, dates, numbers, etc…), you can create 2 blank cells for each data type. Then create 1 variable to hold each data type and insert it into the 2 blank cells for that data type. Set one of each data type’s cells to sort ascending, and other to descending. Add logic to those cells based on the input control selection for ascending vs descending to either show the contents of the variable, or null.
Create another input control to control what to break/section on and have that variable’s content control what datatype cells are populated.
NOTE: Removing All Sorts from the section first is sometimes required for the dynamic breaking/sectioning/sorting to work.