Scenario :-
By default we can sort on a column in descending or ascending order or apply custom sort but recently i had a scenario where the user wanted to sort on different columns based on users choice. The report contains 4 columns State, Discount, Quantity Sold, Sales Revenue. We would like to sort on a different columns in Descending order based on value selected.
Resolution :-
For the above scenario we will be using eFashion universe. The solution requires creating a object in Universe to prompt the user to select the column to sort on. The below object definition can be used to do so. The Object will not parse but that’s ok.
Sort Order – @prompt(‘Select Sort Order’,'A’,{‘Discount’,'Quantity Sold’,'Sales Revenue’},mono,constrained)
The above object should be used in the result object pane and not the Query Filter.
The values {‘Discount’,'Quantity Sold’,'Sales Revenue’} can be changed based on your scenario. When you run the query you should see the below screen for the Prompt for Sort Order.
Once a selection has been made we need to capture the selection which will be used to make decision on which column Business Objects should sort on. The below variable definition will capture that for us.
VAR – UR Sort Order = UserResponse(“Select Sort Order”)
Once we know what was selected we need to create another variable that will be used in the report block . Add a new column to the report block by using the variable. The column created should be hidden by applying sizing and formatting. Since we need the column selected to be sorted in Descending order apply Descending sort on this new column and remove any sorting on other columns. You can apply ascending or custom sort also. The column we added to the report block will need to have the below logic.
VAR – Sort By =If([VAR - UR Sort Order]=”Sales Revenue”;[Sales revenue];If([VAR - UR Sort Order]=”Quantity Sold”;[Quantity sold];[Discount]))
What we are trying to do here is to show the values of the object that was selected in the prompt. The below image shows the column that should be hidden currently the selection is to sort on Discount column in Descending order.
Report when sorted on Quantity Sold and Sales Revenue
1 Comment
