Symptom
- Filter does not work.
- Restrictions added to the Default Values of a BEx Query are ignored in SAP Analytics Cloud.
- When reporting off the same SAP BW BEx Query in BEx Analyzer, it does filter the data based on the value entered in the variable.
- Story based on an SAP BW BEx Query does not filter the data based on the variable, or values, defined under the Default Values in the SAP BW BEx Query.
Environment
- SAP Analytics Cloud 2024.2.16
Reproducing the Issue
- In BEx Query Designer, create a BW Query based on an SAP BW Cube.
- In the Filters, under the section "Default Values", add a filter on a Dimension using a Variable, or values.
- In SAP Analytics Cloud, create a Story based on the SAP BW BEx Query.
- Notice the data is not filtered by the Default Values defined in the SAP BW Query.
Cause
- The SAP BW BEx Query variable filter is defined in the Default Values section in the BEx Query Designer.
- Defining a variable in the Default Value section of an SAP BW BEx Query, does not to filter the data, but it actually brings back all the data in BEx Analyzer, and filter the initial view of the data based the value selected in the variable. This enable the user to change the filter value without hitting the SAP BW Cube again, as all the data is brought back to BEx Analyzer.
- Applications like SAP Analytics Cloud, values, or variable defined under the Default Value Filter in the SAP BW BEx Query, does not filter any data because the SAP BW BEx Query actually returns all the data to the application, and there isn't a concept of initial view like in BEx Analyzer.
-
For example: If we have the following data:
Country City
------------- ---------------
USA New York
USA San Francisco
USA Miami
Canada Toronto
Canada Montreal
If we create an SAP BW BEx Query with a Filter on Country based on a variable defined under Default Values, and for Country variable, you select the value: Canada
It will query the SAP BW cube, and return all the data to the external application:Country City
------------- ---------------
USA New York
USA San Francisco
USA Miami
Canada Toronto
Canada MontrealThen within the external application that support the Default Values filters, it will filter the data based on Country, which will then show:
Country City
------------- ---------------
Canada Toronto
Canada Montreal
The whole concept of Default Values Filter in SAP BW Query is to show an initial view of the data filtered, but send all the data from the SAP BW cube to the external application, so new filtering can be done on the data without having to fetch the data back from the data source. This is why in SAP Analytics Cloud, it list all the data,.
Resolution
- To filter the data, modify the SAP BW BEx Query, and set the filter under the Characteristic Restrictions, instead of under "Default Values"
- Open the SAP BW BEx Query in the BEx Query Designer
- In the SAP BW BEx Query Designer, move the variable from the "Default Values" section to the "Characteristic Restrictions" section.
- Save the SAP BW BEx Query.
Keywords
SAC, BEx Query , KBA , LOD-ANA-DES , Story Design & Visualizations , Problem