Sorting Data Dynamically in a Webi Report

[Former Member]

Hi All,

I had a strange requirement from my client to sort the data dynamically based on fields selected by end user in Read mode as not all users allowed in to go in Design Mode. I searched through a number of blogs but couldn't find anything that could really help me in this case I also noticed a query posted for similar requirement Is it possible to sort data in reading mode? | SCN. as unanswered.

I worked through it and finally got a solution that could really help us in future requirements. I will be using E-fashion universe to demo this.

In this report based on below screen shot I have Year, Article Name, Amount Sold, Quantity Sold and Margin. Now, I want end user to give a flexibility to choose the measure to which he wants to sort the data like Amount Sold, Quantity Sold or Margin.

For this we will first create a Dimension Sort Field as

[Sort Field]="Amount Sold"  ( I am just giving it a random field for the one end user wants later on it will be automatically updated based on end user selection).

Next step is to create a Input Variable that will be displayed to end user in Read Mode along with the choices to which he wants to sort the data. This Input Variable will have a Custom List Of Value containing the Texts that should be available to end user for Sorting the Data in our example we have inserted texts for "Amount Sold", "Quantity Sold" and "Margin".

Now in the next step we will create a Dimension that we will be using for Sorting the Data in the Block.

[SortVariable ]=If ReportFilter([Sort Field])="Amount Sold" Then [Amount Sold]

                      Else If ReportFilter([Sort Field])="Quantity Sold" Then [Quantity Sold]

                      Else If ReportFilter([Sort Field])="Margin" Then [Margin]

                      Else 1

Now Pull this variable in the Block and Hide this Dimension so that it should not be visible to End User. And then just sort the data in the Block based on this variable as shown in scneen shot below.

Now just select the option from the Input Control on which you want the data to be sorted in the Block and you can add up measure if you want the end user to give some more options to sort the data.

This option will be available to end user when in Read mode. The only limitation that I see with this approach is that we can give flexibility to end user on only the objects with similar Data Type like here Amount Sold, Quantity Sold and Margin are Number type if we try to include Article Name as well to the Sorting field I am afraid we will experience some weird results.

Please do share your thoughts and comments on the above approach your suggestions and comments will help me in improving this.

Best Regards

Niraj Parihar