Extended sorting of BEx Variable value help members in Story Filters in SAP Analytics Cloud

[mario_nadegger]

Challenge

SAP Analytics Cloud is a powerful frontend especially when working with SAP BW live connection. Reporting with SAP BW Live Connection  especially in context of BEx Variables can be enhancement to optimize user experience.

In our example we want sort the result of the variable help in SAP Analytics Cloud Story Filter linked to a BEx Variable to make it easier for the end user to select the most current member if time dimension 0FISCPER .

Standard behavior of using an BEx Variables in SAC with BW Live Connection is to offer a value help where values are sorted ascending. In our case we had a look at a BEx Variable named “ZSRC_FISCPER” based on Fiscal Period. When sorting periodic values will start at the very beginning. Assuming you start at 1990 will lead to more than 360 entries in 2020. Our objective is to offer the most current member at the top and reduce clicks to select one of the current data. A simple sort of the value help members descending by key ascending will help user to reduce clicks.

We do not find any way to change that behavior, neither in SAP Analytics Cloud nor in SAP BW BEx Backend.

Objective

In our example we have 3 entries sorted by key ascending.

Our objective is now to change this behavior and get the most current period at the top. The result should look like it is shown in that example.

Solution

In BEx Query an input ready variable was added to InfoObject 0FISCPER named ZSRC_FISCPER.

The SAP BEx Query is used in a model and then in a story that uses the BEx variable as story filter. A first look at the value help will come up with the following member list sorted ascending.

Having a deeper look at the backend calls to the GetRespond of the value help of the variable with Google Developing Tools by pressing F12.

We will see that the request for the value help member list of the variable is sorted by MemberKey without direction, that is by default ascending.

We capture the SAP Analytics Cloud request in the backend in the class CL_BICS_INA_PROC_DATA_F4 method _EXECUTE_VALUE_HELP. By implementing an enhancement point at the end of the method _EXECUTE_VALUE_HELP of class CL_BICS_INA_PROC_DATA_F4 we are able to access table <L_T_MEMBER> that carries values for the value help in the response.

In our example the content of the table will look like this.

With the help of our enhancement implementation named ZPA_ENH_INA_VALUE_F4HELP we change the content of the <l_t_member> and sort the column name ascending with the help of the small code snippet.

"check tabel to sort

CHECK <l_t_member> IS ASSIGNED.

 

DATA: l_r_struc        TYPE REF TO data,

      l_r_struc_descr  TYPE REF TO cl_abap_structdescr.

 

try.

      "get variable

      READ TABLE o_s_bics_ina_request-variables INDEX 1 ASSIGNING FIELD-SYMBOL(<l_s_variable>).

      IF <l_s_variable> IS ASSIGNED AND <l_s_variable>-name IS NOT INITIAL.

 

        "check name of variable to be sorted

        if  <l_s_variable>-name = 'ZSRC_FISCPER'.

 

            "get component to sort

            CREATE DATA l_r_struc LIKE LINE OF <l_t_member>.

            l_r_struc_descr ?= cl_abap_typedescr=>describe_by_data_ref( l_r_struc ).

            data(columns) = l_r_struc_descr->get_components( ).

            data(colName) = columns[ l_idx_key ]-name.

 

            "sort

            sort <l_t_member> by (colName) DESCENDING.

      ENDIF.

     ENDIF.

catch CX_ROOT .

 

ENDTRY.

 

As a result, the values in value help is shown according to the content of the table content.

Conclusion

It would be very helpful to have further possibilities to parametrize value help in SAP Analytics Cloud especially with SAP BW live Connection. This example shows that there are possible improvements. Be aware that the solution is an enhancement of the standard implementation and can change with upgrades, notes on SAP Analytics Cloud and/or SAP BW Backend side.