Filtering query results based on several combinations of Infoobject values

[shai_greenberg]

Requirement

• A Query contains the characteristics ZBASEA, ZBASEB, ZBASEC in the rows and several key figures.
• The requirement was to allow filtering by exclusion of combinations of values for ZBASEA, ZBASEB, ZBASEC - that is,AND(NOT(ZA1,ZB1,ZC1),NOT(ZA2,ZB2,ZC2),...).
• The filter should be selectable ad-hoc - that is, the user should be able to filter out only combinations that are actually in the query results.
• It should be possible to save the filter.

Solution overview

• An infoobject ZCOMP containing all possible combinations of ZBASEA, ZBASEB, and ZBASEC as master data was incorporated into the query and the underlying infoprovider. Relevant texts were created in order to make it clear what was being filtered.
• The BEx setting "Only Posted Values for Navigation" allows filtering only combinations that are in the query results.
• The WAD command SET_VARIABLES_STATE allows passing filter values into the variable screen, which allows saving the variable values as a variant.

Detailed Solution

1. The infoprovider on which the query is based is ZIC. The data in ZIC is loaded from the DSO ZDSO (there is no practical difference if the data is loaded from a PSA for the purposes of this solution). ZDSO and ZIC have the same infoobjects. For the purposes of the solution ZCOMP was added to ZIC.
2. A new DSO ZCDSO containing ZBASEA, ZBASEB and ZBASEC was created. ZCDSO contains all combinations of the characteristic values from ZDSO. ZCDSO is loaded using a transformation from ZDSO. The transformation uses a start routine for faster aggregation of the data. See code below.
3. ZCOMP has ZBASEA, ZBASEB and ZBASEC as attributes. The attributes infoprovider is loaded from ZCDSO. ZCOMP's value is calculated as a unique record number. This is done using start and end routine - see code below.
4. ZCOMP's text infoprovider is loaded from the attributes infoprovider using an expert routine that fetches texts from relevant text tables. See code below.
5. ZCOMP's value in ZIC is calculated from the values of ZBASEA, ZBASEB and ZBASEC in the end routine. see code below.
6. ZCOMP is included in the rows of the query and hidden using the columnwidth module used in the analysis item showing the query. A filter web item is shown above the analysis item.
7. In ZCOMP's properties in the query, under "Filter Value Selection during Query Execution", "Only Posted Values for Navigation" is chosen.
8. The WAD command SET_VARIABLES_STATE allows passing filter values into the variable screen. See Code Below.

Notes

I've replaced the original infoobjects with placeholder names to emphasize that this can be used with ANY module rather than a specific business scenario. Obviously the solution is also extensible to any type or number of infoobjects within reason. Therefore the code samples are meant to be more of a guideline and not as copy-paste ready code.

In theory, ZCOMP can be loaded directly from ZDSO, as ZCDSO does not contain any data not present in ZCOMP. I thought adding ZCDSO would make for a good  seperation of loading tasks.

Use of ZCOMP as a compounded infoobject could save the need for the unique key and the ZIC end routine. Might have an effect on performance though.

2. ZDSO->ZCDSO start routine - aggregation of data

*$*$ begin of routine - insert your code only below this line        *-*

*aggregation - speeds up loading

SORT SOURCE_PACKAGE BY ZBASEA ZBASEB ZBASEC
DELETE ADJACENT DUPLICATES FROM SOURCE_PACKAGE

COMPARING ZBASEA ZBASEB ZBASEC.

*$*$ end of routine - insert your code only before this line         *-*

3A. ZCDSO->ZCOMP start routine - don't load existing combinations

*$*$ begin of routine - insert your code only below this line        *-*

* delete rows that already exist in master data from source package

DATA wa_zcomp TYPE /BIC/MZCOMP.

SELECT * FROM /BIC/MZCOMP

  INTO CORRESPONDING FIELDS OF wa_zcomp
  FOR ALL ENTRIES IN SOURCE_PACKAGE

  WHERE ZBASEA = SOURCE_PACKAGE-ZBASEA AND

       ZBASEB = SOURCE_PACKAGE-ZBASEB AND

        ZBASEC = SOURCE_PACKAGE-ZBASEC

        AND OBJVERS = 'A'.

  DELETE SOURCE_PACKAGE

    WHERE

ZBASEA = wa_zcomp-ZBASEA AND

       ZBASEB = wa_zcomp-ZBASEB AND

       ZBASEC = wa_zcomp-ZBASEC.

ENDSELECT.

*$*$ end of routine - insert your code only before this line         *-*

3B. ZCDSO->ZCOMP end routine - calculate ZCOMP values

 

*$*$ begin of 2nd part global - insert your code only below this line  *

DATA g_index TYPE /BIC/OIZCOMP.

SELECT MAX( /BIC/ZCOMP ) FROM /BIC/MZCOMP INTO g_index.

*$*$ end of 2nd part global - insert your code only before this line   *

*$*$ begin of routine - insert your code only below this line        *-*

* create unique key

LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>.

  g_index = g_index + 1.

  <RESULT_FIELDS>-/BIC/ZCOMP = g_index.

ENDLOOP.

*$*$ end of routine - insert your code only before this line         *-*

 

4. ZCOMP-> ZCOMP expert routine - creating texts

* for example purposes only, obviously heavily dependent on scenario

*Select relevant texts from different text tables and combine those into

*one text

    DATA: it_zbasea TYPE TABLE OF /BI0/tzbasea,

          wa_zbasea TYPE /BI0/tzbasea,

it_zbaseb TYPE TABLE OF /BI0/tzbaseb,

          wa_zbaseb TYPE /BI0/tzbaseb.

    SELECT * FROM /BI0/tzbasea INTO CORRESPONDING FIELDS OF TABLE

   it_zbasea
      FOR ALL ENTRIES IN SOURCE_PACKAGE WHERE

      zbasea = SOURCE_PACKAGE-zbasea.

    SELECT * FROM /BI0/tzbaseb INTO CORRESPONDING FIELDS OF TABLE

   it_zbaseb

      FOR ALL ENTRIES IN SOURCE_PACKAGE WHERE

      zbaseb =  SOURCE_PACKAGE-zbaseb .

* create the text for each source record

    LOOP AT SOURCE_PACKAGE ASSIGNING <SOURCE_FIELDS>.

      RESULT_FIELDS-/BIC/ZCOMP= <SOURCE_FIELDS>-/BIC/ZCOMP.

      READ TABLE it_zbasea

      WITH KEY zbasea = <SOURCE_FIELDS>-zbasea LANGU = 'E'

      INTO wa_zbasea.

      if sy-subrc <> 0.

        clear wa_zbasea.

      endif.

      READ TABLE it_zbaseb

      WITH KEY zbaseb = <SOURCE_FIELDS>-zbaseb LANGU = 'E'

      INTO wa_zbaseb.

      if sy-subrc <> 0.

        clear wa_zbaseb.

      endif.

      CONCATENATE wa_zbasea-TXTMD(26) '\' <SOURCE_FIELDS>-zbasec(4)

      '\'

      wa_zbaseb-TXTMD(26) INTO RESULT_FIELDS-TXTLG.

      APPEND RESULT_FIELDS TO RESULT_PACKAGE.

    ENDLOOP.

5. ZDSO->ZIC end routine - calculate ZCOMP's value

$*$ begin of global - insert your declaration only below this line  *-*

... "insert your code here

TYPES: BEGIN OF  t_zcomp,

        ZCOMP           TYPE /BI0/OIZCOMP,
        ZBASEA           TYPE /BI0/OIZBASEA,

        ZBASEB          TYPE /BI0/OIZBASEB,

        ZBASEB          TYPE /BI0/OIZBASEC,

END   OF t_zcomp.

*$*$ end of global - insert your declaration only before this line   *-*

*$*$ begin of routine - insert your code only below this line        *-*

* compute zcomp value from existing fields

DATA: it_zcomp TYPE TABLE OF t_zcomp,

      wa_zcomp TYPE t_zcomp.

SELECT * FROM /BIC/MZCOMP INTO CORRESPONDING FIELDS OF TABLE

it_zcomp

  FOR ALL ENTRIES IN RESULT_PACKAGE

  WHERE ZBASEA = RESULT_PACKAGE-ZBASEA AND

        ZBASEB = RESULT_PACKAGE-ZBASEBAND

        ZBASEC = RESULT_PACKAGE-ZBASEC.

  SORT it_zcomp BY ZBASEA ASCENDING

           ZBASEB ASCENDING

           ZBASEC ASCENDING.

  LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>
    READ TABLE it_zcomp WITH KEY ZBASEA = <RESULT_FIELDS>-ZBASEA

                                        ZBASEB =

                                        <RESULT_FIELDS>-ZBASEB

                                        ZBASEC =

                                        <RESULT_FIELDS>-ZBASEC

                                        INTO

                                        wa_zcomp.

    <RESULT_fields>-/BIC/ZCOMP =  wa_zcomp-/BIC/ZCOMP.

  ENDLOOP.

*$*$ end of routine - insert your code only before this line         *-*

8. WAD command for passing filter values from analysis item into variable screen

<bi:ACTION type="CHOICE" value="INSTRUCTION" >

      <bi:INSTRUCTION >

          <bi:SET_VARIABLES_STATE >

              <bi:VARIABLE_SCREEN value="X" />

              <bi:VARIABLE_VALUES type="ORDEREDLIST" >

                  <bi:VARIABLE_VALUE type="COMPOSITE" index="1" >

                      <bi:VARIABLE value="ZCMP_MUL" text="ZCMP_MUL" />

                      <bi:VARIABLE_TYPE type="CHOICE" value="SELECTION_BINDING_TYPE" >

                          <bi:SELECTION_BINDING_TYPE type="CHOICE" value="DATA_PROVIDER_CHARACTERISTIC" >

                              <bi:DATA_PROVIDER_CHARACTERISTIC type="COMPOSITE" >

                                  <bi:DATA_PROVIDER_REF value="DP_4" />

                                  <bi:CHARACTERISTIC value="ZCOMP" text="ZCOMP" />

                              </bi:DATA_PROVIDER_CHARACTERISTIC>

                          </bi:SELECTION_BINDING_TYPE>

                      </bi:VARIABLE_TYPE>

                  </bi:VARIABLE_VALUE>

              </bi:VARIABLE_VALUES>

          </bi:SET_VARIABLES_STATE>

      </bi:INSTRUCTION>

  </bi:ACTION>