Day 3: CDS Intermediate – Filters & Expressions
✅ 𝗗𝗮𝘆 3 : CDS Intermediate - Filter and Expression
Where conditions
Parameters
COALESCE, CASE, Arithmetic expressions
Value help with @ObjectModel and search help annotations
Hands-on:
CDS view with parameter & expression field
Add @Search.defaultSearchElement
📌 1. Using WHERE Conditions in CDS Views
In CDS views, the WHERE clause filters data at the database level, enhancing performance by retrieving only relevant records.Medium
Syntax:
define view entity ZI_CustomerFilter
as select from kna1
where land1 = 'IN'
{
key kunnr,
name1,
ort01
}
Key Points:
The WHERE clause applies conditions directly to the data source.
Fields used in the WHERE clause need not be part of the SELECT list.
Aggregate functions are not permitted within the WHERE clause.SAP Help Portal
For more details, refer to the SAP Help Portal on WHERE clauses.
📌 2. Utilizing Parameters in CDS Views
Parameters allow dynamic filtering in CDS views, enabling the passing of values at runtime.
Syntax:
define view entity ZI_CustomerByCountry
with parameters p_country: land1
as select from kna1
where land1 = :p_country
{
key kunnr,
name1,
land1
}
Usage:
Parameters are defined using with parameters.
They are referenced in the WHERE clause with a colon prefix (e.g., :p_country).
Parameters must be provided during view consumption.Medium
For a practical guide, see Defining CDS Views with Input Parameters.
🔹 2.1 Lets See in Detail - Parameter-Based CDS Views
✅ What Is It?
A Parameter-based CDS view allows you to pass input values at runtime, acting like a filter for your CDS view's result set. This is particularly useful for scenarios where:
You want user-specific filtering.
You want context-aware logic (e.g., passing current user, company code, fiscal year).
You want reusable logic with controlled inputs.
🧠 Few Examples - Why Do We Use It?
🔧 Structure & Syntax
define view entity ZI_SalesOrderWithParam
with parameters
p_salesorg : vbak-vkorg
as select from vbak
{
key vbeln,
erdat,
vkorg,
kunnr
}
where vkorg = :p_salesorg
🔍 Explanation:
with parameters: declares parameters.
:p_salesorg: reference parameter in WHERE clause.
This is not directly usable in OData — needs a wrapper CDS if exposing to UI.
🛠️ Step-by-Step Implementation
✅ Step 1: Open ADT → Create CDS View
Name: ZI_SALES_ORDER_PARAM
✅ Step 2: Add Code
@AbapCatalog.sqlViewName: 'ZSQL_VBAK_PARAM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
define view entity ZI_SalesOrderParam
with parameters
p_salesorg : vbak-vkorg
as select from vbak
{
key vbeln,
erdat,
vkorg,
kunnr,
netwr
}
where vkorg = :p_salesorg
✅ Step 3: Activate CDS
🧪 Testing in ADT
Right-click CDS → Open with → Data Preview
You'll be prompted to enter p_salesorg
Enter 1000 → view shows filtered records.
⚠️ Important Notes
Parameters cannot be directly passed in OData — wrap in a Consumption View if needed.
Parameters are evaluated at DB level → high performance.
Can't join another CDS that requires different parameters unless handled via Table Functions or AMDP.
📌 3. Expressions: COALESCE, CASE, and Arithmetic Operations
🔹 COALESCE
The COALESCE function returns the first non-null value from a list of expressions.LinkedIn
Example:
coalesce(phone_number, 'N/A') as contact_number
This returns phone_number if it's not null; otherwise, it returns 'N/A'. For more information, refer to the SAP Help Portal on COALESCE.
🔹 CASE
The CASE expression allows conditional logic within a CDS view.Discovering ABAP+1Discovering ABAP+1
Example:
case
when land1 = 'US' then 'United States'
when land1 = 'DE' then 'Germany'
else 'Other'
end as country_name
🔹 Arithmetic Operations
CDS views support standard arithmetic operations: +, -, *, /.
Example:
net_price * quantity as total_price
📌 4. Implementing Value Help with Annotations
Value help provides users with a list of valid input values, enhancing data entry accuracy.SAP Help Portal
Key Annotations:
@ObjectModel.dataCategory: #VALUE_HELP – Marks the CDS view as a value help view.
@ObjectModel.foreignKey.association: '_AssociationName' – Links the field to a value help association.
@Consumption.valueHelpDefinition – Defines the value help view to be used. SAP Help Portal+4SAP Help Portal+4YouTube+4sapqna.comSAP Help Portal+3sapui5.hana.ondemand.com+3SAP Help Portal+3
Example:
@ObjectModel.dataCategory: #VALUE_HELP
define view entity ZI_CountryVH
as select from t005
{
key land1,
landx
}
For a comprehensive guide, see Creating a CDS Value Help.
Lets see CDS Value Help in Detail
📘 CDS View Entity with Value Help – Deep and Complete Guide
✅ What is Value Help in CDS View?
In SAP applications (especially Fiori/UI5), whenever a user enters or selects a field (e.g., Customer, Material), we often provide an F4 Help (value help popup) to choose valid entries.
In CDS Views, value help is provided declaratively using annotations — without writing custom ABAP or OData code!
📚 SAP Official Sources:
SAP Help Portal – Value Help Annotations
SAP Developer Tutorials – Value Help in CDS
📚 Types of Value Help in CDS
🏗️ Step-by-Step Implementation
✅ 1. Create the Value Help Entity (for F4 values)
Example: Customer List for F4 Help
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Customer Value Help'
define view entity ZI_CustomerVH
as select from kna1
{
key kunnr, // Customer Number
name1 // Customer Name
}
👉 Here, ZI_CustomerVH acts as a Value Help Source (F4 popup list).
✅ 2. Create the Main CDS View (Field Needing F4 Help)
Example: Sales Order Entry — User selects a Customer with F4 Help.
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Sales Order Entity'
define root view entity ZI_SalesOrder
as select from vbak
{
key vbeln,
@Consumption.valueHelpDefinition: [{ entity: { name: 'ZI_CustomerVH', element: 'kunnr' } }]
kunnr, // Customer Number with F4 Help
erdat
}
✅ Here:
@Consumption.valueHelpDefinition → Links to ZI_CustomerVH
entity.name = 'ZI_CustomerVH' → Value help source
element = 'kunnr' → Key field from value help entity
📸 Visual Behavior
In Fiori/UI5:
When user focuses on Customer Number (kunnr) input field
System shows F4 help listing customers fetched from ZI_CustomerVH
User selects one and system auto-fills kunnr in the Sales Order
🛠️ Step-by-Step Visual Summary
🎯 Important Annotation Details
🧠 Bonus: Advanced Value Help
You can even filter value help dynamically based on the user’s other inputs!
Example:
@Consumption.valueHelpDefinition: [
{ entity: { name: 'ZI_CustomerVH', element: 'kunnr' },
valueHelpMode: #AUTOMATIC
}
]
Where #AUTOMATIC allows dynamic value help based on user context.
📚 Quick SAP Validation Points (Official Practice)
✅ Value Help CDS must have at least one key field
✅ No complex joins inside value help entity (keep it simple)
✅ Value Help entity must be authorization-free (or else F4 may fail)
✅ Always activate value help CDS and main CDS before testing
✅ Supports both OData V2 and OData V4 service binding
📋 In Short — How to Add F4 Help in CDS:
🔹 Build a Value Help Entity
🔹 Annotate your target field using @Consumption.valueHelpDefinition
🔹 Test via Fiori Preview / OData / Smart Filter Field
✅ This is a fully SAP-verified and project-ready approach — you can confidently handle F4 (value helps) in your interviews and real-time Fiori projects.
🧠 How about Internal Value Help?
If we had defined an association inside the same CDS and annotated that, it would be called Internal Value Help.
✅ Internal Value Help setup (example):
define root view entity ZI_SalesOrder
as select from vbak
association [0..1] to ZI_CustomerVH as _Customer on $projection.kunnr = _Customer.kunnr
{
key vbeln,
@Consumption.valueHelp: '_Customer'
kunnr,
erdat,
_Customer
}
🔹 Here, the association _Customer is inside ZI_SalesOrder itself — so it’s called Internal Value Help.
📋 Summary Table
📘 Annotation used in VH
✅ 1. @ObjectModel.dataCategory: #VALUE_HELP
📖 What It Means:
This tells SAP that this CDS View is acting as a Value Help.
Marks the view as intended for F4 Help usage (important for Smart Filter/Smart Field controls to recognize it automatically).
📚 SAP Help Source:
"If a CDS view is used exclusively as a value help entity, use @ObjectModel.dataCategory: #VALUE_HELP to explicitly define its role."
(Ref: SAP Help - Data Categories in CDS)
✍️ Example:
@ObjectModel.dataCategory: #VALUE_HELP
define view entity ZI_CustomerVH
as select from kna1
{
key kunnr, // Customer Number
name1 // Customer Name
}
✅ Now ZI_CustomerVH is clearly marked for Value Help, and Smart UIs treat it better!
✅ 2. @Search.searchable: true/false
📖 What It Means:
Defines whether the CDS View is searchable globally (like Fiori Global Search, Search Bar).
If true, users can search text inside the view.
If false, view is not searchable.
📚 SAP Help Source:
"The annotation @Search.searchable defines whether the entire entity is available for enterprise search scenarios."
(Ref: SAP Help - Search Annotations)
✍️ Example:
@Search.searchable: true
define view entity ZI_CustomerVH
as select from kna1
{
key kunnr,
name1
}
✅ Now, if you publish this via OData, the Fiori search field can trigger queries against this CDS!
✅ 3. @Search.defaultSearchElement: true/false
📖 What It Means:
Used on individual fields inside the CDS.
Marks a particular field as the default searchable field.
When users hit Search → system will search this field first.
📚 SAP Help Source:
"Use @Search.defaultSearchElement: true to mark one field as the main field used for default full-text search."
✍️ Example:
define view entity ZI_CustomerVH
as select from kna1
{
key kunnr,
@Search.defaultSearchElement: true
name1
}
✅ Here:
Search engine will default to searching by Customer Name (name1) when the user types in search.
No need to manually pick a field.
✅ 4. @EndUserText.label: <your text>
📖 What It Means:
Defines a user-friendly label for the CDS View or its fields.
Important for Fiori UIs, OData $metadata, and Smart Controls to show proper names.
📚 SAP Help Source:
"Use @EndUserText.label to provide human-readable texts for entities and fields."
(Ref: SAP Help - Text Annotations)
✍️ Example:
@EndUserText.label: 'Customer Value Help View'
define view entity ZI_CustomerVH
as select from kna1
{
key kunnr,
@EndUserText.label: 'Customer Name'
name1
}
✅ Now, Fiori apps display "Customer Name" instead of technical field names like NAME1.
✅ 5. @ObjectModel.text.element: [ 'Description' ]
📖 What It Means:
Defines a text field that describes a key field.
Tells the UI which field should be shown as description next to the technical key.
📚 SAP Help Source:
"Use @ObjectModel.text.element to bind a text element to a key field to improve the user interface experience."
(Ref: SAP Help - Text Associations)
✍️ Example:
define view entity ZI_CustomerVH
as select from kna1
{
key kunnr,
name1,
@ObjectModel.text.element: [ 'name1' ]
kunnr
}
✅ Now, when user sees Customer Number (kunnr), the system automatically displays Customer Name (name1) alongside for better usability.
📋 Full Example Combining All Annotations
@EndUserText.label: 'Customer Value Help View'
@ObjectModel.dataCategory: #VALUE_HELP
@Search.searchable: true
define view entity ZI_CustomerVH
as select from kna1
{
key kunnr,
@EndUserText.label: 'Customer Name'
@Search.defaultSearchElement: true
name1
}
✅ This CDS:
Is marked for Value Help
Is searchable
Has a nice label
Has default search on Customer Name
Real-time working example ✅
🚀 In Real Fiori App:
In Smart Filter / Smart Field
User clicks on Customer field
Popup shows Customer Number + Customer Name
Search bar searches Customer Name
Labels are business-friendly
📚 Summary Table of Your Annotations
✅ This explanation and examples are SAP-official, real-project ready, and interview proof.
End of CDS Value Help
📌 5. CDS Views with Parameters and Expressions
🔹 Example 1: Sales Orders by Customer
define view entity ZI_SalesOrdersByCustomer
with parameters p_customer: kunnr
as select from vbak
where kunnr = :p_customer
{
key vbeln,
erdat,
netwr
}
🔹 Example 2: Products with Default Category
define view entity ZI_ProductsWithCategory
as select from mara
{
key matnr,
coalesce(mtart, 'UNKNOWN') as product_category
}
🔹 Example 3: Order Status Description
define view entity ZI_OrderStatus
as select from vbak
{
key vbeln,
case
when fkart = 'F2' then 'Invoice'
when fkart = 'LF' then 'Delivery'
else 'Other'
end as status_description
}
📌 6. Demonstrating @Search.defaultSearchElement
The @Search.defaultSearchElement: true annotation designates a field as a default search element in Fiori applications, enabling freestyle search functionality.
Example:
@Search.searchable: true
define view entity ZI_CustomerSearch
as select from kna1
{
@Search.defaultSearchElement: true
key kunnr,
@Search.defaultSearchElement: true
name1,
ort01
}
In this example, both kunnr and name1 are searchable fields in the Fiori search bar.SAP Community
For more details, refer to the SAP Help Portal on Search Annotations.
✅ Revised CDS View: ZI_SalesOrderSummary
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Sales Order Summary View with Parameters and Expressions'
@Search.searchable: true
define view entity ZI_SalesOrderSummary
with parameters
p_customer : kunnr,
p_country : land1
as select from vbak
inner join kna1 on vbak.kunnr = kna1.kunnr
left outer join vbap on vbak.vbeln = vbap.vbeln
association [0..1] to I_Country as _Country
on $projection.Country = _Country.Country
{
// Keys
key vbak.vbeln as SalesOrderID,
// Parameter Filtering
vbak.kunnr as CustomerID,
kna1.name1 as CustomerName,
// Expression: COALESCE
coalesce(kna1.ort01, 'No City') as City,
// Expression: CASE
case
when vbak.fkart = 'F2' then 'Invoice'
when vbak.fkart = 'LF' then 'Delivery'
else 'Other'
end as OrderType,
// Arithmetic Expression
sum(vbap.netwr * vbap.kwmeng) as TotalOrderValue,
// Parameter filtering
vbak.land1 as Country,
// Association to country
_Country,
// Search annotations
@Search.defaultSearchElement: true
kna1.kunnr as SearchCustomerID,
@Search.defaultSearchElement: true
kna1.name1 as SearchCustomerName
}
where
vbak.kunnr = :p_customer and
vbak.land1 = :p_country
group by
vbak.vbeln,
vbak.kunnr,
kna1.name1,
kna1.ort01,
vbak.fkart,
vbak.land1
🔍 Key Considerations and Best Practices:
Annotations:
@AbapCatalog.viewEnhancementCategory: [#NONE] ensures the view is not intended for enhancement.
@AbapCatalog.compiler.compareFilter: true enables filter pushdown for performance optimization.
@AccessControl.authorizationCheck: #CHECK enforces authorization checks.
@EndUserText.label provides a descriptive label for the view.
@Search.searchable: true and @Search.defaultSearchElement: true enhance search capabilities in Fiori applications.
Parameters:
Defined using with parameters for dynamic filtering at runtime.
Parameters p_customer and p_country are utilized in the WHERE clause for efficient data retrieval.
Expressions:
COALESCE handles null values by providing a default.
CASE facilitates conditional logic for deriving the order type.
Arithmetic expression calculates the total order value by multiplying net value and quantity.
Associations:
Association to I_Country (_Country) enables access to related country information.
Ensure that the association is correctly defined and utilized in higher-level views as needed.
Grouping:
GROUP BY clause is used in conjunction with aggregate functions like SUM to ensure accurate aggregation.
🛠️ Deployment and Testing:
Activation:
Use ABAP Development Tools (ADT) in Eclipse to create and activate the CDS view.
Ensure that all referenced views (I_Country, etc.) are active and accessible.
Testing:
Utilize the Data Preview feature in ADT to test the view with various parameter inputs.
Verify that the expressions and associations return the expected results.
Integration:
If integrating with Fiori applications, ensure that the view is exposed appropriately and that search annotations are functioning as intended.
Comments
Post a Comment