While accessing the CDS views in BEx query the filter screen/variable prompt 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 helps to show the values at the query level.

Procedure:

The below mentioned CDS View is created based on the BSEG table and below are the justifications based on the view.

CDS View:

List of Value for dimensions in BEx Designer based on CDS views

 

SQL View:

List of Value for dimensions in BEx Designer based on CDS views

 

  1. 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.

List of Value for dimensions in BEx Designer based on CDS views

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

List of Value for dimensions in BEx Designer based on CDS views

 

These values are readily seen in the BEx queries for filter selection/variable prompts.

List of Value for dimensions in BEx Designer based on CDS views

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:

List of Value for dimensions in BEx Designer based on CDS views

 

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.

List of Value for dimensions in BEx Designer based on CDS views

 

Serial Number:

List of Value for dimensions in BEx Designer based on CDS views

 

Equipment No:

List of Value for dimensions in BEx Designer based on CDS views

 

 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.

Subscribe to our Newsletter

5600 Tennyson Pkwy
Suite 120
Plano TX 75024.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com