While accessing the CDS views in BEx query the filter screen/variable prompts screen needs to show the list of values available for easier selection process. By default, the values won’t be displayed for all available dimensions in the report.
Below are the cases which help show the values at the query level. This is useful because it helps define selections on the query. Many a time the list of values does not come up in Query designer and the various ways by which the list of values can be populated is explained below.
For this illustration, we are using a CDS view on the BSEG table and consuming it in BEx. The architecture for the same is shown below.
Procedure:
The below mentioned CDS View is created based on the BSEG table and below are the justifications based on the view.
CDS View:
SQL View:
- Define value help on the selected dimension.
To get the value help in the BEx queries/Variables you need define the desired column as key at the CDS view level. In Image (CDS View) mentioned above the highlighted section (Representative Key) will determine which dimension will have value help at the BEx Query/Variable level.
In this CDS the CompanyCode is defined as the representative Key element which will have values for selection.
2. In some cases, the value help will be available without defining the column as key. This was because the selected field/Data element’s domain was already declared with fixed values.
In BSEG table the field SHKZG was predefined with the fixed values
H – Credit
S – Debit
These values are readily seen in the BEx queries for filter selection/variable prompts.
3. Multiple dimensions with list of values.
In the above example only one column was displayed with list if values. Use the below mentioned procedure to show more dimensions with list of values.
Create an another CDS view with the desired column as key and use the newly created view with foreign key association at the parent CDS view.
CDS View:
In the above CDS view(Y_VALUE_HELP_2) the sub CDS view I_Equipment is used, and an association based on Equipment. In CDS view(I_Equipment) Equipment was already defined as key element which was used as foreignKey. Association in Y_VALUE_HELP_2 to show value help for Equipment Numbers in the BEx Queries/Variables.
Serial Number:
Equipment No:
Conclusion:
To fetch list of values for a dimension based on CDS view, the respective column should be defined as Representative Key column/created as key column in another CDS view and used as Foreign Key Association in the main view/fixed values defined at the data element’s Domain level.
Read our other SAP HANA blogs here. Learn more about Visual BI Solutions SAP HANA services offerings here.
