Enabling End User Sorting in Web Intelligence Read Only Mode

I recently had the task of building a list based report which comprised of the following columns.

The key requirement however was providing the ability for an End User to choose which column they wished to have the data sorted by when viewing the report in Web Intelligence Read mode (as Design mode is restricted only to a small subset of power users). For those familiar with Web Intelligence, at present, the standard sorting feature can only be applied/configured in Design mode. However, by applying some work-arounds, there are ways we can utilise a combination of other standard Web Intelligence features to provide the ability for sorting to be applied in Web Intelligence Read mode.

There following posts provide some great discussion and solutions on how to enable end user Sorting to be applied in Read mode:

https://archive.sap.com/discussions/thread/3944249

https://www.forumtopics.com/busobj/viewtopic.php?p=1008279

https://blogs.sap.com/2016/06/13/sorting-data-dynamically-in-a-webi-report/

The technique described in the Sorting Data Dynamically in a Webi Report post was fantastic in helping me get me started, however I ended up having to make a small variation to the solution due to some sorting requirements which were specific to my case. Because I had a mix of columns which needed to be sorted descending whilst some others were to be sorted ascending, as well as the Player column being texted based (char) whilst the remaining columns were numeric, I wasn’t able to utilise the SortVariable and applying a default sort order (either ascending or descending) on this hidden column (as discussed in the post). I therefore utilised the block ‘hide/show’ feature in combination with an input control to achieve the overall solution.

Let’s take a step by step look at how the solution was implemented.

STEP 1

Create a dimension variable and call it Sort By. Set the value to one of the column objects in the report. I’ve chosen “Rank Current” as this is the default column I wish the sort to be applied on. This variable value will subsequently be automatically updated based on the input control user selection.

Note: I unchecked ‘Allow selection of all values’ to ensure the ‘All Values’ option doesn’t appear in the Input Control drop list.

STEP 3

The following steps are repeated for every column which is included in the sorting Input Control ie. the steps are repeated 6 times for the following sort scenarios:

• Player
• Rank Current
• Rank Move
• YTD Wins
• Career Wins
• Surface WinsUsing the Player column as an example, here’s what we need to do next:

1. Apply the required sorting configuration for the Player column. In this case, we set the sort on Player to Ascending
2. Go into the block properties (right mouse click on the table > Format Table) and give the block a meaningful name. I’ve simply called it the name of the column which I’ve applied the sort on ie. Player
3. Next select the ‘Hide when following formula is true’ checkbox and enter the formula as follows: =ReportFilter([Sort By]) <> “Player”

The purpose of implementing this step is to instruct the report to only show the Player block/table, when the ReportFilter value is equal to Player (ie. when Player is selected from the input control). In all other cases, this block/table should be hidden. Therefore, if ‘Player’ is selected from the input control, display table Player, else hide it.

Now duplicate the Player block and repeat the 3 steps for the next sorting scenario and so forth. With each duplicated block/table, ensure that you position each of the blocks/tables one on top of the other in the exact same position. In my report scenario, I ended up with 6 blocks as follows:

The end result when I view the report in Read mode:

When Player is selected from the input control drop list, the report displays the results by Player ascending.

When Rank Current is selected from the input control drop list, the report displays the results by Rank Current ascending.

When Career Wins is selected from the input control drop list, the report displays the results by Career Wins ascending.

An additional feature which I implemented to provide better clarity of the sorting applied to the report, in particular for printing purposes where the input control pane is no longer visible, is a little arrow which appears under the column selected for sorting. An upward facing arrow ^ indicates an ascending sort order and a downward facing arrow v indicates a descending sort. The formula used is the similar to the block/table hide/show formula eg. If ReportFilter([Sort By]) = “Career Wins” Then “v” Else “”.