Summary:
This article contains step by step process how to suppress columns based on input filter in SAP BEX query designer.
Introduction:
In BEX query designer is no standard function to suppress rows/columns based on input filter. There is only function in query properties to suppress zeros with effect on rows/columns .
There is no option to do suppression dynamically. We can probably achieve this by using conditions. In this article I will share you my experience and show solution how to dynamically suppress columns with a simple trick and little bit coding in customer exit.
Target Audience:
Main target of this article are BW consultants who have enough experience with BW Query and realizing complex business customer requirements.
Tools:
SAP BW 7.4, Bex Query Designer
Business scenario:
We have the structure in query and we want to hide some columns based on input filter.
Below are the columns from specific tax form related to companies (PO) and persons (FO).
Query Key figures definition:
We have to show in query only one of this part of structure based on input global filter. But all columns from Part 1 (PO) or 2 (FO) has to be shown even if they are empty (e.g. r. 1020 or r.63a).
Solution:
Selection for restricted key figures are define with one key figure and characteristic. In our scenario characteristic has only two values “FO” and “PO”.
Definition for columns "r. 1010 - r. 1030" "PO: part.
Definition for columns "r. 117 - r. 64" "FO: part.
For the characteristic in query global filter you have to define variable which has to be set as "Manual Input" on the General tab
Mandatory and input ready on the details tab.
Than we use this variable in global filter.
Generally following settings allows you to hide zero columns based on input filter (FO/PO). Key figures from Par 1 or Part 2 will be always zero and not shown in query result based on the restricted key figures we defined above in column section.
In query option allow setting for columns suppression
On your structure with key figures on the display tab uncheck this option
This setting will not only suppress all zero columns from Part1/2 (based on input filter FO/PO) but also zero columns from Part we want to show as result (see picture below).
What in case we need to show zero column as well from selected Part?
Than we have to create Formulas for all your restricted key figures.
All restricted key figures set for “always hide” on display tab.
Create two formula variables. One for each value of your characteristic used for restriction. In our case there are only two values FO/PO as mentioned at beginning.
In our case:
ZVAR_EXIT_CONSTANT_0001
ZVAR_EXIT_CONSTANT_0002
with customer exit option
Single value option on Details tab, uncheck input ready option
Each calculated key figure will then use previous restricted key figure plus one of the customer exit variables made for respective values (FO/PO in our case).
Definition for calculated key figures "r. 1010 - r. 1030" "PO: part
Definition for calculated key figures columns "r. 117 - r. 64" "FO: part
In next step we write our own logic in CMOD transaction how to fill this variables.
Open t-code CMOD and write logic for filling this customer exit variables with value “0.001”.
WHEN 'ZVAR_EXIT_CONSTANT_0001'.
IF i_step EQ 2.
READ TABLE i_t_var_range INTO i_range
WITH KEY vnam = 'SMO_TAXDRH'.
IF sy-subrc = 0 AND i_range-low = 'FO'.
l_s_range-low = '0.001'.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
ENDIF.
APPEND l_s_range TO e_t_range.
ENDIF.
WHEN 'ZVAR_EXIT_CONSTANT_0002'.
IF i_step EQ 2.
READ TABLE i_t_var_range INTO i_range
WITH KEY vnam = 'SMO_TAXDRH'.
IF sy-subrc = 0 AND i_range-low = 'PO'.
l_s_range-low = '0.001'.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
ENDIF.
APPEND l_s_range TO e_t_range.
ENDIF.
On display tab for each calculated key figure of our new created structure set decimal places as “0.00”.
Result:
So as result of all these settings we change previous value 0 in cell by adding formula (filled in customer exit) in calculated key figure. New result will be “0.001” shown as “0.00” based in the decimals setting we did in last step.
That do the magic trick and report shows 0 for the desired column which would be suppressed by the standard settings of query. On background the value will be 0.001 and column will be not suppressed.
Result based on filter:
- SAP Managed Tags:
