Blogs / SAP / SAP BW/HANA / SAP HANA / Display list of values for selection/Variable Screen at BEx Query based on CDS Views

Display list of values for selection/Variable Screen at BEx Query based on CDS Views

Jun 29, 2018

SHARE

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.

Display list of values for selection/Variable Screen at BEx Query based on CDS Views

Procedure:

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

CDS View:

Display list of values for selection/Variable Screen at BEx Query based on CDS Views

SQL View:

Display list of values for selection/Variable Screen at BEx Query 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.

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

Display list of values for selection/Variable Screen at BEx Query based on CDS Views

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

Display list of values for selection/Variable Screen at BEx Query 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:

Display list of values for selection/Variable Screen at BEx Query 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.

Display list of values for selection/Variable Screen at BEx Query based on CDS Views

Serial Number:

Display list of values for selection/Variable Screen at BEx Query based on CDS Views

Equipment No:

Display list of values for selection/Variable Screen at BEx Query 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.

Read our other SAP HANA blogs here. Learn more about Visual BI Solutions  SAP HANA services offerings here. 


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!